Skip to content

Latest commit

 

History

History
250 lines (142 loc) · 19.9 KB

code.md

File metadata and controls

250 lines (142 loc) · 19.9 KB

7.13. Date and Time Functions and Operators

Date and Time Operators

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

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)
  • date(x) → date
  • This is an alias for CAST(x AS date).
  • from_iso8601_timestamp(string) → timestamp with time zone
  • Parses the ISO 8601 formatted string into a timestamp with time zone.
  • from_iso8601_date(string) → date
  • Parses the ISO 8601 formatted string into a 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 using string for the time zone.
  • from_unixtime(unixtime, hours, minutes) → timestamp with time zone
  • Returns the UNIX timestamp unixtime as a timestamp with time zone using 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 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
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

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 to unit.

Interval Functions

The functions in this section support the following interval units:

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.

Duration Function

The 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: 
    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
%a Abbreviated weekday name (Sun .. Sat)
%b Abbreviated month name (Jan .. Dec)
%c Month, numeric (1 .. 12) [[4]](#z)
%D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d Day of the month, numeric (01 .. 31) [[4]](#z)
%e Day of the month, numeric (1 .. 31) [[4]](#z)
%f Fraction of second (6 digits for printing: 000000 .. 999000; 1 - 9 digits for parsing: 0 .. 999999999) [[1]](#f)
%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]](#z)
%p AM or PM
%r Time, 12-hour (hh:mm:ss followed by AM or PM)
%S Seconds (00 .. 59)
%s Seconds (00 .. 59)
%T Time, 24-hour (hh:mm:ss)
%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]](#w)
%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]](#y)
%% A literal % character
%x x, for any x not listed above
[[1]](#id4)Timestamp is truncated to milliseconds.
[[2]](#id7)When parsing, two-digit year format assumes range 1970 .. 2069, so “70” will result in year 1970 but “69” will produce 2069.
[[3]](#id6)This specifier is not supported yet. Consider using [day_of_week()](#day_of_week) (it uses 1-7 instead of 0-6).
[4]([1](#id1), [2](#id2), [3](#id3), [4](#id5)) 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 using format.
  • date_parse(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 with JodaTime’s DateTimeFormat pattern format.

  • format_datetime(timestamp, format) → varchar
  • Formats timestamp as a string using format.
  • parse_datetime(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:

Field Description
YEAR [year()](#year)
QUARTER [quarter()](#quarter)
MONTH [month()](#month)
WEEK [week()](#week)
DAY [day()](#day)
DAY_OF_MONTH [day()](#day)
DAY_OF_WEEK [day_of_week()](#day_of_week)
DOW [day_of_week()](#day_of_week)
DAY_OF_YEAR [day_of_year()](#day_of_year)
DOY [day_of_year()](#day_of_year)
YEAR_OF_WEEK [year_of_week()](#year_of_week)
YOW [year_of_week()](#year_of_week)
HOUR [hour()](#hour)
MINUTE [minute()](#minute)
SECOND [second()](#second)
TIMEZONE_HOUR [timezone_hour()](#timezone_hour)
TIMEZONE_MINUTE [timezone_minute()](#timezone_minute)

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 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.
  • day_of_month(x) → bigint
  • This is an alias for [day()](#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()](#day_of_week).
  • doy(x) → bigint
  • This is an alias for [day_of_year()](#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_of_year(x) → bigint
  • This is an alias for [week()](#week).
  • year(x) → bigint
  • Returns the year from x.
  • yow(x) → bigint
  • This is an alias for [year_of_week()](#year_of_week).