7.13. Date and Time Functions and Operators

Time Zone Conversion

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 zoneusing string for the time zone.

• from_unixtime(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.

• This is an alias for current_timestamp.

• to_iso8601(x) → varchar
• Formats x as an ISO 8601 string. x can be date, timestamp, ortimestamp 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

The date_trunc function supports the following units:

• date_trunc(unit, x) → [same as input]
• Returns x truncated to unit.

Interval Functions

The functions in this section support the following interval units:

• 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 expressed in terms of unit.

Duration Function

The parse_duration function supports the following units:

• parse_duration(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

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:

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 withJodaTime’s 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:

• extract(field FROM x) → bigint
• Returns field from x. Note This SQL-standard function uses special syntax for specifying the arguments.

• day(x) → bigint

• day_of_month(x) → bigint
• This is an alias for .

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

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

• year(x) → bigint
• Returns the year from x.

• year_of_week(x) → bigint
• Returns the year of the ISO week from x.

• This is an alias for .