A datetime expression produces a value of a datetime data type.
Datetime expressions
Syntax of datetime expressions:
{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 (0.00 sec)
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
Usage notes
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': Specify the time zone as an offset from UTC. Where,hhspecify the number of hours, andmispecifies the number of minutes.DBTIMEZONE: Use the time zone specified when the database is created.SESSIONTIMEZONE: Use the time zone of the session created by default or created by the latestATLER SESSIONstatement.'time_zone_name': Return 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.
Example
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