Date and Time Functions

current_date() → date

Returns the current date.

date(x) → date

This is an alias for CAST(x AS date).

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
week	2001-08-20 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, DATE, and TIMESTAMP WITH TIME ZONE. 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.

For these functions, the input timestamp has range limitations on seconds and nanoseconds. Seconds should be in the range [INT64_MIN/1000 - 1, INT64_MAX/1000], nanoseconds should be in the range [0, 999999999]. 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.

last_day_of_month(x) → date

Returns the last day of the month.

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.

timezone_hour(timestamp) → bigint

Returns the hour of the time zone offset from timestamp.

timezone_minute(timestamp) → bigint

Returns the minute of the time zone offset from timestamp.

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().