Date and Time 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           
-------------------------
 60 years 2 mons 18 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

Example:

When the input parameter is of 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)

When the input parameter is of 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)

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

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

• If the first parameter is timestamp, the function returns timestamp.
• If the first parameter is time or tex, 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.

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

Example:

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)