Date and Time Functions

from_unixtime(unixtime) → timestamp

Returns the UNIX timestamp unixtime as a timestamp.

from_unixtime(unixtime, string) → timestamp with time zone

Returns the UNIX timestamp unixtime as a timestamp with time zone using string for the time zone.

to_unixtime(timestamp) → double

Returns timestamp as a UNIX timestamp.

Truncation Function

The date_trunc function supports the following units:

Unit	Example Truncated Value
second	2001-08-22 03:04:05.000
minute	2001-08-22 03:04:00.000
hour	2001-08-22 03:00:00.000
day	2001-08-22 00:00:00.000
month	2001-08-01 00:00:00.000
quarter	2001-07-01 00:00:00.000
year	2001-01-01 00:00:00.000

The above examples use the timestamp 2001-08-22 03:04:05.321 as the input.

date_trunc(unit, x) → x

Returns x truncated to unit. The supported types for x are TIMESTAMP, DATE, and TIMESTAMP WITH TIME ZONE.

Interval Functions

The functions in this section support the following interval units:

Unit	Description
millisecond	Milliseconds
second	Seconds
minute	Minutes
hour	Hours
day	Days
month	Months
quarter	Quarters of a year
year	Years

date_add(unit, value, x) → x

Adds an interval value of type unit to x. The supported types for x are TIMESTAMP and DATE. Subtraction can be performed by using a negative value.

date_diff(unit, x1, x2) → bigint

Returns x2 - x1 in terms of unit. The supported types for x are TIMESTAMP and DATE.

MySQL Date Functions

The functions in this section use a format string that is compatible with the MySQL date_parse and str_to_date functions. The following table, based on the MySQL manual, describes the format specifiers:

Specifier	Description
%a	Abbreviated weekday name (Sun … Sat)
%b	Abbreviated month name (Jan … Dec)
%c	Month, numeric (1 … 12) [4]
%D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d	Day of the month, numeric (01 … 31) [4]
%e	Day of the month, numeric (1 … 31) [4]
%f	Fraction of second (6 digits for printing: 000000 … 999000; 1 - 9 digits for parsing: 0 … 999999999) [1]
%H	Hour (00 … 23)
%h	Hour (01 … 12)
%I	Hour (01 … 12)
%i	Minutes, numeric (00 … 59)
%j	Day of year (001 … 366)
%k	Hour (0 … 23)
%l	Hour (1 … 12)
%M	Month name (January … December)
%m	Month, numeric (01 … 12) [4]
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S	Seconds (00 … 59)
%s	Seconds (00 … 59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00 … 53), where Sunday is the first day of the week
%u	Week (00 … 53), where Monday is the first day of the week
%V	Week (01 … 53), where Sunday is the first day of the week; used with %X
%v	Week (01 … 53), where Monday is the first day of the week; used with %x
%W	Weekday name (Sunday … Saturday)
%w	Day of the week (0 … 6), where Sunday is the first day of the week [3]
%X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric, four digits
%y	Year, numeric (two digits) [2]
%%	A literal % character
%x	x, for any x not listed above

[1]

Timestamp is truncated to milliseconds.

[2]

When parsing, two-digit year format assumes range 1970 … 2069, so “70” will result in year 1970 but “69” will produce 2069.

[3]

This specifier is not supported yet. Consider using day_of_week() (it uses 1-7 instead of 0-6).

[4] (1,2,3,4)

This specifier does not support 0 as a month or day.

Warning: The following specifiers are not currently supported: %D, %U, %u, %V, %w, %X.

date_format(x, format) → varchar

Formats x as a string using format. x is a timestamp or a timestamp with time zone.

Java Date Functions

The functions in this section leverage a native cpp implementation that follows a format string compatible with JodaTime’s DateTimeFormat pattern format. The symbols currently supported are y, Y, M , d, H, m, s, S, and Z.

parse_datetime(string, format) → timestamp with time zone

Parses string into a timestamp with time zone using format.

Convenience Extraction Functions

These functions support TIMESTAMP, DATE, and TIMESTAMP WITH TIME ZONE input types.

These functions are implemented using std::gmtime which raises an error when input timestamp is too large (for example, > 100’000’000’000’000’000). This behavior is different from Presto Java that allows arbitrary large timestamps.

day(x) → bigint

Returns the day of the month from x.

day_of_month(x) → bigint

This is an alias for day().

day_of_week(x) → bigint

Returns the ISO day of the week from x. The value ranges from 1 (Monday) to 7 (Sunday).

day_of_year(x) → bigint

Returns the day of the year from x. The value ranges from 1 to 366.

dow(x) → bigint

This is an alias for day_of_week().

doy(x) → bigint

This is an alias for day_of_year().

hour(x) → bigint

Returns the hour of the day from x. The value ranges from 0 to 23.

millisecond(x) → int64

Returns the millisecond of the second from x.

minute(x) → bigint

Returns the minute of the hour from x.

month(x) → bigint

Returns the month of the year from x.

quarter(x) → bigint

Returns the quarter of the year from x. The value ranges from 1 to 4.

second(x) → bigint

Returns the second of the minute from x.

week(x) → bigint

Returns the ISO-Week of the year from x. The value ranges from 1 to 53.

week_of_year(x) → bigint

This is an alias for week().

year(x) → bigint

Returns the year from x.

year_of_week(x) → bigint

Returns the year of the ISO week from x.

yow(x) → bigint

This is an alias for year_of_week().