Operator Nội dung chính Example Result The Returns the current date as of the start of the query. Returns the current time as of the start of the query. Returns the current timestamp as of the start of the query. Returns the current time zone in the format defined by IANA [e.g.,
This is an alias for Returns the last day of the month. Parses the ISO 8601 formatted Parses the ISO 8601 formatted Returns the UNIX timestamp Returns
the UNIX timestamp Date and Time Operators#
+
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-7Time Zone Conversion#
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 -> dateAmerica/Los_Angeles] or as fixed offset from UTC [e.g., +08:35]CAST[x AS date].string into a timestamp with time zone.string into
a date.unixtime as a timestamp.unixtime as a timestamp with time zone using string for the time zone.
Returns the UNIX timestamp unixtime as a timestamp with time zone using hours and minutes for the time zone
offset.
Returns the current time as of the start of the query.
localtimestamp -> timestampReturns the current timestamp as of the start of the query.
now[] → timestamp with time zone#This is an alias for
current_timestamp.
Formats x as an ISO 8601 string. x can be date, timestamp, or timestamp with time zone.
Returns the
day-to-second interval as milliseconds.
Returns timestamp as a UNIX timestamp.
Note
The following SQL-standard functions do not use parenthesis:
current_datecurrent_timecurrent_timestamplocaltimelocaltimestamp
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.
Returns x truncated to unit.
Interval Functions#
The functions in this section support the following interval units:
| Milliseconds |
| Seconds |
| Minutes |
| Hours |
| Days |
| Weeks |
| Months |
| Quarters of a year |
| Years |
Adds an interval value of type unit to timestamp. Subtraction can be performed by using a negative value.
Returns timestamp2 - timestamp1 expressed in terms of unit.
Duration Function#
The parse_duration function supports the following units:
| Nanoseconds |
| Microseconds |
| Milliseconds |
| Seconds |
| Minutes |
| Hours |
| Days |
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:
| Abbreviated weekday name [ |
| Abbreviated month name [ |
| Month, numeric [ |
| Day of the month with English suffix [ |
| Day of the month, numeric [ |
| Day of the month, numeric [ |
| Fraction of second [6 digits for printing: |
| Hour [ |
| Hour [ |
| Hour [ |
| Minutes, numeric [ |
| Day of year [ |
| Hour [ |
| Hour [ |
| Month name [ |
| Month, numeric [ |
|
|
| Time, 12-hour [ |
| Seconds [ |
| Seconds [ |
| Time, 24-hour [ |
| Week [ |
| Week [ |
| Week [ |
| Week [ |
| Weekday name [ |
| Day of the week [ |
| Year for the week where Sunday is the first day of the week, numeric, four digits; used with |
| Year for the week, where Monday is the first day of the week, numeric, four digits; used with |
| Year, numeric, four digits |
| Year, numeric [two digits] 2 |
| A literal |
|
|
Timestamp is truncated to milliseconds.
2When parsing, two-digit year format assumes range 1970 .. 2069, so “70” will result in year 1970 but “69” will produce 2069.
This specifier is not supported yet. Consider using day_of_week[] [it uses 1-7 instead of 0-6].
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
Formats timestamp as a string using format.
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.
Parses string into a timestamp with time zone using format.