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 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, from`Sun` 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, from`Sunday` 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, 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.

Support

Workiva Support Center