Datetime data types that are commonly used in Oracle tenants include: DATE, TIMESTAMP, and INTERVAL. For more information about the datetime data types, see SQL Reference (Oracle Mode).
Datetime functions commonly 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. Sample code:
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. Sample code:
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