Oracle's Timestamp data types enhance the Date data type by including fractional seconds and time zone information. It's range is from January 1, 4712 BC to December 31, 9999 AD. Timestamps come in three different flavors:
Oracle TIMESTAMP
This data type extends the DATE data type by including fractional seconds. This is useful when the seconds are just not granular enough, such as the outcome of a race.
Specify a TIMESTAMP column using TIMESTAMP([seconds_precision]), where seconds_precision is from 0 to 9. 6 is the default if omitted.
The TIMESTAMP Keyword
Expressing literal TIMESTAMP values can be done using the TIMESTAMP keyword, and specifying the Year, Month, Day, Hour, Minute, Second and fractions of seconds (if desired) values. This is similar to the DATE keyword. Here is an example:
select TIMESTAMP '1969-06-25 16:32:45.333'
as mydate from dual;
| mydate |
|---|
| 25-JUN-69 04.32.45.333000000 PM |
The TO_TIMESTAMP Function
Use the TO_TIMESTAMP function to convert a literal string into a TIMESTAMP value. This function uses Format Patterns, similar to the TO_DATE function. Here is an example:
select to_timestamp(
'2008-11-02 4:05:10.333 PM','YYYY-MM-DD HH:MI:SSXFF AM')
as mydate from dual;
| mydate |
|---|
| 02-NOV-08 04:05:10.333000000 PM |
Consult TO_TIMESTAMP in Oracle Date Functions for more information.
The TO_CHAR Function
Oracle uses this function to convert a DATE or TIMESTAMP value to a string for presentation. Here is an example using today's date, contained in the current_timestamp variable.
select
TO_CHAR(current_timestamp,'YYYY-MM-DD HH24:MI:SSXFF')
as mydate from dual;
| mydate |
|---|
| 2008-11-01 19:20:52.703000 |
Consult TO_CHAR in Oracle Date Functions for more information.
The NLS_TIMESTAMP_FORMAT Setting
Oracle has a default Format Pattern contained in the NLS_TIMESTAMP_FORMAT setting. It is applied when reading a TIMESTAMP value, and no Format Pattern has been used. For an example, please consult The NLS_DATE_FORMAT Setting, as these two settings have a similar function.
Oracle TIMESTAMP WITH TIME ZONE
The Oracle TIMESTAMP WITH TIME ZONE data type extends TIMESTAMP data type to include time zone information. This is very useful if a single table is to have date/time information stored in it from different time zones.
Specify a column using TIMESTAMP([seconds_precision]) WITH TIME ZONE, where seconds_precision is from 0 to 9. 6 is the default if omitted.
The TIMESTAMP keyword can be used to express a TIMESTAMP WITH TIME ZONE literal in the same way as with a TIMESTAMP value. But in this case, time zone information can also be included.
The TO_CHAR function operates the same when used with this data type, only time zone information can be added as well.
The NLS_TIMESTAMP_TZ_FORMAT setting is the same as the NLS_TIMESTAMP_FORMAT setting, only it is directed towards timestamps which include time zones.
The TO_TIMESTAMP_TZ Function
This function operates similar to the TO_TIMESTAMP function, only this function allows time zone information to be included. Here is a repeat of Figure 2 with time zone information added.
select to_timestamp_tz(
'2008-11-02 4:05:10.333 PM -04:00',
'YYYY-MM-DD HH:MI:SSXFF AM TZH:TZM')
as mydate from dual;
| mydate |
|---|
| 02-NOV-08 04:05:10.333000000 PM -04:00 |
Time Zone Region Names
Time zone information can be expressed as the number of hours from UTC time (as in Figure 4), or as a time zone region name. Here is an example of the latter:
select
TO_TIMESTAMP_TZ(
'2008-11-02 19:45:30 canada/eastern',
'YYYY-MM-DD HH24:MI:SS TZR')
as mydate from dual;
| mydate |
|---|
| 02-NOV-08 07.45.30.000000000 PM CANADA/EASTERN |
The region names are case-insensitive. For a complete list of acceptable region names, consult the v$timezone_names system view.
Daylight Savings Time
Oracle can determine if Daylight Savings Time (DST) is being observed if the hour, minute and time zone information are all supplied. Here is an example using today's date:
-- make Oracle include time zone and DST information.
alter session set nls_timestamp_tz_format=
'YYYY-MM-DD HH24:MI:SS TZR TZD';
-- tell Oracle what time zone we are in.
alter session set time_zone='US/Eastern';
-- now select today's date.
select current_timestamp from dual;
| current timestamp |
|---|
| 2009-04-05 23:45:35 US/EASTERN EDT |
Oracle correctly determined we are observing Daylight Savings.
When DST goes into effect, the time changes from 1:59:59am to 03:00:00am. The minutes from 2:00:00 to 2:59:59 do not exist, and are considered invalid.
When Standard Time goes back into effect, the time changes from 02:00:00am back to 01:00:01am. A block of time will repeat itself. Therefore, it is possible to have a time of 1:00am EDT and 1:00am EST for the same day. To eliminate this confusion, include both the time zone name and it's abbreviation.
This problem, and the solution, are better understood with a long example. Daylight Savings Time begins on April 5th and ends on October 25th in 2009, at 2:00am in both cases.
-- create a table to contain Timestamps.
create table mytime(n number,t timestamp with time zone);
-- insert some values.
insert into mytime values(
1,to_timestamp_tz(
'2009-10-25 06:00:00 AM Canada/Eastern',
'YYYY-MM-DD HH:MI:SS AM TZR TZD'));
-- same insert statement substituting these values:
2,'2009-10-25 01:00:00 AM Canada/Eastern'
3,'2009-10-25 01:00:00 AM Canada/Eastern EDT'
4,'2009-10-25 01:00:00 AM Canada/Eastern EST'
commit;
-- now list the contents of the table.
select * from mytable order by n;
| n | t |
|---|---|
| 1 | 2009-10-25 01:00:00 CANADA/EASTERN EST |
| 2 | 2009-10-25 01:00:00 CANADA/EASTERN EST |
| 3 | 2009-10-25 01:00:00 CANADA/EASTERN EDT |
| 4 | 2009-10-25 01:00:00 CANADA/EASTERN EST |
Record 1 did not specify a time zone abbreviation. Notice that EST as correctly assumed.
Record 2 did not include an abbreviation either, but for some reason, EST was assumed. It was a toss-up between EST and EDT.
Records 2 and 3 show that 1:00am on this day can exist in both Daylight Savings and Standard Time. This is where the ambiguity sets in.
Record 4 clears up the ambiguity by explicitly specifying EST. This is the solution: Include both the time zone region name and the appropriate abbreviation for Daylight Savings or Standard Time.
Oracle TIMESTAMP WITH LOCAL TIME ZONE
The Oracle TIMESTAMP WITH LOCAL TIME ZONE data type differs from the TIMESTAMP WITH TIME ZONE data type in that time zone information is NOT stored in the database. Instead, when this data is retrieved, it is automatically adjusted to the user's session time zone. This is useful when datetime values will be read by users in different timezones.
All the functionality described above for the TIMESTAMP WITH TIME ZONE data type applies here.
Here is an example showing the difference between WITH TIME ZONE and WITH LOCAL TIME ZONE.
-- create a table to store the timestamp info.
create table mytime (
t1 timestamp with time zone,
t2 timestamp with local time zone);
-- insert some data.
insert into mytime values(
current_timestamp, current_timestamp);
commit;
-- retrieve the data.
select * from mytime;
| t1 | t2 |
|---|---|
| 02-NOV-08 12.01.15.046000 AM -05:00 | 02-NOV-08 12.01.15.046000 AM |
-- alter the session's time zone.
alter session set time_zone='-04:00';
-- retrieve the data again.
select * from mytime;
| t1 | t2 |
|---|---|
| 02-NOV-08 12.01.15.046000 AM -05:00 | 02-NOV-08 01.01.15.046000 AM |
The t1 column stored the time zone information with the data, and thus, it does not change. The t2 column did not store this info. Instead, t2 adjusted the datetime value to coincide with the user's session timezone. This behavior is more dynamic.