Use datetime functions in queries

Datetime data types that are frequently used in Oracle-compatible tenants include DATE, TIMESTAMP, and INTERVAL. For more information about the datetime data types, see SQL syntax (Oracle compatible mode).

Datetime functions frequently used in Oracle-compatible tenants to fetch the system time include SYSDATE() and SYSTIMESTAMP(). Their default formats are determined by the NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT parameters. Functions for the INTERVAL data type include NUMTODSINTERVAL() and NUMTOYMINTERVAL(), which convert between numbers and intervals.

• Example: Format the datetime display

  In an Oracle-compatible tenant, you can use the TO_CHAR() function to adjust the datetime display format. The SQL statement is as follows:

  obclient> select sysdate, to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') new_date from dual;
  +-----------+---------------------+
  | SYSDATE   | NEW_DATE            |
  +-----------+---------------------+
  | 01-APR-20 | 2020/04/01 22:17:07 |
  +-----------+---------------------+
  1 row in set
  

• Example: Extract the year, month, day, hour, minute, and second values from a datetime value

  In an Oracle-compatible tenant, you can use the TO_CHAR() function to extract year, month, day, hour, minute, or second values from a datetime value. The SQL statement is as follows:

  obclient> SELECT sysdate
       , to_char(sysdate,'yyyy') d_year
       , to_char(sysdate,'mm') d_month
       , to_char(sysdate,'dd') d_day
       , to_char(sysdate,'hh24') d_hour24
       , to_char(sysdate,'mi') d_min
       ,to_char(sysdate,'ss') d_sec
       FROM dual;
  +-----------+--------+---------+-------+----------+-------+-------+
  | SYSDATE   | D_YEAR | D_MONTH | D_DAY | D_HOUR24 | D_MIN | D_SEC |
  +-----------+--------+---------+-------+----------+-------+-------+
  | 01-JUN-22 | 2022   | 06      | 01    | 17       | 12    | 10    |
  +-----------+--------+---------+-------+----------+-------+-------+
  1 row in set
  

• Example: Perform addition and subtraction operations on datetime values

  In an Oracle-compatible tenant, you can directly add days to or subtract days from a datetime value. You can also perform addition and subtraction operations on datetime values by using the NUMTODSINTERVAL() or ADD_MONTHS() function.

  obclient> SELECT sysdate
  , sysdate+1                                 t0
  , sysdate + numtodsinterval(1,'hour')       t1
  , sysdate + numtodsinterval(1,'minute')     t2
  , sysdate + numtodsinterval(1,'second')     t3
  , add_months(sysdate, 1)                    t4
  , add_months(sysdate, -1)                   t5
  FROM dual\G
  *************************** 1. row ***************************
  SYSDATE: 01-JUN-22
  T0: 02-JUN-22
  T1: 01-JUN-22
  T2: 01-JUN-22
  T3: 01-JUN-22
  T4: 01-JUL-22
  T5: 01-MAY-22
  1 row in set
  

• Example: interval-related datetime functions

  obclient> select sysdate, systimestamp
       ,numtoyminterval(100,'YEAR')    interval_year
       ,numtoyminterval(100,'MONTH')   interval_mon
       ,numtodsinterval(100,'DAY')     interval_day
       ,numtodsinterval(100,'HOUR')    interval_hour
       ,numtodsinterval(100,'MINUTE')  interval_min
       ,numtodsinterval(100,'SECOND')  interval_sec
       from dual \G
  *************************** 1. row ***************************
       SYSDATE: 01-JUN-22
  SYSTIMESTAMP: 01-JUN-22 05.12.58.432471 PM +08:00
  INTERVAL_YEAR: +000000100-00
  INTERVAL_MON: +000000008-04
  INTERVAL_DAY: +000000100 00:00:00.000000000
  INTERVAL_HOUR: +000000004 04:00:00.000000000
  INTERVAL_MIN: +000000000 01:40:00.000000000
  INTERVAL_SEC: +000000000 00:01:40.000000000
  1 row in set