Time and Date Calculation Functions

age(timestamp, timestamp)

Description: Subtracts arguments and returns the difference. If the result is negative, the returned result is also negative.

Return type: interval

Example:

1
2
3
4
5

SELECT age(timestamp '2001-04-10', timestamp '1957-06-13');
           age           
-------------------------
 43 years 9 mons 27 days
(1 row)

age(timestamp)

Description: Subtracts the argument from the current date.

Return type: interval

Example:

1
2
3
4
5

SELECT age(timestamp '1957-06-13');
           age           
-------------------------
 68 years 7 mons 7 days
(1 row)

adddate(date, interval | int)

Description: Returns the result of a given datetime plus the time interval of a specified unit. The default unit is day (when the second parameter is an integer).

Return type: timestamp

Examples:

Set the input parameter to the text type.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

SELECT adddate('2020-11-13', 10);
  adddate
------------
 2020-11-23
(1 row)

SELECT adddate('2020-11-13', interval '1' month);
  adddate
------------
 2020-12-13
(1 row)

SELECT adddate('2020-11-13 12:15:16', interval '1' month);
       adddate
---------------------
 2020-12-13 12:15:16
(1 row)


SELECT adddate('2020-11-13', interval '1' minute);
       adddate
---------------------
 2020-11-13 00:01:00
(1 row)

Set the input parameter to the date type.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT adddate(current_date, 10);
  adddate
------------
 2021-09-24
(1 row)

SELECT adddate(date '2020-11-13', interval '1' month);
       adddate
---------------------
 2020-12-13 00:00:00
(1 row)

subdate(date, interval | int)

Description: Returns the result of a given datetime minus the time interval of a specified unit. The default unit is day (when the second parameter is an integer).

Return type: timestamp

Examples:

Set the input parameter to the text type.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

SELECT subdate('2020-11-13', 10);
  subdate
------------
 2020-11-03
(1 row)

SELECT subdate('2020-11-13', interval '2' month);
  subdate
------------
 2020-09-13
(1 row)

SELECT subdate('2020-11-13 12:15:16', interval '1' month);
       subdate
---------------------
 2020-10-13 12:15:16
(1 row)

SELECT subdate('2020-11-13', interval '2' minute);
       subdate
---------------------
 2020-11-12 23:58:00
(1 row)

Set the input parameter to the date type.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT subdate(current_date, 10);
  subdate
------------
 2021-09-05
(1 row)

SELECT subdate(current_date, interval '1' month);
       subdate
---------------------
 2021-08-15 00:00:00
(1 row)

addtime(timestamp | time | text, interval | text | numeric)

Description: Returns the result of a given datetime plus the time interval of a specified unit. This parameter is supported only by clusters of version 8.2.0 or later.

• If the first parameter is a timestamp, the function returns a timestamp.
• If the first parameter is a time or text, the function returns text or time (depending on compatibility configuration and actual situations, usually in the string format) and can display a time longer than 24 hours (similar to MySQL behavior).

• The second parameter can be of the numeric type (usually indicating the number of seconds).
• The second parameter can be of the text type (parsed as a time format or number).
• The system will truncate invalid characters and interprets a leading negative sign (-) in the input text.

Return type: same as the type of the first input parameter.

Examples:

Add a specified interval (of the interval type) to a given time (of the timestamp type).

1
2
3
4
5

SELECT ADDTIME('2020-11-13 01:01:01', '23:59:59');
addtime
---------------------
2020-11-14 01:01:00
(1 row)

Add a specified interval (of the text type) to a given time (of the text type). The returned result can be longer than 24 hours.

1
2
3
4
5

SELECT ADDTIME('20:1:1', '20:59:59+8');
addtime
----------
41:01:00
(1 row)

Add a specified interval (of the numeric type, usually indicating seconds) to a given time (of the time type).

1
2
3
4
5

SELECT addtime('10:00:00', 1.999999);
addtime
-----------------
10:00:01.999999
(1 row)

Add the text with invalid characters to a given time (of the text type). The invalid characters will be truncated.

1
2
3
4
5

SELECT addtime('12:30:45', '00:15:30xyz');
addtime
----------
12:46:15
(1 row)

subtime(timestamp | time | text, interval | text | numeric)

Description: Returns the result of a given datetime minus the time interval of a specified unit. This parameter is supported only by clusters of version 8.2.0 or later.

Return type: same as the type of the first input parameter.

Examples:

Subtract interval from timestamp.

1
2
3
4
5

SELECT subtime('2020-11-13 01:01:01', '23:59:59');
subtime
---------------------
2020-11-12 01:01:02
(1 row)

Subtract a value (unit: seconds) from time.

1
2
3
4
5

SELECT subtime('10:00:00', 1.999999);
subtime
-----------------
09:59:58.000001
(1 row)

Subtract the negative text input.

1
2
3
4
5

SELECT subtime('10:00:00', '-59.9999999');
subtime
----------
10:01:00
(1 row)

Subtract the text with minus signs (-) and invalid characters.

1
2
3
4
5

SELECT subtime('12:30:45', '-00:10:15abc');
subtime
----------
12:41:00
(1 row)

date_add(date, interval)

Description: Returns the result of a given datetime plus the time interval of a specified unit. It is equivalent to adddate(date, interval | int).

Return type: timestamp

date_sub(date, interval)

Description: Returns the result of a given datetime minus the time interval of a specified unit. It is equivalent to subdate(date, interval | int).

Return type: timestamp

timestampadd(field, numeric | text, timestamp)

Description: Adds an integer interval in the unit of field (the number of seconds can be a decimal) to a datetime expression. If the value is negative, the corresponding time interval is subtracted from the given datetime expression.

The field can be year, month, quarter, day, week, hour, minute, second, or microsecond.

Parameters:

• When the second parameter is a numeric value, decimal operations are supported, for example, 1.5 years.
• When the second parameter is a text:
  • If field is second, the decimal part is retained for calculation.
  • If field is other units (such as day and year), the decimal part is truncated (rounded off), and only the integer part is used for calculation. This is to solve the problem of inconsistent behavior with MySQL.

When you pass a time expression in the yyyy-mon-day+{value} format to the function, the {value} part represents the time zone for the given date. For example, 2023-01-31+1 means January 31, 2023, in the UTC+1 time zone. In DWS, this format is handled differently in MySQL. MySQL processes this format only if the loose parsing rule is enabled, interpreting it as 01:00 on January 31, 2023, in the system's default time zone.

Return type: timestamp

Examples:

Set the numeric to an integer.

1
2
3
4
5

SELECT timestampadd(year, 1, timestamp '2020-2-29');
    timestampadd
---------------------
 2021-02-28 00:00:00
(1 row)

Set the numeric to a decimal (Month 2.8, approximately equal to 2 months + 0.8 months).

1
2
3
4
5

SELECT timestampadd(month, 2.8, '2020-01-31'::timestamp);
    timestampadd     
---------------------
 2020-04-30 00:00:00
(1 row)

Set field to day (Day 30.9 is truncated to 30).

1
2
3
4
5

SELECT timestampadd(day, '30.9', '2016-02-29');
    timestampadd     
---------------------
 2016-03-30 00:00:00
(1 row)

Set field to second (decimals are retained).

1
2
3
4
5

SELECT timestampadd(second, '-4.999999', '2016-03-28 10:00:00');
        timestampadd        
----------------------------
 2016-03-28 09:59:55.000001
(1 row)

timestampdiff(field, timestamp1, timestamp2)

Description: Subtracts timestamp1 from timestamp2 and returns the difference in the unit of field. If the difference is negative, this function returns it normally. The field can be year, month, quarter, day, week, hour, minute, second, or microsecond.

Return type: bigint

Example:

1
2
3
4
5

SELECT timestampdiff(day, timestamp '2001-02-01', timestamp '2003-05-01 12:05:55');
 timestampdiff 
---------------
      819
(1 row)

timediff(timestamp | time | text, timestamp | time | text)

Description: Subtracts a date from another date. If the difference is negative, this function returns it normally. This parameter is supported only by clusters of version 8.2.0 or later.

• The types of the two input parameters must be the same or can be implicitly converted to the same type. Both input parameters support the text format.
• If the first parameter is less than the second parameter, the return value is a negative number.
• This function can be used to calculate the difference of more than 24 hours.

Return type: time

Examples:

Set the data type to timestamp.

1
2
3
4
5

SELECT TIMEDIFF('2022-7-5 1:1:1'::timestamp, '2022-7-5 2:2:2');
 timediff  
-----------
 -01:01:01
(1 row)

Set the data type to time. (The result is negative.)

1
2
3
4
5

SELECT TIMEDIFF('1:1:1'::TIME, '23:22:59');
 timediff  
-----------
 -22:21:58
(1 row)

Set the data type to text.

1
2
3
4
5

SELECT TIMEDIFF('1:1:1', '23:22:59');
 timediff  
-----------
 -22:21:58
(1 row)

datediff(date1, date2)

Description: Returns the number of days between two given dates.

Return type: bigint

Example:

1
2
3
4
5

SELECT datediff(date '2020-11-13', date '2012-10-16');
 datediff
----------
     2950
(1 row)

period_add(P, N)

Description: Returns the date of a given period plus N months.

Return type: integer

Example:

1
2
3
4
5

SELECT period_add(200801, 2);
 period_add
------------
     200803
(1 row)

period_diff(P1, P2)

Description: Returns the number of months between two given dates.

Return type: integer

1
2
3
4
5

SELECT period_diff(200802, 200703);
 period_diff
-------------
          11
(1 row)

add_months(d,n)

Description: Calculates the date value after date is added by N months.

Return type: timestamp

Example:

1
2
3
4
5

SELECT add_months(to_date('2017-5-29', 'yyyy-mm-dd'), 11) FROM dual;
     add_months      
---------------------
 2018-04-29 00:00:00
(1 row)