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.

AT TIME ZONE

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

SELECT timestamp '2020-10-31 01:00 UTC';
2020-10-31 01:00:00.000 UTC

SELECT timestamp '2020-10-31 01:00 UTC' AT TIME ZONE 'America/Los_Angeles'; 
2020-10-30 18:00:00.000 America/Los_Angeles

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.