Updated on 2022-11-18 GMT+08:00

Date and Time Operators

Date and Time Operators

Operator

Example

Result

+

date '2012-08-08' + interval '2' day

2012-08-10

+

time '01:00' + interval '3' hour

04:00:00.000

+

timestamp '2012-08-08 01:00' + interval '29' hour

2012-08-09 06:00:00.000

+

timestamp '2012-10-31 01:00' + interval '1' month

2012-11-30 01:00:00.000

+

interval '2' day + interval '3' hour

2 03:00:00.000

+

interval '3' year + interval '5' month

3-5

-

date '2012-08-08' - interval '2' day

2012-08-06

-

time '01:00' - interval '3' hour

22:00:00.000

-

timestamp '2012-08-08 01:00' - interval '29' hour

2012-08-06 20:00:00.000

-

timestamp '2012-10-31 01:00' - interval '1' month

2012-09-30 01:00:00.000

-

interval '2' day - interval '3' hour

1 21:00:00.000

-

interval '3' year - interval '5' month

2-7

Time Zone Conversion

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

Date/Time Functions

  • current_date -> date

    Returns the current date (UTC time zone).

    select current_date; -- 2020-07-25 
  • 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

Truncation Function

Similar to the operation of reserving decimal places, the date_trunc function supports the following units:

Unit

Value After Truncation

second

2001-08-22 03:04:05.000

minute

2001-08-22 03:04:00.000

hour

2001-08-22 03:00:00.000

day

2001-08-22 00:00:00.000

week

2001-08-20 00:00:00.000

month

2001-08-01 00:00:00.000

quarter

2001-07-01 00:00:00.000

year

2001-01-01 00:00:00.000

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

Interval Functions

The functions in this chapter 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

  • 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

Duration Function

The duration can use the following units:

Unit

Description

ns

nanosecond

us

microsecond

ms

millisecond

s

second

m

minute

h

hour

d

day

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

MySQL Date Functions

The formatted strings that are compatible with the MySQL date_parse and str_to_date methods in this section.

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

Format Descriptor

Description

%a

Day in a week (Sun .. Sat)

%b

Month (Jan .. Dec)

%c

Month (1 .. 12)

%D

Day of the month (0th, 1st, 2nd, 3rd, ...)

%d

Day in the month (01.. 31) (Two digits. Zeros are added before the single digits.)

%e

Day in the month (1 .. 31)

%f

Seconds after the decimal point (6 digits for printing: 000000 .. 999000; 1 - 9 digits for parsing: 0 .. 999999999)

%H

Hour (00 .. 23)

%h

Hour (01.. 12)

%I

Hour (01.. 12)

%i

Minute, number (00 .. 59)

%j

Day of the year (001 .. 366)

%k

Hour (0 .. 23)

%l

Hour (1.. 12)

%M

Month name (January .. December)

%m

Month, number (01 .. 12)

%p

AM or PM

%r

Time in the 12-hour format (hh:mm:ss followed by AM or PM)

%S

Second (00 .. 59)

%s

Second (00 .. 59)

%T

Time in the 24-hour format (hh:mm:ss)

%U

Week (00 .. 53) Sunday is the first day of a week.

%u

Week (00 .. 53) Monday is the first day of a week.

%V

Week (01.. 53) Sunday is the first day of a week. Used together with %X.

%v

Week (01 .. 53) Monday is the first day of a week. Used together with %X.

%W

Day of the week (Sunday .. Saturday)

%w

Day of the week (0.. 6) Sunday is the first day of a week.

%X

Year, a four-digit number. The first day is Sunday.

%x

Year, a four-digit number. The first day is Monday.

%Y

Year, a four-digit number.

%y

Year. The value is a two-digit number ranging from 1970 to 2069.

%%

Indicates the character '%'.

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.

Java Date Functions

The formatting strings used in this section are compatible with the Java SimpleDateFormat style.

  • 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 

Common Extraction Functions

Domain

Description

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()

YEAR_OF_WEEK

year_of_week()

YOW

year_of_week()

HOUR

hour()

MINUTE

minute()

SECOND

second()

TIMEZONE_HOUR

timezone_hour()

TIMEZONE_MINUTE

timezone_minute()

Example:

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

    Description: Obtains the month name.

    SELECT monthname(timestamp '2019-09-09 12:12:12.000');-- SEPTEMBER
    SELECT monthname(date '2019-07-09');--JULY
  • 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

Function

Example

Description

SECONDS_ADD(TIMESTAMP date, INT seconds)

SELECT seconds_add(timestamp '2019-09-09 12:12:12.000', 10);

The time is added in seconds.

SECONDS_SUB(TIMESTAMP date, INT seconds)

SELECT seconds_sub(timestamp '2019-09-09 12:12:12.000', 10);

Subtracts time in seconds.

MINUTES_ADD(TIMESTAMP date, INT minutes)

SELECT MINUTES_ADD(timestamp '2019-09-09 12:12:12.000', 10);

Add the time in the unit of minute.

MINUTES_SUB(TIMESTAMP date, INT minutes)

SELECT MINUTES_SUB(timestamp '2019-09-09 12:12:12.000', 10);

The time is subtracted in the unit of minute.

HOURS_ADD(TIMESTAMP date, INT hours)

SELECT HOURS_ADD(timestamp '2019-09-09 12:12:12.000', 1);

Add the time in the unit of hour.

HOURS_SUB(TIMESTAMP date, INT hours)

SELECT HOURS_SUB(timestamp '2019-09-09 12:12:12.000', 1);

The time is subtracted in hours.

  • 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