Presto: how to specify time interval using the current date and timezone

Question

How to rewrite the following query:

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

but making the interval dynamic: from 14 days ago to current_date?

Answer 1

Presto provides quite handy functionality interval within date and time functions and operations.

-- 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
)

Result

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

Update 2019-11-11

Note: current_date returns the current date as of the start of the query. I think, Athena would always use UTC time, but not 100% sure. So to extract current date in a particular time zone, I'd suggest to use timestamps with time zone conversion. Although it is true that

current_timestamp = current_timestamp at TIME ZONE 'America/New_York'

since AT TIME ZONE represents the same instant in time but differs only in the time zone used to print them. However the following is not always true due to 5 hour offset.

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

This can be easily verified with:

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. Here we can see that 2 NY timestamps fall into 2019-10-29 and 2019-10-30 whereas for UTC timestamps it is only 1 and 3 respectively.

 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 

Now, let's fast forward a month. There was a change to winter time in NY on 3rd or November 2019. However, timestamp in UTC format is not affected by it. Therefore:

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)

Result. Here we can see that 3 NY timestamps fall into 2019-11-29 and 1 falling into 2019-11-30, whereas for UTC timestamps ratio of 1/3 remained the same.

 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 

Furthermore, different countries switch to winter/summer time on different dates. For instance in 2019, London (UK) moved clock 1 hour back on 27 October 2019, whereas NY (USA) moved clock 1 hour back on 3 November 2019.