Date and time functions and operators#
These functions and operators operate on date and time data types.
Date and time operators#
Operator |
Example |
Result |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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# Returns the current date as of the start of the query.
-
current_time# Returns the current time with time zone as of the start of the query.
-
current_timestamp# Returns the current timestamp with time zone as of the start of the query, with
3digits of subsecond precision,
-
current_timestamp(p) Returns the current timestamp with time zone as of the start of the query, with
pdigits of subsecond precision:SELECT current_timestamp(6); -- 2020-06-24 08:25:31.759993 America/Los_Angeles
-
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)
-
from_iso8601_timestamp(string) → timestamp(3) with time zone# Parses the ISO 8601 formatted date
string, optionally with time and time zone, into atimestamp(3) with time zone. The time defaults to00:00:00.000, and the time zone defaults to the session time zone:SELECT from_iso8601_timestamp('2020-05-11'); -- 2020-05-11 00:00:00.000 America/Vancouver SELECT from_iso8601_timestamp('2020-05-11T11:15:05'); -- 2020-05-11 11:15:05.000 America/Vancouver SELECT from_iso8601_timestamp('2020-05-11T11:15:05.055+01:00'); -- 2020-05-11 11:15:05.055 +01:00
-
from_iso8601_timestamp_nanos(string) → timestamp(9) with time zone# Parses the ISO 8601 formatted date
string, optionally with time and time zone, into atimestamp(9) with time zone. The time defaults to00:00:00.000000000, and the time zone defaults to the session time zone:SELECT from_iso8601_timestamp('2020-05-11'); -- 2020-05-11 00:00:00.000000000 America/Vancouver SELECT from_iso8601_timestamp('2020-05-11T11:15:05'); -- 2020-05-11 11:15:05.000000000 America/Vancouver SELECT from_iso8601_timestamp('2020-05-11T11:15:05.123456789+01:00'); -- 2020-05-11 11:15:05.123456789 +01:00
-
from_iso8601_date(string) → date# Parses the ISO 8601 formatted date
stringinto adate. The date can be a calendar date, a week date using ISO week numbering, or year and day of year combined:SELECT from_iso8601_date('2020-05-11'); -- 2020-05-11 SELECT from_iso8601_date('2020-W10'); -- 2020-03-02 SELECT from_iso8601_date('2020-123'); -- 2020-05-02
-
at_timezone(timestamp, zone) → timestamp(p) with time zone# Change the time zone component of
timestampwith precisionptozonewhile preserving the instant in time.
-
with_timezone(timestamp, zone) → timestamp(p) with time zone# Returns a timestamp with time zone from
timestampwith precisionpandzone.
-
from_unixtime(unixtime)# Returns the UNIX timestamp
unixtimeas a timestamp.unixtimeis the number of seconds since1970-01-01 00:00:00 UTC.
-
from_unixtime(unixtime, zone) → timestamp(3) with time zone Returns the UNIX timestamp
unixtimeas a timestamp with time zone usingzonefor the time zone.unixtimeis the number of seconds since1970-01-01 00:00:00 UTC.
-
from_unixtime(unixtime, hours, minutes) → timestamp(3) with time zone Returns the UNIX timestamp
unixtimeas a timestamp with time zone usinghoursandminutesfor the time zone offset.unixtimeis the number of seconds since1970-01-01 00:00:00indoubledata type.
-
from_unixtime_nanos(unixtime)# Returns the UNIX timestamp
unixtimeas a timestamp.unixtimeis the number of nanoseconds since1970-01-01 00:00:00.000000000 UTC.
-
localtime# Returns the current time as of the start of the query.
-
localtimestamp# Returns the current timestamp as of the start of the query, with
3digits of subsecond precision.
-
localtimestamp(p) Returns the current timestamp as of the start of the query, with
pdigits of subsecond precision:SELECT localtimestamp(6); -- 2020-06-10 15:55:23.383628
-
now() → timestamp(3) with time zone# This is an alias for
current_timestamp.
-
to_iso8601(x) → varchar# Formats
xas an ISO 8601 string.xcan be date, timestamp, or timestamp with time zone.
-
to_milliseconds(interval) → bigint# Returns the day-to-second
intervalas milliseconds.
-
to_unixtime(timestamp) → double# Returns
timestampas a UNIX timestamp.
Note
The following SQL-standard functions do not use parenthesis:
current_datecurrent_timecurrent_timestamplocaltimelocaltimestamp
Truncation function#
The date_trunc function supports the following units:
Unit |
Example Truncated Value |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The above examples use the timestamp 2001-08-22 03:04:05.321 as the input.
-
date_trunc(unit, x) → [same as input]# Returns
xtruncated tounit.
Interval functions#
The functions in this section support the following interval units:
Unit |
Description |
|---|---|
|
Milliseconds |
|
Seconds |
|
Minutes |
|
Hours |
|
Days |
|
Weeks |
|
Months |
|
Quarters of a year |
|
Years |
-
date_add(unit, value, timestamp) → [same as input]# Adds an interval
valueof typeunittotimestamp. Subtraction can be performed by using a negative value:SELECT date_add('second', 86, TIMESTAMP '2020-03-01 00:00:00'); -- 2020-03-01 00:01:26.000 SELECT date_add('hour', 9, TIMESTAMP '2020-03-01 00:00:00'); -- 2020-03-01 09:00:00.000 SELECT date_add('day', -1, TIMESTAMP '2020-03-01 00:00:00 UTC'); -- 2020-02-29 00:00:00.000 UTC
-
date_diff(unit, timestamp1, timestamp2) → bigint# Returns
timestamp2 - timestamp1expressed in terms ofunit:SELECT date_diff('second', TIMESTAMP '2020-03-01 00:00:00', TIMESTAMP '2020-03-02 00:00:00'); -- 86400 SELECT date_diff('hour', TIMESTAMP '2020-03-01 00:00:00 UTC', TIMESTAMP '2020-03-02 00:00:00 UTC'); -- 24 SELECT date_diff('day', DATE '2020-03-01', DATE '2020-03-02'); -- 1 SELECT date_diff('second', TIMESTAMP '2020-06-01 12:30:45.000000000', TIMESTAMP '2020-06-02 12:30:45.123456789'); -- 86400 SELECT date_diff('millisecond', TIMESTAMP '2020-06-01 12:30:45.000000000', TIMESTAMP '2020-06-02 12:30:45.123456789'); -- 86400123
Duration function#
The parse_duration function supports the following units:
Unit |
Description |
|---|---|
|
Nanoseconds |
|
Microseconds |
|
Milliseconds |
|
Seconds |
|
Minutes |
|
Hours |
|
Days |
-
parse_duration(string) → interval# Parses
stringof formatvalue unitinto an interval, wherevalueis fractional number ofunitvalues: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
-
human_readable_seconds(double) → varchar# Returns
secondsexpressed in terms ofhuman readable interval:SELECT human_readable_seconds(56363463); -- 93 weeks, 1 day, 8 hours, 31 minutes, 3 seconds SELECT human_readable_seconds(61); -- 1 minute, 1 second
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 |
|---|---|
|
Abbreviated weekday name ( |
|
Abbreviated month name ( |
|
Month, numeric ( |
|
Day of the month with English suffix ( |
|
Day of the month, numeric ( |
|
Day of the month, numeric ( |
|
Fraction of second (6 digits for printing: |
|
Hour ( |
|
Hour ( |
|
Hour ( |
|
Minutes, numeric ( |
|
Day of year ( |
|
Hour ( |
|
Hour ( |
|
Month name ( |
|
Month, numeric ( |
|
|
|
Time of day, 12-hour (equivalent to |
|
Seconds ( |
|
Seconds ( |
|
Time of day, 24-hour (equivalent to |
|
Week ( |
|
Week ( |
|
Week ( |
|
Week ( |
|
Weekday name ( |
|
Day of the week ( |
|
Year for the week where Sunday is the first day of the week, numeric, four digits; used with |
|
Year for the week, where Monday is the first day of the week, numeric, four digits; used with |
|
Year, numeric, four digits |
|
Year, numeric (two digits) 2 |
|
A literal |
|
|
- 1
Timestamp is truncated to milliseconds.
- 2
When parsing, two-digit year format assumes range
1970..2069, so “70” will result in year1970but “69” will produce2069.- 3
This specifier is not supported yet. Consider using
day_of_week()(it uses1-7instead of0-6).- 4(1,2,3,4)
This specifier does not support
0as a month or day.
Warning
The following specifiers are not currently supported: %D %U %u %V %w %X
-
date_format(timestamp, format) → varchar# Formats
timestampas a string usingformat.
-
date_parse(string, format)# Parses
stringinto a timestamp usingformat.
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
timestampas a string usingformat.
-
parse_datetime(string, format) → timestamp with time zone# Parses
stringinto a timestamp with time zone usingformat.
Extraction function#
The extract function supports the following fields:
Field |
Description |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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
fieldfromx.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_week(x) → bigint# Returns the ISO day of the week from
x. The value ranges from1(Monday) to7(Sunday).
-
day_of_year(x) → bigint# Returns the day of the year from
x. The value ranges from1to366.
-
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 from0to23.
-
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 from1to4.
-
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.
-
year(x) → bigint# Returns the year from
x.
-
yow(x) → bigint# This is an alias for
year_of_week().