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 that DATE_DIFF
only applies to Date objects and you may be required to cast other objects in order for this function to work.
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,
|
%% |
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, month_number_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.