date and time data types
.
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:
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 3
digits of subsecond precision,
current_timestamp(p)
Returns the current timestamp with time zone as
timestamp-with-time-zone-data-typeof the start of the query, with
p` digits of subsecond precision:
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(3) with time zone
Parses the ISO 8601 formatted 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:
from_iso8601_timestamp_nanos(string)
→ timestamp(9) with time zone
Parses the ISO 8601 formatted date and time string
. The time zone
defaults to the session time zone:
from_iso8601_date(string)
→ date
Parses the ISO 8601 formatted date 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:
at_timezone(timestamp, zone)
→ timestamp(p) with time zone
Returns the timestamp specified in 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:
with_timezone(timestamp, zone)
→ timestamp(p) with time zone
Returns the timestamp specified in timestamp
with the time zone specified in zone
with precision p
.
from_unixtime(unixtime)
→ timestamp(3) with time zone
Returns the UNIX timestamp unixtime
as a timestamp with time zone.
unixtime
is the number of seconds since 1970-01-01 00:00:00 UTC
.
from_unixtime(unixtime, zone)
→ timestamp(3) with time zone
Returns the UNIX timestamp 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
.
from_unixtime(unixtime, hours, minutes)
→ timestamp(3) with time zone
Returns the UNIX timestamp 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.
from_unixtime_nanos(unixtime)
→ timestamp(9) with time zone
Returns the UNIX timestamp unixtime
as a timestamp with time zone.
unixtime
is the number of nanoseconds since
1970-01-01 00:00:00.000000000 UTC
:
now()
→ timestamp(3) 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.
date_trunc
function supports the following units:
Unit | Example | Truncated Value |
---|---|---|
second | 2001-08-22 03:04:05.000 | 2001-08-22 03:04:05.000 |
minute | 2001-08-22 03:04:00.000 | 2001-08-22 03:04:00.000 |
hour | 2001-08-22 03:00:00.000 | 2001-08-22 03:00:00.000 |
day | 2001-08-22 00:00:00.000 | 2001-08-22 00:00:00.000 |
week | 2001-08-20 00:00:00.000 | 2001-08-20 00:00:00.000 |
month | 2001-08-01 00:00:00.000 | 2001-08-01 00:00:00.000 |
quarter | 2001-07-01 00:00:00.000 | 2001-07-01 00:00:00.000 |
year | 2001-01-01 00:00:00.000 | 2001-01-01 00:00:00.000 |
2001-08-22 03:04:05.321
as the
input.
date_trunc(unit, x)
→ [same as input]
Returns 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 |
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
:
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 string
of format value unit
into an interval, where value
is fractional number of unit
values:
human_readable_seconds(double)
→ varchar
Formats the double value of 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 )1 |
%D | Day of the month with English suffix (0th , 1st , 2nd , 3rd , …) |
%d | Day of the month, numeric (01 .. 31 )2 |
%e | Day of the month, numeric (1 .. 31 )3 |
%f | Fraction of second (6 digits for printing: 000000 .. 999000 ; 1 - 9 digits for parsing: 0 .. 999999999 )4 |
%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 )5 |
%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 week6 |
%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)7 |
%% | A literal % character |
%x | x , for any x not |
%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
:
format_datetime(timestamp, format)
→ varchar
Formats timestamp
as a string using format
.
parse_datetime(string, format)
→ timestamp
Parses 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.
extract(field FROM x)
→ bigint
Returns field
from x
:
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
.
0
as a month or day. ↩
0
as a month or day. ↩
0
as a month or day. ↩
0
as a month or day. ↩
day_of_week
(it uses 1-7
instead of 0-6
). ↩
1970
.. 2069
, so “70” will result in year 1970
but “69” will produce 2069
. ↩