Presto:如何使用当前日期和时区指定时间间隔

Question

如何重写以下查询：

WHERE (
parsedTime BETWEEN 
TIMESTAMP '2019-10-29 00:00:00 America/New_York' AND
TIMESTAMP '2019-11-11 23:59:59 America/New_York'
) 

但使间隔具有动态性：从14天前到current_date？

Answer 1

Presto在日期和时间函数和操作中提供了非常方便的功能interval。

-- Creating sample dataset
WITH dataset AS (
SELECT
'engineering' as department,
ARRAY[
TIMESTAMP '2019-11-05 00:00:00', 
TIMESTAMP '2018-10-29 00:00:00'
] as parsedTime_array
)
SELECT department, parsedTime FROM dataset
CROSS JOIN UNNEST(parsedTime_array) as t(parsedTime)
-- Filtering records for the past 14 days from current_date
WHERE(
parsedTime > current_date - interval '14' day
)

结果

| department    | parsedTime
---------------------------------------
1   | engineering   | 2019-11-05 00:00:00.000

更新2019-11-11

注意：current_date返回查询开始时的当前日期。我想，雅典娜会一直使用UTC时间，但不是100%确定。因此，要提取特定时区的当前日期，我建议使用带有时区转换的时间戳。尽管确实

current_timestamp = current_timestamp at TIME ZONE 'America/New_York'

因为CCD_ 4在时间上表示相同的时刻但仅在用于打印它们的时区上不同。然而，由于5小时的偏移，以下情况并不总是正确的。

DATE(current_timestamp) = DATE(current_timestamp at TIME ZONE 'America/New_York')

这可以很容易地通过进行验证

WITH dataset AS (
SELECT
ARRAY[
TIMESTAMP '2019-10-29 23:59:59 UTC',
TIMESTAMP '2019-10-30 00:00:00 UTC',
TIMESTAMP '2019-10-30 04:59:59 UTC',
TIMESTAMP '2019-10-30 05:00:00 UTC'
] as parsedTime_array
)
SELECT
parsedTime AS "Time UTC",
DATE(parsedTime) AS "Date UTC",
DATE(parsedTime at TIME ZONE 'America/New_York') AS "Date NY",
to_unixtime(DATE(parsedTime)) AS "Unix UTC",
to_unixtime(DATE(parsedTime at TIME ZONE 'America/New_York')) AS "Unix NY"
FROM
dataset,
UNNEST(parsedTime_array) as t(parsedTime)

结果这里我们可以看到2纽约时间戳分为2019-10-29和2019-10-30，而UTC时间戳分别只有1和3。

Time UTC                    | Date UTC   | Date NY    | Unix UTC   | Unix NY    
-----------------------------|------------|------------|------------|------------
2019-10-29 23:59:59.000 UTC | 2019-10-29 | 2019-10-29 | 1572307200 | 1572307200 
2019-10-30 00:00:00.000 UTC | 2019-10-30 | 2019-10-29 | 1572393600 | 1572307200 
2019-10-30 04:59:59.000 UTC | 2019-10-30 | 2019-10-30 | 1572393600 | 1572393600 
2019-10-30 05:00:00.000 UTC | 2019-10-30 | 2019-10-30 | 1572393600 | 1572393600 

现在，让我们快进一个月。2019年11月3日，纽约的冬季时间发生了变化。但是，UTC格式的时间戳不受其影响。因此：

WITH dataset AS (
SELECT
ARRAY[
TIMESTAMP '2019-11-29 23:59:59 UTC',
TIMESTAMP '2019-11-30 00:00:00 UTC',
TIMESTAMP '2019-11-30 04:59:59 UTC',
TIMESTAMP '2019-11-30 05:00:00 UTC'
] as parsedTime_array
)
SELECT
parsedTime AS "Time UTC",
DATE(parsedTime) AS "Date UTC",
DATE(parsedTime at TIME ZONE 'America/New_York') AS "Date NY",
to_unixtime(DATE(parsedTime)) AS "Unix UTC",
to_unixtime(DATE(parsedTime at TIME ZONE 'America/New_York')) AS "Unix NY"
FROM
dataset,
UNNEST(parsedTime_array) as t(parsedTime)

结果在这里，我们可以看到3纽约时间戳落入2019-11-29，1落入2019-11-30，而对于UTC时间戳，1/3的比率保持不变。

Time UTC                    | Date UTC   | Date NY    | Unix UTC   | Unix NY    
-----------------------------|------------|------------|------------|------------
2019-11-29 23:59:59.000 UTC | 2019-11-29 | 2019-11-29 | 1574985600 | 1574985600 
2019-11-30 00:00:00.000 UTC | 2019-11-30 | 2019-11-29 | 1575072000 | 1574985600 
2019-11-30 04:59:59.000 UTC | 2019-11-30 | 2019-11-29 | 1575072000 | 1574985600 
2019-11-30 05:00:00.000 UTC | 2019-11-30 | 2019-11-30 | 1575072000 | 1575072000 

此外，不同的国家在不同的日期切换到冬季/夏季。例如，2019年，伦敦(英国(于2019年10月27日将时钟拨回1小时，而纽约(美国(则于2019年11月3日将时钟移回1小时。