6.13. Date and Time Functions and Operators

Date and Time Operators

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

currentdate -> 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)

date(_x) → date

This is an alias for CAST(x AS date).

fromiso8601_timestamp(_string) → timestamp with time zone

Parses the ISO 8601 formatted string into a timestamp with time zone.

fromiso8601_date(_string) → date

Parses the ISO 8601 formatted string into a date.

fromunixtime(_unixtime) → timestamp

Returns the UNIX timestamp unixtime as a timestamp.

fromunixtime(_unixtime, string) → timestamp with time zone

Returns the UNIX timestamp unixtime as a timestamp with time zoneusing string for the time zone.

fromunixtime(_unixtime, hours, minutes) → timestamp with time zone

Returns the UNIX timestamp unixtime as a timestamp with time zoneusing hours and minutes 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 currenttimestamp.

to_iso8601(_x) → varchar

Formats x as an ISO 8601 string. x can be date, timestamp, ortimestamp with time zone.

tomilliseconds(_interval) → bigint

Returns the day-to-second interval as milliseconds.

tounixtime(_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:

The above examples use the timestamp 2001-08-22 03:04:05.321 as the input.

datetrunc(_unit, x) → [same as input]

Returns x truncated to unit.

Interval Functions

The functions in this section support the following interval units:

dateadd(_unit, value, timestamp) → [same as input]

Adds an interval value of type unit to timestamp.Subtraction can be performed by using a negative value.

datediff(_unit, timestamp1, timestamp2) → bigint

Returns timestamp2 - timestamp1 expressed in terms of unit.

Duration Function

The parse_duration function supports the following units:

parseduration(_string) → interval

Parses string of format value unit into an interval, wherevalue is fractional number of unit 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 withthe 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 (hhss followed by AM or PM)
%S	Seconds (00 .. 59)
%s	Seconds (00 .. 59)
%T	Time, 24-hour (hhss)
%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

dateformat(_timestamp, format) → varchar

Formats timestamp as a string using format.

dateparse(_string, format) → timestamp

Parses string into a timestamp using format.

Java Date Functions

The functions in this section use a format string that is compatible withJodaTime’s DateTimeFormat pattern format.

formatdatetime(_timestamp, format) → varchar

Formats timestamp as a string using format.

parsedatetime(_string, format) → timestamp with time zone

Parses string into a timestamp with time zone using format.

Extraction Function

The extract function supports the following fields:

The types supported by the extract function vary depending on thefield to be extracted. Most fields support all date and time types.

extract(field FROM x) → bigint

Returns field from x.


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.

dayof_month(_x) → bigint

This is an alias for day().

dayof_week(_x) → bigint

Returns the ISO day of the week from x.The value ranges from 1 (Monday) to 7 (Sunday).

dayof_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.

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.

timezonehour(_timestamp) → bigint

Returns the hour of the time zone offset from timestamp.

timezoneminute(_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.

weekof_year(_x) → bigint

This is an alias for week().

year(x) → bigint

Returns the year from x.

yearof_week(_x) → bigint

Returns the year of the ISO week from x.

yow(x) → bigint

This is an alias for year_of_week().

原文: https://prestodb.io/docs/current/functions/datetime.html