Date and Time Functions and Operators

Date and Time Operators

Time Zone Conversion

The AT TIME ZONE operator sets the time zone of a timestamp:

SELECT timestamp '2012-10-31 01:00 UTC';
2012-10-31 01:00:00.000 UTC
SELECT timestamp '2012-10-31 01:00 UTC' AT TIME ZONE 'America/Los_Angeles';
2012-10-30 18:00:00.000 America/Los_Angeles

Date and Time Functions

current_date -> date()

Returns the current date as of the start of the query.

current_time -> time with time zone()

Returns the current time as of the start of the query.

current_timestamp -> timestamp with time zone()

Returns the current timestamp as of the start of the query.

current_timezone() -> varchar()

Returns the current time zone in the format defined by IANA (e.g., America/Los_Angeles) or as fixed offset from UTC (e.g., +08:35)

date(x) -> date()

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

last_day_of_month(x) -> date()

Returns the last day of the month.

from_iso8601_timestamp(string) -> timestamp with time zone()

Parses the ISO 8601 formatted string into a timestamp with time zone.

from_iso8601_date(string) -> date()

Parses the ISO 8601 formatted string into a 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.

from_unixtime(unixtime, hours, minutes) -> timestamp with time zone()

Returns the UNIX timestamp unixtime as a timestamp with time zone using hours and minutes for the time zone offset.

localtime -> time()

Returns the current time as of the start of the query.

localtimestamp -> timestamp()

Returns the current timestamp as of the start of the query.

now() -> timestamp with time zone()

This is an alias for current_timestamp.

to_iso8601(x) -> varchar()

Formats x as an ISO 8601 string. x can be date, timestamp, or timestamp with time zone.

to_milliseconds(interval) -> bigint()

Returns the day-to-second interval as milliseconds.

to_unixtime(timestamp) -> double()

Returns timestamp as a UNIX timestamp.

Note

The following SQL-standard functions do not use parenthesis:

• current_date

• current_time

• current_timestamp

• localtime

• localtimestamp

Truncation Function

The date_trunc function supports the following units:

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

date_trunc(unit, x) -> [same as input]()

Returns x truncated to unit.

Interval Functions

The functions in this section support the following interval units:

date_add(unit, value, timestamp) -> [same as input]()

Adds an interval value of type unit to timestamp. Subtraction can be performed by using a negative value.

date_diff(unit, timestamp1, timestamp2) -> bigint()

Returns timestamp2 - timestamp1 expressed in terms of unit.

Duration Function

The parse_duration function supports the following units:

parse_duration(string) -> interval()

Parses string of format value unit into an interval, where value is fractional number of unit values:

SELECT parse_duration('42.8ms'); -- 0 00:00:00.043
SELECT parse_duration('3.81 d'); -- 3 19:26:24.000
SELECT parse_duration('5m');     -- 0 00:05:00.000

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:

[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(timestamp, format) -> varchar()

Formats timestamp as a string using format.

date_parse(string, format) -> timestamp()

Parses string into a timestamp using format.

Java Date Functions

The functions in this section use a format string that is compatible with JodaTime’s DateTimeFormat pattern format.

format_datetime(timestamp, format) -> varchar()

Formats timestamp as a string using format.

parse_datetime(string, format) -> timestamp with time zone()

Parses string into a timestamp with time zone using format.

Extraction Function

The extract function supports the following fields:

The types supported by the extract function vary depending on the field to be extracted. Most fields support all date and time types.

extract(field FROM x) -> bigint()

Returns field from x.

Note

This SQL-standard function uses special syntax for specifying the arguments.

Convenience Extraction Functions

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

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