转:Oracle Timestamp Data Types

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:

• TIMESTAMP
• TIMESTAMP WITH TIME ZONE
• TIMESTAMP WITH LOCAL TIME ZONE

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:

Figure 1: Expressing a Literal Timestamp Value.

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:

Figure 2: Using the TO_TIMESTAMP function.

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.

Figure 3: The TO_CHAR Function.

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.

Figure 4: Using the TO_TIMESTAMP_TZ function.

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:

Figure 5: Expressing time zone regions.

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:

Figure 6: Retrieving time zone and DST information.

-- 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.

Figure 7: The ambiguity that occurs when Daylight Savings ends.

-- 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.

Figure 8: Understanding TIMESTAMP 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.