Date and Time Functions and Operators

Date and Time Operators

OperatorExampleResult
+date '2012-08-08' + interval '2' day2012-08-10
+time '01:00' + interval '3' hour04:00:00.000
+timestamp '2012-08-08 01:00' + interval '29' hour2012-08-09 06:00:00.000
+timestamp '2012-10-31 01:00' + interval '1' month2012-11-30 01:00:00.000
+interval '2' day + interval '3' hour2 03:00:00.000
+interval '3' year + interval '5' month3-5
-date '2012-08-08' - interval '2' day2012-08-06
-time '01:00' - interval '3' hour22:00:00.000
-timestamp '2012-08-08 01:00' - interval '29' hour2012-08-06 20:00:00.000
-timestamp '2012-10-31 01:00' - interval '1' month2012-09-30 01:00:00.000
-interval '2' day - interval '3' hour1 21:00:00.000
-interval '3' year - interval '5' month2-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

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.

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

at_timezone(timestamp, zone) -> timestamp with time zone

Change the time zone component of timestamp to zone while preserving the instant in time.

with_timezone(timestamp, zone) -> timestamp with time zone

Returns a timestamp with time zone from timestamp and zone.

from_unixtime(unixtime) -> timestamp

Returns the UNIX timestamp unixtime as a timestamp. unixtime is the number of seconds since 1970-01-01 00:00:00.

from_unixtime(unixtime, zone) -> timestamp 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.

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. unixtime is the number of seconds since 1970-01-01 00:00:00.

localtime

Returns the current time as of the start of the query.

localtimestamp

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:

UnitExample Truncated Value
second2001-08-22 03:04:05.000
minute2001-08-22 03:04:00.000
hour2001-08-22 03:00:00.000
day2001-08-22 00:00:00.000
week2001-08-20 00:00:00.000
month2001-08-01 00:00:00.000
quarter2001-07-01 00:00:00.000
year2001-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:

SELECT date_add('second', 86, TIMESTAMP '2020-03-01 00:00:00'); -- 2020-03-01 00:01:26.000
SELECT date_add('hour', 9, TIMESTAMP '2020-03-01 00:00:00'); -- 2020-03-01 09:00:00.000
SELECT date_add('day', -1, TIMESTAMP '2020-03-01 00:00:00 UTC'); -- 2020-02-29 00:00:00.000 UTC

date_diff(unit, timestamp1, timestamp2) -> bigint

Returns timestamp2 - timestamp1 expressed in terms of unit:

SELECT date_diff('second', TIMESTAMP '2020-03-01 00:00:00', TIMESTAMP '2020-03-02 00:00:00'); -- 86400
SELECT date_diff('hour', TIMESTAMP '2020-03-01 00:00:00 UTC', TIMESTAMP '2020-03-02 00:00:00 UTC'); -- 24
SELECT date_diff('day', DATE '2020-03-01', DATE '2020-03-02'); -- 1

Duration Function

The parse_duration function supports the following units:

UnitDescription
nsNanoseconds
usMicroseconds
msMilliseconds
sSeconds
mMinutes
hHours
dDays

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:

SpecifierDescription
%aAbbreviated weekday name (Sun .. Sat)
%bAbbreviated month name (Jan .. Dec)
%cMonth, numeric (1 .. 12)1
%DDay of the month with English suffix (0th, 1st, 2nd, 3rd, ...)
%dDay of the month, numeric (01 .. 31)2
%eDay of the month, numeric (1 .. 31)3
%fFraction of second (6 digits for printing: 000000 .. 999000; 1 - 9 digits for parsing: 0 .. 999999999)4
%HHour (00 .. 23)
%hHour (01 .. 12)
%IHour (01 .. 12)
%iMinutes, numeric (00 .. 59)
%jDay of year (001 .. 366)
%kHour (0 .. 23)
%lHour (1 .. 12)
%MMonth name (January .. December)
%mMonth, numeric (01 .. 12)5
%pAM or PM
%rTime, 12-hour (hh:mm:ss followed by AM or PM)
%SSeconds (00 .. 59)
%sSeconds (00 .. 59)
%TTime, 24-hour (hh:mm:ss)
%UWeek (00 .. 53), where Sunday is the first day of the week
%uWeek (00 .. 53), where Monday is the first day of the week
%VWeek (01 .. 53), where Sunday is the first day of the week; used with %X
%vWeek (01 .. 53), where Monday is the first day of the week; used with %x
%WWeekday name (Sunday .. Saturday)
%wDay of the week (0 .. 6), where Sunday is the first day of the week6
%XYear for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%xYear for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%YYear, numeric, four digits
%yYear, numeric (two digits)7
%%A literal % character
%xx, for any x not listed above
caution

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:

FieldDescription
YEARyear
QUARTERquarter
MONTHmonth
WEEKweek
DAYday
DAY_OF_MONTHday
DAY_OF_WEEKday_of_week
DOWday_of_week
DAY_OF_YEARday_of_year
DOYday_of_year
YEAR_OF_WEEKyear_of_week
YOWyear_of_week
HOURhour
MINUTEminute
SECONDsecond
TIMEZONE_HOURtimezone_hour
TIMEZONE_MINUTEtimezone_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_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.

[1]: This specifier does not support 0 as a month or day.

[2]: This specifier does not support 0 as a month or day.

[3]: This specifier does not support 0 as a month or day.

[4]: Timestamp is truncated to milliseconds.

[5]: This specifier does not support 0 as a month or day.

[6]: This specifier is not supported yet. Consider using day_of_week (it uses 1-7 instead of 0-6).

[7]: When parsing, two-digit year format assumes range 1970 .. 2069, so \"70\" will result in year 1970 but \"69\" will produce 2069.