A datetime expression produces a value of a datetime data type.
Syntax
The syntax of datetime expressions is as follows:
{TIMESTAMP | DATE} string [ AT
{ LOCAL
| TIME ZONE { '[ + | - ] hh:mi'
| DBTIMEZONE
| SESSIONTIMEZONE
| 'time_zone_name'
}
} ]
A combination of TIMESTAMP or DATE and a string literal yields a value of TIMESTAMP or DATE data type. Note that the string format must be the same as the system variables NLS_TIMESTAMP_FORMAT and NLS_DATE_FORMAT. You can use the following SQL statement to query the values of the 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 yield 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
Considerations
If you specify AT LOCAL, the database uses the time zone of the current session.
If you specify AT TIME ZONE, the time zone is returned based on the following rules:
'[ + | - ] hh:mi': specifies the time zone as an offset from UTC. Where,hhspecifies the number of hours, andmispecifies the number of minutes.DBTIMEZONE: uses the time zone specified when the database is created.SESSIONTIMEZONE: uses the time zone of the session created by default or created by the latestATLER SESSIONstatement.'time_zone_name': returns the name of the time zone. To obtain the list of valid time zone regions, you can run theV$TIMEZONE_NAMESstatement in the dictionary view.
Examples
Use AT TIME ZONE to convert a value of the TIMESTAMP 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