Date and Time Operators

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 'Asia/Shanghai'; -- 2012-10-30 09:00:00.000 Asia/Shanghai

• current_time -> time with time zone

  Returns the current time (UTC time zone).

  select current_time;-- 11:49:07.427 Asia/Shangha

• current_timestamp -> timestamp with time zone

  Returns the current timestamp (UTC time zone).

  select current_timestamp; -- 2020-07-25 11:50:27.350 Asia/Shanghai

• current_timezone() → varchar

  Returns the current time zone.

  select current_timezone();-- Asia/Shanghai 

• date(x) → date

  Converts a date literal to a variable of the date type.

  select date('2020-07-25');-- 2020-07-25

• from_iso8601_timestamp(string) → timestamp with time zone

  Converts a timestamp literal in ISO 8601 format into a timestamp variable with a time zone.

  SELECT from_iso8601_timestamp('2020-05-11');-- 2020-05-11 00:00:00.000 Asia/Shanghai
  SELECT from_iso8601_timestamp('2020-05-11T11:15:05'); -- 2020-05-11 11:15:05.000 Asia/Shanghai 
  SELECT from_iso8601_timestamp('2020-05-11T11:15:05.055+01:00');-- 2020-05-11 11:15:05.055 +01:00

• from_iso8601_date(string) → date

  Converts a date literal in ISO 8601 format into a variable of the date type.

  SELECT from_iso8601_date('2020-05-11');-- 2020-05-11
  SELECT from_iso8601_date('2020-W10');-- 2020-03-02
  SELECT from_iso8601_date('2020-123');-- 2020-05-02

• from_unixtime(unixtime) → timestamp

  Converts a UNIX timestamp to a timestamp.

  Select FROM_UNIXTIME(1.595658735E9); -- 2020-07-25 14:32:15.000
  Select FROM_UNIXTIME(875996580); --1997-10-04 20:23:00.000

• from_unixtime(unixtime, string) → timestamp with time zone

  Converts a UNIX timestamp into a timestamp variable. The time zone option can be contained.

  select from_unixtime(1.595658735E9, 'Asia/Shanghai');-- 2020-07-25 14:32:15.000 Asia/Shanghai 

• from_unixtime(unixtime, hours, minutes) → timestamp with time zone

  Converts a UNIX timestamp to a timestamp variable with a time zone. hours and minutes indicate the time zone offsets.

  select from_unixtime(1.595658735E9, 8, 30);-- 2020-07-25 14:32:15.000 +08:30 \

• localtime -> time

  Obtains the current time

  select localtime;-- 14:16:13.096 

• localtimestamp -> timestamp

  Obtains the current timestamps.

  select localtimestamp;-- 2020-07-25 14:17:00.567 

• months_between(date1, date2)à double

  Return the number of months between date1 and date2. If date1 is later than date2, the result is a positive number. Otherwise, the result is a negative number. If the days of the two dates are the same, the result is an integer. Otherwise, the decimal part is calculated based on the difference between the hour, minute, and second (31 days of each month). The type of date1 and date2 can be date, timestamp, or a string in the yyyy-MM-dd or yyyy-MM-dd HH:mm:ss format.

  select months_between('2020-02-28 10:30:00', '2021-10-30');-- -20.05040323
  select months_between('2021-01-30', '2020-10-30'); -- 3.0

• now() → timestamp with time zone

  Obtains the current time, which is the alias of current_time.

  select now();-- 2020-07-25 14:39:39.842 Asia/Shanghai

• unix_timestamp()

  Obtains the current unix timestamp.

  select unix_timestamp(); -- 1600930503

• to_iso8601(x) → varchar

  Converts x into a character string in the ISO 8601 format. x can be DATE or TIMESTAMP [with time zone].

  select to_iso8601(date '2020-07-25'); -- 2020-07-25
  select to_iso8601(timestamp '2020-07-25 15:22:15.214'); -- 2020-07-25T15:22:15.214+08:00

• to_milliseconds(interval) → bigint

  Obtains the number of milliseconds since 00:00 on the current day.

  select to_milliseconds(interval '8' day to second);-- 691200000

• to_unixtime(timestamp) → double

  Converts the timestamp to the UNIX time.

  select to_unixtime(cast('2020-07-25 14:32:15.147' as timestamp));-- 1.595658735147E9

• trunc(string date, string format) →string

  Truncates a date value based on the format. The supported format is MONTH/MON/MM or YEAR/YYYY/YY.

  select trunc(date '2020-07-08','yy');-- 2020-01-01
  select trunc(date '2020-07-08','MM');-- 2020-07-01

  

  Do not use parentheses when using the following SQL standard functions:

  current_data

  current_time

  current_timestamp

  localtime

  Localtimestamp

In the preceding example, the timestamp 2001-08-22 03:04:05.321 is used as the input.

date_trunc(unit, x) → [same as input]

Returns the value of x after the unit.

select date_trunc('hour', timestamp '2001-08-22 03:04:05.321'); -- 2001-08-22 03:00:00.000

• date_add(unit, value, timestamp) → [same as input]

  Add value units to timestamp. If you want to perform the subtraction operation, you can assign a negative value to the 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 the value of timestamp2 minus timestamp1. The unit of the value is unit.

  The value of unit is a character string. For example, day, week, and year.

  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
  SELECT date_diff('second', TIMESTAMP '2020-06-01 12:30:45.000', TIMESTAMP '2020-06-02 12:30:45.123');-- 86400
  SELECT date_diff('millisecond', TIMESTAMP '2020-06-01 12:30:45.000', TIMESTAMP '2020-06-02 12:30:45.123');-- 86400123

• adddate(date, bigint)→ [same as input]
  Description: Date addition. The input type can be date or timestamp, indicating that the date is added or deducted. If the input type is subtraction, the value of bigint is negative.

  select ADDDATE(timestamp '2020-07-04 15:22:15.124',-5);-- 2020-06-29 15:22:15.124 
  select ADDDATE(date '2020-07-24',5); -- 2020-07-29

parse_duration(string) → interval

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

• date_format(timestamp, format) → varchar

  Uses format to format timestamp.

  select date_format(timestamp '2020-07-22 15:00:15', '%Y/%m/%d');-- 2020/07/22

• date_parse(string, format) → timestamp

  Parses the date literals using format.

  select date_parse('2020/07/20', '%Y/%m/%d');-- 2020-07-20 00:00:00.000 

The following table is based on the MySQL manual and describes various format descriptors.

Example:

select date_format(timestamp '2020-07-25 15:04:00.124','%j day of a year with English suffix (0th, 1st, 2nd, 3rd...),%m month %d day,%p %T %W');
                       _col0                       
---------------------------------------------------
 207th day of the year, 25th day of July, PM 15:04:00 Saturday
(1 row)

These format descriptors are not currently supported: %D %U %u %V %X.

• date_format(timestamp, format) → varchar

  Uses format to format timestamp.

• date_parse(string, format) → timestamp

  Parses the timestamp character string.

  select date_parse('2020/07/20', '%Y/%m/%d');-- 2020-07-20 00:00:00.000 

select second(timestamp '2020-02-12 15:32:33.215');-- 33
select timezone_hour(timestamp '2020-02-12 15:32:33.215');-- 8

• extract(field FROM x) → bigint

  Description: Returns the field from x. For details about the corresponding field, see the table in this document.

  select extract(YOW FROM timestamp '2020-02-12 15:32:33.215');-- 2020
  select extract(SECOND FROM timestamp '2020-02-12 15:32:33.215');-- 33
  select extract(DOY FROM timestamp '2020-02-12 15:32:33.215');--43

• last_day(timestamp) -> date

  Description: Returns the last day of each month based on the specified timestamp.

  SELECT last_day(timestamp '2019-09-09 12:12:12.000');--  2019-09-30
  SELECT last_day(date '2019-07-09');--2019-07-31

• add_months(timestamp) -> [same as input]

  Description: Returns the correct date by adding the specified date to the specified month.

  SELECT add_months(timestamp'2019-09-09 00:00:00.000', 11);-- 2020-08-09 00:00:00.000

• next_day() (timestamp, string) -> date

  Description: Returns the next day of the specified weekday based on the specified date.

  SELECT next_day(timestamp'2019-09-09 00:00:00.000', 'monday');-- 2019-09-16 00:00:00.000
  SELECT next_day(date'2019-09-09', 'monday');-- 2019-09-16

• numtoday(integer) -> BIGINT

  Description: Converts transferred integer values to values of the day type, for example, BIGINT.

  SELECT numtoday(2);-- 2