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 |
AT TIME ZONE
operator sets the time zone of a timestamp:
3
digits of subsecond precision,
p
digits of subsecond precision:
America/Los_Angeles
) or as fixed offset from UTC (e.g., +08:35
)
CAST(x AS date)
.
string
, optionally with time and time
zone, into a timestamp(3) with time zone
. The time defaults to
00:00:00.000
, and the time zone defaults to the session time zone:
string
. The time zone
defaults to the session time zone:
string
into a date
. The date can
be a calendar date, a week date using ISO week numbering, or year and day
of year combined:
timestamp
with the time zone
converted from the session time zone to the time zone specified in zone
with precision p
. In the following example, the session time zone is set
to America/New_York
, which is three hours ahead of
America/Los_Angeles
:
timestamp
with the time zone
specified in zone
with precision p
:
unixtime
as a timestamp with time zone. unixtime
is the
number of seconds since 1970-01-01 00:00:00 UTC
.
unixtime
as a timestamp with time zone
using zone
for the time zone. unixtime
is the number of seconds
since 1970-01-01 00:00:00 UTC
.
unixtime
as a timestamp with time zone
using hours
and minutes
for the time zone offset. unixtime
is
the number of seconds since 1970-01-01 00:00:00
in double
data type.
unixtime
as a timestamp with time zone. unixtime
is the
number of nanoseconds since 1970-01-01 00:00:00.000000000 UTC
:
3
digits of subsecond precision.
p
digits of subsecond precision:
current_timestamp
.
x
as an ISO 8601 string. x
can be date, timestamp, or
timestamp with time zone.
interval
as milliseconds.
timestamp
as a UNIX timestamp.
current_date
current_time
current_timestamp
localtime
localtimestamp
date_trunc
function supports the following units:
Unit | Example Truncated Value |
---|---|
millisecond | 2001-08-22 03:04:05.321 |
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 |
2001-08-22 03:04:05.321
as the input.
x
truncated to unit
:
Unit | Description |
---|---|
millisecond | Milliseconds |
second | Seconds |
minute | Minutes |
hour | Hours |
day | Days |
week | Weeks |
month | Months |
quarter | Quarters of a year |
year | Years |
value
of type unit
to timestamp
.
Subtraction can be performed by using a negative value:
timestamp2 - timestamp1
expressed in terms of unit
:
parse_duration
function supports the following units:
Unit | Description |
---|---|
ns | Nanoseconds |
us | Microseconds |
ms | Milliseconds |
s | Seconds |
m | Minutes |
h | Hours |
d | Days |
string
of format value unit
into an interval, where
value
is fractional number of unit
values:
seconds
into a human readable string containing
weeks
, days
, hours
, minutes
, and seconds
:
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 ), this specifier does not support 0 as a month. |
%D | Day of the month with English suffix (0th , 1st , 2nd , 3rd , …) |
%d | Day of the month, numeric (01 .. 31 ), this specifier does not support 0 as a month or day. |
%e | Day of the month, numeric (1 .. 31 ), this specifier does not support 0 as a day. |
%f | Fraction of second (6 digits for printing: 000000 .. 999000 ; 1 - 9 digits for parsing: 0 .. 999999999 ), timestamp is truncated to milliseconds. |
%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 ), this specifier does not support 0 as a month. |
%p | AM or PM |
%r | Time of day, 12-hour (equivalent to %h:%i:%s %p ) |
%S | Seconds (00 .. 59 ) |
%s | Seconds (00 .. 59 ) |
%T | Time of day, 24-hour (equivalent to %H:%i:%s ) |
%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, this specifier is not supported,consider using day_of_week (it uses 1-7 instead of 0-6 ). |
%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), when parsing, two-digit year format assumes range 1970 .. 2069 , so “70” will result in year 1970 but “69” will produce 2069 . |
%% | A literal % character |
%x | x , for any x not listed above |
%D %U %u %V %w %X
timestamp
as a string using format
:
string
into a timestamp using format
:
timestamp
as a string using format
.
string
into a timestamp with time zone using format
.
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 |
extract
function vary depending on the
field to be extracted. Most fields support all date and time types.
field
from x
:
x
.
day
.
x
.
The value ranges from 1
(Monday) to 7
(Sunday).
x
.
The value ranges from 1
to 366
.
day_of_week
.
day_of_year
.
x
.
The value ranges from 0
to 23
.
x
.
x
.
x
.
x
.
The value ranges from 1
to 4
.
x
.
timestamp
.
timestamp
.
x
.
The value ranges from 1
to 53
.
x
.
x
.