Date and Time Functions¶
- current_date() date ¶
Returns the current date.
- date(x) date ¶
This is an alias for
CAST(x AS date)
.
- 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.
- to_unixtime(timestamp) double ¶
Returns
timestamp
as a UNIX timestamp.
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) x ¶
Returns
x
truncated tounit
. The supported types forx
are TIMESTAMP, DATE, and TIMESTAMP WITH TIME ZONE.
Interval Functions¶
The functions in this section support the following interval units:
Unit |
Description |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
- date_add(unit, value, x) x ¶
Adds an interval
value
of typeunit
tox
. The supported types forx
are TIMESTAMP, DATE, and TIMESTAMP WITH TIME ZONE. Subtraction can be performed by using a negative value.
- date_diff(unit, x1, x2) bigint ¶
Returns
x2 - x1
in terms ofunit
. The supported types forx
are TIMESTAMP and DATE.
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 |
|
|
Warning: The following specifiers are not currently supported: %D
, %U
, %u
, %V
, %w
, %X
.
- date_format(x, format) varchar ¶
Formats
x
as a string usingformat
.x
is a timestamp or a timestamp with time zone.
Java Date Functions¶
The functions in this section leverage a native cpp implementation that follows
a format string compatible with JodaTime’s DateTimeFormat
pattern format. The symbols currently supported are y
, Y
, M
, d
,
H
, m
, s
, S
, and Z
.
- parse_datetime(string, format) timestamp with time zone ¶
Parses string into a timestamp with time zone using
format
.
Convenience Extraction Functions¶
These functions support TIMESTAMP, DATE, and TIMESTAMP WITH TIME ZONE input types.
For these functions, the input timestamp has range limitations on seconds and nanoseconds. Seconds should be in the range [INT64_MIN/1000 - 1, INT64_MAX/1000], nanoseconds should be in the range [0, 999999999]. This behavior is different from Presto Java that allows arbitrary large timestamps.
- 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 from 0 to 23.
- last_day_of_month(x) date ¶
Returns the last day of the month.
- millisecond(x) int64 ¶
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
.
- 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()
.