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 在 date and time functions and operations 内提供了非常方便的 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 返回查询开始时的当前日期。我想，Athena 会一直使用 UTC 时间，但不能 100% 确定。因此，要在特定时区提取当前日期，我建议使用时间戳和时区转换。虽然这是真的

current_timestamp = current_timestamp at TIME ZONE 'America/New_York'

因为 AT TIME ZONE 表示同一时刻，但仅用于打印它们的时区不同。然而，由于 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)

Result. 在这里我们可以看到 2 NY 时间戳落入 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 

现在，让我们快进一个月。 NY on 3rd or November 2019 冬令时发生了变化.但是，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 NY 时间戳落入 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 年，伦敦(英国)将时钟拨回 1 小时 27 October 2019 ，而纽约(美国)将时钟向后移动了 1 小时 3 November 2019 .