Use datetime functions in queries

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

Datetime functions frequently used in Oracle 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 tenant, you can use the TO_CHAR() function to adjust the datetime display format. For example:

  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 tenant, you can use the TO_CHAR() function to extract year, month, day, hour, minute, or second values from a datetime value. For example:

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