7.13. Date and Time Functions and Operators#
Date and Time Operators#
| Operator | Example | Result |
|---|---|---|
+ |
date '2012-08-08' + interval '2' day |
2012-08-10 |
+ |
time '01:00' + interval '3' hour |
04:00:00.000 |
+ |
timestamp '2012-08-08 01:00' + interval '29' hour |
2012-08-09 06:00:00.000 |
+ |
timestamp '2012-10-31 01:00' + interval '1' month |
2012-11-30 01:00:00.000 |
+ |
interval '2' day + interval '3' hour |
2 03:00:00.000 |
+ |
interval '3' year + interval '5' month |
3-5 |
- |
date '2012-08-08' - interval '2' day |
2012-08-06 |
- |
time '01:00' - interval '3' hour |
22:00:00.000 |
- |
timestamp '2012-08-08 01:00' - interval '29' hour |
2012-08-06 20:00:00.000 |
- |
timestamp '2012-10-31 01:00' - interval '1' month |
2012-09-30 01:00:00.000 |
- |
interval '2' day - interval '3' hour |
1 21:00:00.000 |
- |
interval '3' year - interval '5' month |
2-7 |
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.
-
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
stringinto atimestamp with time zone.
-
from_iso8601_date(string) → date# Parses the ISO 8601 formatted
stringinto adate.
-
at_timezone(timestamp, zone) → timestamp with time zone# Change the time zone component of
timestamptozonewhile preserving the instant in time.
-
with_timezone(timestamp, zone) → timestamp with time zone# Returns a timestamp with time zone from
timestampandzone.
-
from_unixtime(unixtime) → timestamp# Returns the UNIX timestamp
unixtimeas a timestamp.unixtimeis the number of seconds since1970-01-01 00:00:00.
-
from_unixtime(unixtime, zone) → timestamp 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.
-
from_unixtime(unixtime, hours, minutes) → timestamp 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:00.
-
localtime# Returns the current time as of the start of the query.
-
localtimestamp# 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
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 |
|---|---|
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) → [same as input]# Returns
xtruncated tounit.
Interval Functions#
The functions in this section support the following interval units:
| Unit | Description |
|---|---|
millisecond |
Milliseconds |
second |
Seconds |
minute |
Minutes |
hour |
Hours |
day |
Days |
week |
Weeks |
month |
Months |
quarter |
Quarters of a year |
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
Duration Function#
The parse_duration function supports the following units:
| Unit | Description |
|---|---|
ns |
Nanoseconds |
us |
Microseconds |
ms |
Milliseconds |
s |
Seconds |
m |
Minutes |
h |
Hours |
d |
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
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(timestamp, format) → varchar# Formats
timestampas a string usingformat.
-
date_parse(string, format) → timestamp# 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 |
|---|---|
YEAR |
year() |
QUARTER |
quarter() |
MONTH |
month() |
WEEK |
week() |
DAY |
day() |
DAY_OF_MONTH |
day() |
DAY_OF_WEEK |
day_of_week() |
DOW |
day_of_week() |
DAY_OF_YEAR |
day_of_year() |
DOY |
day_of_year() |
YEAR_OF_WEEK |
year_of_week() |
YOW |
year_of_week() |
HOUR |
hour() |
MINUTE |
minute() |
SECOND |
second() |
TIMEZONE_HOUR |
timezone_hour() |
TIMEZONE_MINUTE |
timezone_minute() |
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().