A datetime expression generates a value of the datetime data type.
Datetime expressions
The syntax of a datetime expression is as follows:
{TIMESTAMP | DATE} string [ AT
{ LOCAL
| TIME ZONE { '[ + | - ] hh:mi'
| DBTIMEZONE
| SESSIONTIMEZONE
| 'time_zone_name'
}
} ]
You can use a TIMESTAMP or DATE expression with a string literal to generate a value of the TIMESTAMP or DATE data type. The string must be in the format specified by the system variables NLS_TIMESTAMP_FORMAT and NLS_DATE_FORMAT. You can query the following SQL statement to view the values of these system variables:
obclient> SELECT * from v$nls_parameters WHERE parameter like '%FORMAT';
+-------------------------+------------------------------+--------+
| PARAMETER | VALUE | CON_ID |
+-------------------------+------------------------------+--------+
| NLS_DATE_FORMAT | DD-MON-RR | 0 |
| NLS_TIMESTAMP_FORMAT | DD-MON-RR HH.MI.SSXFF AM | 0 |
| NLS_TIMESTAMP_TZ_FORMAT | DD-MON-RR HH.MI.SSXFF AM TZR | 0 |
+-------------------------+------------------------------+--------+
3 rows in set
For example, if the value of the system variable NLS_TIMESTAMP_FORMAT is DD-MON-RR HH.MI.SSXFF AM, you can use the following expression to generate a value of the TIMESTAMP data type.
obclient> SELECT TIMESTAMP '2020-01-01 10:00:00' FROM DUAL;
+---------------------------------+
| TIMESTAMP'2020-01-0110:00:00' |
+---------------------------------+
| 01-JAN-20 10.00.00.000000000 AM |
+---------------------------------+
1 row in set
Rules
If you specify AT LOCAL, the database uses the time zone of the current session.
If you specify AT TIME ZONE, the rules for returning the time zone are as follows:
'[ + | - ] hh:mi': specifies the time zone as an offset from UTC. In this case,hhspecifies the number of hours, andmispecifies the number of minutes.DBTIMEZONE: specifies the database time zone established during database creation.SESSIONTIMEZONE: specifies the session time zone of the default session or the most recent session created by using theALTER SESSIONstatement.'time_zone_name': specifies the name of the time zone. You can view the list of valid time zone names in theV$TIMEZONE_NAMESview.
Examples
Use AT TIME ZONE to convert a value of the TIMESTAMP data type to a value of another time zone.
obclient> SELECT TIMESTAMP '2020-01-01 10:00:00' AT LOCAL FROM DUAL;
+--------------------------------------+
| TIMESTAMP'2020-01-0110:00:00'ATLOCAL |
+--------------------------------------+
| 01-JAN-20 10.00.00.000000 AM +08:00 |
+--------------------------------------+
1 row in set
obclient> SELECT TIMESTAMP '2020-01-01 10:00:00' AT TIME ZONE '-05:00' FROM DUAL;
+-------------------------------------------------+
| TIMESTAMP'2020-01-0110:00:00'ATTIMEZONE'-05:00' |
+-------------------------------------------------+
| 31-DEC-19 09.00.00.000000 PM -05:00 |
+-------------------------------------------------+
1 row in set