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,
|
%% |
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.