Time and Date Functions

age(timestamp, timestamp)

Description: Subtracts two parameters and returns the year, month, and day. If the subtraction result is negative, the function also returns a negative value.

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 current time from the parameter value.

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 (when the second parameter is an integer) is days.

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)

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

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

Example:

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)

subdate(date, interval | int)

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

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

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)

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

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

Description: Returns the result of a given date/time minus a specified interval. This function is only supported by clusters of version 8.2.0 or later.

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

Example:

1
2
3
4
5

select subtime('2020-11-13 01:01:01', '23:59:59');
       addtime
---------------------
 2020-11-12 01:01:02
(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, timestamp)

Description: Adds an integer time interval (seconds can include decimals) in the unit of field to a datetime expression. If the value is negative, the corresponding interval is subtracted from the given datetime expression. The field can be year, month, quarter, day, week, hour, minute, second, and microsecond.

Return type: timestamp.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

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

select timestampadd(second, 2.354156, timestamp '2020-11-13');
        timestampadd
----------------------------
 2020-11-13 00:00:02.354156
(1 row)

timestampdiff(field, timestamp1, timestamp2)

Description: Subtracts timestamp2 from timestamp1 from two date parameters and returns a value in the unit of field. If the result is negative, the function returns a negative value. The field can be year, month, quarter, day, week, hour, minute, second, and 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 two date parameters. If the result is negative, the function returns a negative value. Both input parameters must be of the same type. This function is only supported by clusters of version 8.2.0 or later.

Return type: time.

Example:

1
2
3
4
5

SELECT timediff('2022-7-5 1:1:1', '2021-7-5 1:1:1');
   timediff 
---------------
  8760:00:00
(1 row)

clock_timestamp()

Description: Current timestamp of the real-time clock.

Return type: timestamp with time zone.

Example:

1
2
3
4
5

SELECT clock_timestamp();
        clock_timestamp        
-------------------------------
 2017-09-01 16:57:36.636205+08
(1 row)

current_date

Description: Current date.

Return type: date.

Example:

1
2
3
4
5

SELECT current_date;
    current_date    
------------
 2017-09-01
(1 row)

curdate()

Description: Current date. This function is compatible with MySQL. This function is only supported by clusters of version 8.2.0 or later.

Return type: date.

Example:

1
2
3
4
5

SELECT curdate();
   curdate    
------------
 2022-09-19
(1 row)

current_time

Description: Current time.

Return type: time with time zone.

Example:

1
2
3
4
5

SELECT current_time;
       current_time       
--------------------
 16:48:27.59887
(1 row)

curtime([fsp])

Description: Current time. fsp is an optional parameter of integer type, specifying the fractional second precision of the return value. This function is only supported by clusters of version 8.2.0 or later.

Return type: time with time zone.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT curtime();
       curtime       
--------------------
 16:46:40.759849
(1 row)
SELECT curtime(2);
       curtime       
--------------------
 16:47:13.9
(1 row)

current_timestamp

Description: Current date and time.

Return type: timestamp with time zone.

Example:

1
2
3
4
5

SELECT current_timestamp;
       current_timestamp        
------------------------------
 2025-01-22 16:45:41.935586
(1 row)

convert_tz(timestamp, from_tz, to_tz)

Description: Converts the timestamp value from the time zone specified by from_tz (text) to the time zone specified by to_tz (text) and returns the converted time. This function is only supported by clusters of version 8.2.0 or later.

Return type: timestamp without time zone.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT convert_tz('2018-12-25 13:25:00', '+02:00', '+08:00');
    convert_tz       
--------------------
 2018-12-25 19:25:00
(1 row)
SELECT convert_tz('2000-02-28 23:00:00', 'GMT', 'MET');
    convert_tz       
--------------------
 2000-02-29 00:00:00
(1 row)

datediff(date1, date2)

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

Return type: integer.

Example:

1
2
3
4
5

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

date_part(text, timestamp)

Description: Retrieves the precision specified by text.

This function is equivalent to extract(field from timestamp).

Return type: double precision.

Example:

1
2
3
4
5

SELECT date_part('hour', timestamp '2001-02-16 20:38:40');
 date_part 
-----------
        20
(1 row)

date_part(text, interval)

Description: Retrieves the precision specified by text. If the value exceeds 12, it takes the modulus with 12.

This function is equivalent to extract(field from timestamp).

Return type: double precision.

Example:

1
2
3
4
5

SELECT date_part('month', interval '2 years 3 months');
 date_part 
-----------
         3
(1 row)

date_trunc(text, timestamp)

Description: Truncates to the precision specified by text.

Return type: timestamp.

Example:

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

SELECT date_trunc('hour', timestamp  '2001-02-16 20:38:40');
     date_trunc      
---------------------
 2001-02-16 20:00:00
(1 row)

-- Get the last day of last year.
SELECT date_trunc('day', date_trunc('year',CURRENT_DATE)+ '-1');
       date_trunc
------------------------
 2022-12-31 00:00:00+08
(1 row)

-- Get the first day of this year.
SELECT date_trunc('year',CURRENT_DATE);
       date_trunc
------------------------
 2023-01-01 00:00:00+08
(1 row)

-- Get the first day of last year.
SELECT date_trunc('year',now() + '-1 year');
       date_trunc
------------------------
 2022-01-01 00:00:00+08
(1 row)

trunc(timestamp)

Description: Truncates by day by default.

Return type: timestamp.

Example:

1
2
3
4

SELECT trunc(timestamp  '2001-02-16 20:38:40');                                                                                                                                                                   trunc
---------------------
2001-02-16 00:00:00
(1 row)

extract(field from timestamp)

Description: Retrieves the value of the specified precision for the field. Valid field values can be found in EXTRACT.

Return type: double precision.

Example:

1
2
3
4
5

SELECT extract(hour from timestamp '2001-02-16 20:38:40');
 date_part 
-----------
        20
(1 row)

extract(field from interval)

Description: Retrieves the value of the specified precision for the field. If the value exceeds 12, it takes the modulus with 12. Valid field values can be found in EXTRACT.

Return type: double precision.

Example:

1
2
3
4
5

SELECT extract(month from interval '2 years 3 months');
 date_part 
-----------
         3
(1 row)

day(date)

Description: Retrieves the number of days in the month for the given date, which is identical to the dayofmonth function.

Range: 1 to 31.

Return type: integer.

Example:

1
2
3
4
5

select day('2020-06-28');
 day
-----
  28
(1 row)

dayofmonth(date)

Description: Retrieves the number of days in the month for the given date.

Range: 1 to 31.

Return type: integer.

Example:

1
2
3
4
5

select dayofmonth('2020-06-28');
 dayofmonth
------------
         28
(1 row)

dayofweek(date)

Description: Returns the weekday index for the given date, with Sunday being the first day of the week.

Range: 1 to 7.

Return type: integer.

Example:

1
2
3
4
5

select dayofweek('2020-11-22');
 dayofweek
-----------
         1
(1 row)

dayofyear(date)

Description: Returns the number of days into the year for the given date.

Range: 1 to 366.

Return type: integer.

Example:

1
2
3
4
5

select dayofyear('2020-02-29');
 dayofyear
-----------
        60
(1 row)

hour(timestamp with time zone)

Description: Retrieves the hour value from the time.

Return type: integer.

Example:

1
2
3
4
5

SELECT hour(timestamptz '2018-12-13 12:11:15+06');
 hour
------
   14
(1 row)

isfinite(date)

Description: Checks if the date is finite.

Return type: boolean.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT isfinite(date '2001-02-16');
 isfinite 
----------
 t
(1 row)
SELECT isfinite(date 'infinity');
 isfinite
----------
 f
(1 row)

isfinite(timestamp)

Description: Determines if the time is finite.

Return type: boolean.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT isfinite(timestamp '2001-02-16 21:28:30');
 isfinite 
----------
 t
(1 row)
SELECT isfinite(timestamp 'infinity');
 isfinite
----------
 f
(1 row)

isfinite(interval)

Description: Verifies if the interval is finite.

Return type: boolean.

Example:

1
2
3
4
5

SELECT isfinite(interval '4 hours');
 isfinite 
----------
 t
(1 row)

justify_days(interval)

Description: Converts the time interval into months, assuming each month has 30 days.

Return type: interval.

Example:

1
2
3
4
5

SELECT justify_days(interval '35 days');
 justify_days 
--------------
 1 mon 5 days
(1 row)

justify_hours(interval)

Description: Converts the time interval into days, assuming each day has 24 hours.

Return type: interval.

Example:

1
2
3
4
5

SELECT JUSTIFY_HOURS(INTERVAL '27 HOURS');
 justify_hours  
----------------
 1 day 03:00:00
(1 row)

justify_interval(interval)

Description: Adjusts interval using both justify_days and justify_hours.

Return type: interval.

Example:

1
2
3
4
5

SELECT JUSTIFY_INTERVAL(INTERVAL '1 MON -1 HOUR');
 justify_interval 
------------------
 29 days 23:00:00
(1 row)

localtime

Description: Current local time.

Return type: time.

Example:

1
2
3
4
5

SELECT localtime AS RESULT;
     result
----------------
 2025-01-17 09:48:08.056091
(1 row)

localtimestamp

Description: Current local date and time.

Return type: timestamp.

Example:

1
2
3
4
5

SELECT localtimestamp;
         localtimestamp          
----------------------------
 2017-09-01 17:03:30.781902
(1 row)

makedate(year, dayofyear)

Description: Constructs a date value based on the provided year and day-of-year.

Return type: date.

Example:

1
2
3
4
5

select makedate(2020, 60);
  makedate
------------
 2020-02-29
(1 row)

maketime(hour, minute, second)

Description: Creates a time value from the given hour, minute, and second. Since the time type in DataArts Fabric SQL ranges from 00:00:00 to 24:00:00, scenarios where the hour is greater than 24 or less than 0 are unsupported.

Return type: time.

Example:

1
2
3
4
5

select maketime(12, 15, 30.12);
  maketime
-------------
 12:15:30.12
(1 row)

microsecond(timestamp with time zone)

Description: Retrieves the microsecond value from the time.

Return type: integer.

Example:

1
2
3
4
5

SELECT microsecond(timestamptz '2018-12-13 12:11:15.123634+06');
 microsecond
-------------
      123634
(1 row)

minute(timestamp with time zone)

Description: Obtains the minute value in the time.

Return type: integer.

Example:

1
2
3
4
5

SELECT minute(timestamptz '2018-12-13 12:11:15+06');
 minute
--------
     11
(1 row)

month(date)

Description: Returns the month of a given datetime.

Return type: integer.

Example:

1
2
3
4
5

select month('2020-11-30');
 month
-------
    11
(1 row)

now([fsp])

Description: Start date and time of the current transaction. The parameter determines the microsecond output precision. The default value is 6.

Return type: timestamp with time zone.

Example:

1
2
3
4
5

SELECT now();
              now              
-------------------------------
 2017-09-01 17:03:42.549426+08
(1 row)

1
2
3
4
5

SELECT now(3);
             now    
---------------------------- 
 2021-09-08 10:59:00.427+08
(1 row)        

numtodsinterval(num, interval_unit)

Description: Converts a number to the interval type. The value of num is a number of the numeric type, and the value of interval_unit is a string in the fixed format ('DAY' | 'HOUR' | 'MINUTE' | 'SECOND').

You can set IntervalStyle to oracle to be compatible with the interval output format of the function in the Oracle database.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

SELECT numtodsinterval(100, 'HOUR');
 numtodsinterval 
-----------------
 100:00:00
(1 row)

SET intervalstyle = oracle;
SET
SELECT numtodsinterval(100, 'HOUR');
        numtodsinterval
-------------------------------
 +000000004 04:00:00.000000000
(1 row)

pg_sleep(seconds)

Description: Specifies the delay time of the server thread, in seconds.

Return type: void.

Example:

1
2
3
4
5

SELECT pg_sleep(10);
 pg_sleep 
----------

(1 row)

period_add(P, N)

Description: Returns the date of the 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)

quarter(date)

Description: Obtains the quarter the date belongs to.

Return type: integer.

Example:

1
2
3
4
5

SELECT quarter(date '2018-12-13');
 quarter
---------
       4
(1 row)

second(timestamp with time zone)

Description: Obtains the second value of the time.

Return type: integer.

Example:

1
2
3
4
5

SELECT second(timestamptz '2018-12-13 12:11:15+06');
 second
--------
     15
(1 row)

statement_timestamp()

Description: Current date and time.

Return type: timestamp with time zone.

Example:

1
2
3
4
5

SELECT statement_timestamp();
      statement_timestamp      
-------------------------------
 2017-09-01 17:04:39.119267+08
(1 row)

sysdate

Description: Current date and time.

Return type: timestamp.

Example:

1
2
3
4
5

SELECT sysdate;
       sysdate       
---------------------
 2017-09-01 17:04:49
(1 row)

timeofday()

Description: Current date and time (like clock_timestamp, but text is returned).

Return type: text.

Example:

1
2
3
4
5

SELECT timeofday();
              timeofday              
-------------------------------------
 Fri Sep 01 17:05:01.167506 2017 CST
(1 row)

transaction_timestamp()

Description: Current date and time, which is equivalent to current_timestamp.

Return type: timestamp with time zone.

Example:

1
2
3
4
5

SELECT transaction_timestamp();
     transaction_timestamp     
-------------------------------
 2017-09-01 17:05:13.534454+08
(1 row)

from_unixtime(unix_timestamp[,format])

Description: Converts the Unix timestamp to the date and time type when the format string is set to the default value. If the format string is specified, convert the Unix timestamp to a string of the specified format for output.

Return type: timestamp (default format string) or text (specified by the format string).

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT from_unixtime(875996580);
    from_unixtime
---------------------
 1997-10-04 20:23:00
(1 row)
SELECT from_unixtime(875996580, '%Y %D %M %h:%i:%s');
       from_unixtime
---------------------------
 1997 5th October 04:23:00
(1 row)

unix_timestamp([timestamp with time zone])

Description: Obtains the number of seconds from 1970-01-01 00:00:00 UTC to the input parameter time. If there is no input parameter, the current time is used.

Return type: bigint (no input parameter) or numeric (with input parameters).

Example:

1
2
3
4
5

SELECT unix_timestamp();
 unix_timestamp
----------------
     1693906219
(1 row)

1
2
3
4
5

SELECT unix_timestamp('2018-09-08 12:11:13+06');
 unix_timestamp
----------------
     1536387073.000000
(1 row)

add_months(d,n)

Description: Calculates the time point d plus 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 
(1 row)

last_day(d)

Description: Calculates the time of the last day of the month at time point d.

• In ORA and TD compatibility mode, the return value type is timestamp.
• In MySQL-compatible mode, the return value type is date.

Example:

1
2
3
4
5

select last_day(to_date('2017-01-01', 'YYYY-MM-DD')) AS cal_result;
     cal_result      
---------------------
 2017-01-31 00:00:00
(1 row)

next_day(x,y)

Description: Calculates the time of the next week y starting from time x.

• In ORA and TD compatibility mode, the return value type is timestamp.
• In MySQL-compatible mode, the return value type is date.

Example:

1
2
3
4
5

select next_day(timestamp '2017-05-25 00:00:00','Sunday')AS cal_result;
     cal_result      
---------------------
 2017-05-28 00:00:00
(1 row)

from_days(days)

Description: Returns the corresponding date value based on the given number of days.

Return type: date.

Example:

1
2
3
4
5

select from_days(730669);
 from_days
------------
 2000-07-03
(1 row)

to_days(timestamp)

Description: Returns the number of days from year 0 to the input parameter date.

Return type: integer.

Example:

1
2
3
4
5

SELECT to_days(timestamp '2008-10-07');
 to_days
---------
  733687
(1 row)