python - Impala 在不输入日期的情况下查询昨天的数据可能使用 python

Question

数据已经被解析为日期时间“2018-03-08 00:00:00”。我可以通过说“2018-03-08 00:00:00”和“2018-03-08 24:00:00”之间的位置来获取 3/8/18 的数据。但我想以某种方式使用 Impala 日期时间函数，这样我就可以每天运行它而无需手动输入每个日期。我已经通读了一些文档，但仍然感到困惑。

查询 Impala

cursor.execute("SELECT sourceaddress,count(sourceaddress) as count FROM table
GROUP BY sourceaddress
ORDER BY count desc
LIMIT 10")

即使我可以在查询之前用 python 定义昨天:

yesterday = str((pd.to_datetime('today') - pd.Timedelta(days=1)).date())

并以某种方式将其合并到 Impala 查询中。

Answer 1

您的问题并不完全清楚，但据我了解，您想使用 Impala/python 日期时间函数来自动执行查询以选择一天的数据。下面是我如何在 impala 中执行的示例。

-- to_date - function will cut the date from timestamp
-- now()   - gives you the current timestamp

select to_date(now()) as currentDate, 
to_date(now() + interval 1 days) as currentDatePlusaDay, 
now() as currentTimestamp, 
now() + interval 1 day as currentTimestampPlusaDay, 
concat(to_date(now() - interval 1 days), ' 00:00:00') as whereBetweenMin, 
concat(to_date(now() - interval 1 days), ' 24:00:00') as whereBetweenMax ;

--Result

+-------------+---------------------+-------------------------------+-------------------------------+---------------------+---------------------+
| currentdate | currentdateplusaday | currenttimestamp              | currenttimestampplusaday      | wherebetweenmin     | wherebetweenmax     |
+-------------+---------------------+-------------------------------+-------------------------------+---------------------+---------------------+
| 2018-03-23  | 2018-03-24          | 2018-03-23 12:14:36.073281000 | 2018-03-24 12:14:36.073281000 | 2018-03-22 00:00:00 | 2018-03-22 24:00:00 |
+-------------+---------------------+-------------------------------+-------------------------------+---------------------+---------------------+

--You can probably use wherebetweenmin & wherebetweenmax