SQL date and time functions

When you enter your query in the SQL Editor or create a calculation, you use Presto SQL syntax. To adjust or perform calculations on date or timestamp values, you may find these common functions useful.

Note: Runtime variables in Chains, such as those used for date and time transformations, use Python strftime rather than Presto SQL.

AT TIME ZONE

To convert the time zone of a timestamp, use the AT TIME ZONE operator. For example:

SELECT cast(timestamp '2020-10-31 01:00 UTC' as VARCHAR)
2020-10-31 01:00:00.000 UTC

SELECT cast(timestamp '2020-10-31 01:00 UTC' AT TIME ZONE 'America/Los_Angeles' as VARCHAR)
2020-10-30 18:00:00.000 America/Los_Angeles

Tip: In most areas, you can use America/Los_Angeles for Pacific time zone, America/Denver for Mountain, America/Chicago for Central, and America/New_York for Eastern. 

CURRENT_DATE

To return the date when the query runs, use the CURRENT_DATE function. For example, SELECT CURRENT_DATE().

DATE

To convert a value to a date value, use the DATE function as DATE(value). 

DATE_ADD

To add an interval unit value to a date or timestamp, use the DATE_ADD function as DATE_ADD(unit, value, timestamp). For unit, DATE_ADD supports:

• millisecond
• second
• minute
• hour
• day
• week
• month
• quarter
• year

For example, SELECT DATE_ADD('day', 10, "2020-06-15") returns 2020-06-25.

Note: To parse an IS08601-formatted string as the date value, use the FROM_ISO8601_DATE function, such as FROM_ISO8601_DATE('2020-06-15').

DATE_DIFF

To return the difference of two date or timestamp values, use the DATE_DIFF function as DATE_DIFF(unit, timestamp1, timestamp 2). For unit, DATE_DIFF supports the same values as DATE_ADD. For example, SELECT DATE_DIFF('day', "2020-06-15", "2020-06-25") returns 10.

Note: To parse an IS08601-formatted string as the date value, use the FROM_ISO8601_DATE function, such as FROM_ISO8601_DATE('2020-06-15').

DATE_FORMAT

To return a date or timestamp value in a set format, use the DATE_FORMAT function as DATE_FORMAT(timestamp, format). For format, DATE_FORMAT supports a combination of these values:

Format	Description
%a	Abbreviated name of the day of the week, fromSun to Sat
%b	Abbreviated month name, from Jan to Dec
%c	Numeric month value, from 1 to 12
%d	Numeric day of the month as a two-digit number, from 01 to 31
%e	Numeric day of the month, from 1 to 31
%f	Millisecond, from 000000 to 999000 for printing; from 0 to 999999999 for parsing
%H	Hour of 24-hour clock as a two-digit number, from 00 to 23
%h or %I	Hour of 12-hour clock as a two-digit number, from 00 to 12
%i	Minute, from 00 to 59
%j	Day of the year as a three-digit number, from 000 to 366
%k	Hour of 24-hour clock, from 0 to 23
%l	Hour of 12-hour clock, from 0 to 12
%M	Month name, from January to December
%m	Numeric month value as a two-digit number, from 01 to 12
%p	AM or PM of 12-hour clock
%r	Time on 12-hour clock, as hh:mm:ss followed by AM or PM
%s or %S	Second, from 00 to 59
%T	Time on 24-hour clock, as hh:mm:ss
%v	Week of the year, from 01 to 53, where Monday is the first day of the week; used with %x
%W	Name of the day of the week, fromSunday to Saturday
%x	Year for when Monday is the first day of the week; used with %v
%Y	Year, as a four-digit number
%y	Year, as a two-digit number Note: When parsing, %y assumes 1970 to 2069; 70 results in 1970, 69 results in 2069.
%%	The literal % character

Note: To return the day of the week as a number, from 1 to 7, use the DAY_OF_WEEK function.

DAY

To return the day of the month—as a number from 1 to 31—from a date or timestamp, use the DAY function as DAY(date_value). For example, SELECT DAY("2020-06-15") returns 15.

DAY_OF_WEEK

To return the day of the week—as a number from 1 for Monday to 7 for Sunday—from a date or timestamp, use the DAY_OF_WEEK function as DAY_OF_WEEK(date_value).

EOMONTH

To return the number of the last day of the month from a date or timestamp, use the EOMONTH function, such as EOMONTH(date_value). 

FROM_ISO8601_DATE

To parse an ISO8601-formatted string as a date, use the FROM_ISO8601_DATE function, such as FROM_ISO8601_DATE(date_value). 

INTERVAL

To add or subtract interval unit values for a date or timestamp, use INTERVAL function with a + or - operator, such as +/- INTERVAL value unit. For unit, INTERVAL supports the same values as DATE_ADD. For example:

• DATE '2020-08-08' + INTERVAL '2' day returns 2020-08-10.
• TIMESTAMP '2020-08-08 01:00' + INTERVAL '29' hour returns 2020-08-09 06:00:00.000.
• INTERVAL '2'  day + INTERVAL '3' hour returns 2 03:00:00.000.
• TIMESTAMP '2020-10-31 01:00' - INTERVAL '1' month returns 2020-09-30 01:00:00.000.

MONTH

To return the month—as a number from 1 to 12—from a date or timestamp, use the MONTH function as MONTH(date_value). For example, SELECT MONTH("2020-06-15") returns 6.

NOW

To return the timestamp when the query runs, use the NOW function. For example, SELECT NOW().

QUARTER

To return the quarter of the year—as a number from 1 to 4—from a date or timestamp, use the QUARTER function as QUARTER(date_value). For example, SELECT QUARTER("2020-06-15") returns 2.

YEAR

To return the year—as a number from 1000 to 9990—from a date or timestamp, use the YEAR function as YEAR(date_value). For example, SELECT YEAR("2020-06-15") returns 2020.