Date and Time Functions and Operators#
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 -> 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
)
-
from_iso8601_timestamp
(string) → timestamp with time zone# Parses the ISO 8601 formatted
string
into atimestamp with time zone
.
-
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 usingstring
for the time zone.
-
from_unixtime
(unixtime, hours, minutes) → timestamp with time zone# Returns the UNIX timestamp
unixtime
as a timestamp with time zone usinghours
andminutes
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:
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
x
truncated 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
value
of typeunit
totimestamp
. Subtraction can be performed by using a negative value.
-
date_diff
(unit, timestamp1, timestamp2) → bigint# Returns
timestamp2 - timestamp1
expressed in terms ofunit
.
Duration Function#
The parse_duration
function supports the following units:
Unit |
Description |
---|---|
|
Nanoseconds |
|
Microseconds |
|
Milliseconds |
|
Seconds |
|
Minutes |
|
Hours |
|
Days |
-
parse_duration
(string) → interval# Parses
string
of formatvalue unit
into an interval, wherevalue
is fractional number ofunit
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:
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, 12-hour ( |
|
Seconds ( |
|
Seconds ( |
|
Time, 24-hour ( |
|
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 year1970
but “69” will produce2069
.- 3
This specifier is not supported yet. Consider using
day_of_week()
(it uses1-7
instead of0-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 usingformat
.
-
date_parse
(string, format) → timestamp# Parses
string
into 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
timestamp
as a string usingformat
.
-
parse_datetime
(string, format) → timestamp with time zone# Parses
string
into 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
field
fromx
.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 from1
to366
.
-
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 from0
to23
.
-
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 from1
to4
.
-
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()
.