2.3. Date and Time Functions and Operators
Warning
Timestamps are currently represented as UNIX timestamps using the bigint type. This will change in a future release. Timestamps should be treated as opaque values and only used with the functions and operators in this chapter. Performing integer math on timestamp values is guaranteed to break in a future release.
Date and Time Operators
Operator | Example |
---|---|
+ | date '2012-08-08' + interval '1' hour |
+ | time '01:00' + interval '3' hour |
+ | timestamp '2012-08-08 01:00' + interval '23' hour |
- | date '2012-08-08' - interval '1' hour |
- | time '01:00' - interval '3' hour |
- | timestamp '2012-08-08 01:00' - interval '23' hour |
Date and Time Functions
- current_date -> date
Returns the current date as of the start of the query.
Note
This SQL-standard function does not use parenthesis.
Note
This function is not yet supported.
- current_time -> time
Returns the current time as of the start of the query.
Note
This SQL-standard function does not use parenthesis.
Note
This function is not yet supported.
- current_timestamp -> timestamp
Returns the current timestamp as of the start of the query.
Note
This SQL-standard function does not use parenthesis.
- from_unixtime(unixtime) → timestamp
Returns the UNIX timestamp unixtime as a timestamp.
- now() → timestamp
This is an alias for current_timestamp.
- to_unixtime(timestamp) → double
Returns timestamp as a UNIX timestamp.
Interval Functions
The functions in this section support the following interval units:
Unit | Description |
---|---|
second | Seconds |
minute | Minutes |
hour | Hours |
day | Days |
week | Weeks |
month | Months |
quarter | Quarters of a year |
year | Years |
century | Centuries |
- date_add(unit, value, timestamp) → timestamp
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.
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 (0 .. 12) |
%D | Day of the month with English suffix (0th, 1st, 2nd, 3rd, ...) |
%d | Day of the month, numeric (00 .. 31) |
%e | Day of the month, numeric (0 .. 31) |
%f | Microseconds (000000 .. 999999) |
%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 (00 .. 12) |
%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 |
%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) |
%% | A literal % character |
%x | x, for any x not listed above |
Warning
The following specifiers are not currently supported: %D %U %u %V %X %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 the Java SimpleDateFormat pattern format.
- format_datetime(timestamp, format) → timestamp
Formats timestamp as a string using format.
- parse_datetime(string, format) → timestamp
Parses string into a timestamp using format.
Extraction Function
The extract function supports the following fields:
Field | Description |
---|---|
CENTURY | century() |
YEAR | year() |
QUARTER | quarter() |
MONTH | month() |
WEEK | week() |
DAY | day() |
DAY_OF_MONTH | day() |
DAY_OF_WEEK | day_of_week() |
DOW | day_of_week() |
DAY_OF_YEAR | day_of_year() |
DOY | day_of_year() |
HOUR | hour() |
MINUTE | minute() |
SECOND | second() |
TIMEZONE_HOUR | Hour component of the time zone offset |
TIMEZONE_MINUTE | Minute component of the time zone offset |
- extract(field FROM timestamp) → bigint
Returns field from timestamp.
Note
This SQL-standard function uses special syntax for specifying the arguments.
Convenience Extraction Functions
- century(timestamp) → bigint
Returns the centry from timestamp.
- day(timestamp) → bigint
Returns the day of the month from timestamp.
- day_of_month(timestamp) → bigint
This is an alias for day().
- day_of_week(timestamp) → bigint
Returns the ISO day of the week from timestamp. The value ranges from 1 (Monday) to 7 (Sunday).
- day_of_year(timestamp) → bigint
Returns the day of the year from timestamp. The value ranges from 1 to 366.
- dow(timestamp) → bigint
This is an alias for day_of_week().
- doy(timestamp) → bigint
This is an alias for day_of_year().
- hour(timestamp) → bigint
Returns the hour of the day from timestamp. The value ranges from 0 to 23.
- minute(timestamp) → bigint
Returns the minute of the hour from timestamp.
- month(timestamp) → bigint
Returns the month of the year from timestamp.
- quarter(timestamp) → bigint
Returns the quarter of the year from timestamp. The value ranges from 1 to 4.
- second(timestamp) → bigint
Returns the second of the hour from timestamp.
- week(timestamp) → bigint
Returns the ISO week of the year from timestamp. The value ranges from 1 to 53.
- week_of_year(timestamp) → bigint
This is an alias for week().
- year(timestamp) → bigint
Returns the year from timestamp.