Previous
Previous
 
Next
Next

TIMESTAMP_LTZ Data Type

TIMESTAMP_LTZ corresponds to the SQL TIMESTAMP WITH LOCAL TIMEZONE data type. It is another variant of TIMESTAMP that includes a time zone offset in its value. It differs from TIMESTAMP_LTZ in that data stored in the Database is normalized to the database time zone, and the time zone offset is not stored as part of the column data. When a user retrieves the data, Oracle returns it in the user's local session time zone. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time—formerly Greenwich Mean Time). This data type is useful for displaying date information in the time zone of the client system in a two-tier application.

Oracle time zone data is derived from the public domain information available at ftp://elsie.nci.nih.gov/pub/. Oracle time zone data may not reflect the most recent data available at this site.

The TIMESTAMP_LTZ data type differs from TIMESTAMP_TZ in that data stored in the Database is normalized to the database time zone. The time zone offset is not stored as part of the column data. There is no literal for TIMESTAMP_LTZ. Rather, you represent values of this data type using any of the other valid datetime literals. The table that follows shows some formats you can use to add a TIMESTAMP_LTZ value into object, along with the corresponding value returned by a OLAP DML statement such as a SHOW command.

Value Specified When Adding Value Value Returned
'19-FEB-2004' 19-FEB-2004.00.00.000000 AM
SYSTIMESTAMP 19-FEB-04 02.54.36.497659 PM
TO_TIMESTAMP('19-FEB-2004', 'DD-MON-YYYY')); 19-FEB-04 12.00.00.000000 AM
SYSDATE 19-FEB-04 02.55.29.000000 PM
TO_DATE('19-FEB-2004', 'DD-MON-YYYY')); 19-FEB-04 12.00.00.000000 AM
TIMESTAMP'2004-02-19 8:00:00 US/Pacific'); 19-FEB-04 08.00.00.000000 AM

Notice that if the value specified does not include a time component (either explicitly or implicitly, then the value returned defaults to midnight.