Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question, arnab.

Asked: September 27, 2010 - 9:22 pm UTC

Last updated: March 21, 2024 - 12:30 am UTC

Version: 9.1

Viewed 10K+ times! This question is

You Asked

Hi Tom

Here is the problem description-
A reporting tables holds data with the date columns in EST. The report when viewed the same columns are reported in CST.
I can add a straight offset and make the report available with the columns in CST. But what happens during the DST changes. Will this strategy work during the DST change period.


and Tom said...

why not use a timestamp with timezone?

When you have a date like 2:00am - you don't really know what time it is - but if it has a timezone (since timezones are relevant to you) - it would - it would be 2am EST or 2am EDT

read about new_time if you are using just dates:


  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment


arnab, September 28, 2010 - 11:36 am UTC

Thanks. Let me explain again.
I am storing data in a normal date column ( without timezone ) in 9i , i am in EST/EDT.
My report shows the data in CST/CDT. How should i treat data (after the DST is turned on) between EDT(2-3),
It is still CST between this time.Same is the case when it is turned off.
Tom Kyte
September 28, 2010 - 12:21 pm UTC

I know what you are doing.

... How should i treat data (after the DST is
turned on) between EDT(2-3), ...

you tell me - you don't have any timezone information - so you cannot tell the difference between EDT and EST at that point in time.

If you want standard timezone reporting, you would use a timestamp with timezone.

Otherwise, you don't have enough information stored to deal with this issue. new_time is going to be about it for you.

arnab, September 28, 2010 - 2:26 pm UTC

Hi Tom

So if i create something as - 
SQL> create table t
  2  (ts timestamp,
  3  tstz timestamp with time zone,
  4  tsltz timestamp with local time zone);

Table created.

And i create a record as - (lets say i am running this after the DST sets in after 2pm March14,2010 and it is EDT , but it is still CST elsewhere)
insert into tmp_tst_tz values (systimestamp, systimestamp, systimestamp) ;

Now after sometime while it is still CST and i select from the table to view the time in CST, what should occur.
Does oracle know what to do during that one hour period when it is EDT but still CST.

Tom Kyte
September 28, 2010 - 2:49 pm UTC

yes, it is time zone aware. Here is an excerpt from the 2nd edition of Expert Oracle Database Architecture, it shows this at work:


The TIMESTAMP WITH TIME ZONE type inherits all of the qualities of the TIMESTAMP type and adds time zone support. The TIMESTAMP WITH TIME ZONE type consumes 13 bytes of storage, with the extra two bytes being used to preserve the time zone information. It differs from a TIMESTAMP structurally only by the addition of these 2 bytes:

ops$tkyte@ORA11GR2> create table t
  2  (
  3    ts    timestamp,
  4    ts_tz timestamp with time zone
  5  )
  6  /
Table created.
ops$tkyte@ORA11GR2> insert into t ( ts, ts_tz )
  2  values ( systimestamp, systimestamp );
1 row created.
ops$tkyte%ORA11GR2> select * from t;

TS                           TS_TZ
---------------------------- -----------------------------------
12-APR-10 PM 12-APR-10 PM -04:00

ops$tkyte%ORA11GR2> select dump(ts) dump, dump(ts_tz) dump from t;

DUMP                                DUMP
----------------------------------- -----------------------------------
Typ=180 Len=11: 120,110,4,12,20,10, Typ=181 Len=13: 120,110,4,12,24,10,
53,29,250,104,112                   53,29,250,104,112,16,60

So, as you can see, upon retrieval the default TIMESTAMP WITH TIME ZONE format included the time zone information (I was on East Coast U.S. time during daylight savings time when this was executed). 

TIMESTAMP WITH TIME ZONEs store the data in whatever time zone was specified when the data was stored. The time zone becomes part of the data itself. Note how the TIMESTAMP WITH TIME ZONE field stored …24,10,53… for the hour, minutes, and seconds (in excess-1 notation, so that is 23:09:52), whereas the TIMESTAMP field stored simply …20,10,53…, which is 19:09:52 —the exact time in the string we inserted. The TIMESTAMP WITH TIME ZONE had four hours added to it, in order to store in GMT (also known as UTC) time. The trailing 2 bytes are used upon retrieval to properly adjust the TIMESTAMP value.

It is not my intention to cover all of the nuances of time zones here in this book; that is a topic well covered elsewhere. To that end, I’ll just point out that there is support for time zones in this datatype. This support is more relevant in applications today than ever before. A decade or so ago, applications were not nearly as global as they are now. In the days before widespread Internet use, applications were many times distributed and decentralized, and the time zone was implicitly based on where the server was located. Today, with large centralized systems being used by people worldwide, the need to track and use time zones is very relevant. 

Before time zone support was built into a datatype, it would have been an application function to store the DATE and in another column the time zone information, and then supply functions to convert DATEs from one time zone to another. Now that is the job of the database, and it is able to store data in multiple time zones:

ops$tkyte%ORA11GR2> create table t
  2  ( ts1  timestamp with time zone,
  3    ts2  timestamp with time zone
  4  )
  5  /
Table created.

ops$tkyte%ORA11GR2> insert into t (ts1, ts2)
  2  values ( timestamp'2010-02-27 16:02:32.212 US/Eastern',
  3           timestamp'2010-02-27 16:02:32.212 US/Pacific' );
1 row created.
and perform correct TIMESTAMP arithmetic on them:

ops$tkyte%ORA11GR2> select ts1-ts2 from t;

-000000000 03:00:00.000000

Since there is a three-hour time difference between those two time zones, even though they show the “same time” of 16:02:32.212, the interval reported is a three-hour difference. When performing TIMESTAMP arithmetic on TIMESTAMPS WITH TIME ZONE types, Oracle automatically converts both types to UTC time first and then performs the operation.


This type works much like the TIMESTAMP column. It is a 7- or 11-byte field (depending on the precision of the TIMESTAMP), but it is normalized to be stored in database’s time zone. To see this, we’ll use the DUMP command once again. First we create a table with three columns—a DATE, a TIMESTAMP WITH TIME ZONE, and a TIMESTAMP WITH LOCAL TIME ZONE—and then we insert the same value into all three columns:

ops$tkyte@ORA11GR2> create table t
  2  ( dt   date,
  3    ts1  timestamp with time zone,
  4    ts2  timestamp with local time zone
  5  )
  6  /
Table created.
ops$tkyte%ORA11GR2> insert into t (dt, ts1, ts2)
  2  values ( timestamp'2010-02-27 16:02:32.212 US/Pacific',
  3           timestamp'2010-02-27 16:02:32.212 US/Pacific',
  4           timestamp'2010-02-27 16:02:32.212 US/Pacific' );
1 row created.

ops$tkyte%ORA11GR2> select dbtimezone from dual;


Now, when we dump those values as follows:

ops$tkyte%ORA11GR2> select dump(dt), dump(ts1), dump(ts2) from t;

Typ=12 Len=7:   120,110,2,27,17,3,33
Typ=181 Len=13: 120,110,2,28,1,3,33,12,162,221,0,137,156
Typ=231 Len=11: 120,110,2,27,20,3,33,12,162,221,0

we can see that in this case, three totally different date/time representations were stored:

 * DT: This column stored the date/time 27-FEB-2010 16:02:32. The time zone and fractional seconds are lost because we used the DATE type. No time zone conversions were performed at all. We stored the exact date/time inserted, but lost the time zone.

 * TS1: This column preserved the TIME ZONE information and was normalized to be in UTC with respect to that TIME ZONE. The inserted TIMESTAMP value was in the US/Pacific time zone, which at the time of this writing was eight hours off UTC. Therefore, the stored date/time was 2828-FEB-2010 00:02:32. It advanced our input time by eight hours to make it UTC time, and it saved the time zone US/Pacific as the last 2 bytes so this data can be properly interpreted later.

 * TS2: This column is assumed to be in the database’s time zone, which is US/Eastern. Now, 16:02:32 US/Pacific is 19:02:32 US/Eastern, so that is what was stored in the bytes …20,3,33… (excess-1 notation; remember to subtract 1).

Since the TS1 column preserved the original time zone in the last 2 bytes, we’ll see the following upon retrieval:

ops$tkyte%ORA11GR2> select ts1, ts2 from t;

27-FEB-10 PM

 The database would be able to show that information, but the TS2 column with the LOCAL TIME ZONE (the time zone of the database) shows the time in database’s time zone, which is the assumed time zone for that column (and in fact all columns in this database with the LOCAL TIME ZONE). My database was in the US/Eastern time zone, so 16:02:32 US/Pacific on the way in is now displayed as 8:00 pm East Coast time on the way out.  You might be wondering why it is 8pm and not 7pm (3 hours difference).   That is because the date I stored was stored when ‘Standard’ time was in effect and when I retrieved it – the current time zone was in ‘Daylight Savings’  time – the extra hour is due to that (so if you see different results, it could well just be due to Standard versus Daylight Savings).  If you rerun the example with dates all in the same exact time period (I ran this originally on April 12th, 2010) :

ops$tkyte%ORA11GR2> delete from t;
1 row deleted.

ops$tkyte%ORA11GR2> insert into t (dt, ts1, ts2)
  2  values ( timestamp'2010-04-12 16:02:32.212 US/Pacific',
  3           timestamp'2010-04-12 16:02:32.212 US/Pacific',
  4           timestamp'2010-04-12 16:02:32.212 US/Pacific' );
1 row created.

ops$tkyte%ORA11GR2> select ts1, ts2 from t;

12-APR-10 PM

it works more like you might expect – a three hour difference between the two.  I only point this out to drive home the fact that time zone ‘math’ is much more complex than it appears!

The TIMESTAMP WITH LOCAL TIME ZONE provides sufficient support for most applications, if you need not remember the source time zone, but only need a datatype that provides consistent worldwide handling of date/time types. Additionally, the TIMESTAMP(0) WITH LOCAL TIMEZONE provides you the equivalent of a DATE type with time zone support—it consumes 7 bytes of storage and the ability to have the dates stored “normalized” in UTC form.

One caveat with regard to the TIMESTAMP WITH LOCAL TIME ZONE type is that once you create tables with this column, you will find your database’s time zone is “frozen,” and you will not be able to change it:

ops$tkyte@ORA11GR2> alter database set time_zone = 'PST';
alter database set time_zone = 'PST'
ERROR at line 1:
ORA-30079: cannot alter database timezone when database has 

ops$tkyte@ORA11GR2> !oerr ora 30079
30079, 00000, "cannot alter database timezone when database has 
               TIMESTAMP WITH LOCAL TIME ZONE columns"
// *Cause:  An attempt was made to alter database timezone with
//          TIMESTAMP WITH LOCAL TIME ZONE column in the database.
// *Action: Either do not alter database timezone or first drop all the

It should be obvious why, if you were to change the database’s time zone, you would have to rewrite every single table with a TIMESTAMP WITH LOCAL TIME ZONE—their current values would be wrong given the new time zone!


arnab, September 28, 2010 - 2:56 pm UTC

Thanks and superb!!!

UTC time format

Srini, October 14, 2010 - 11:43 am UTC

Tom , Thanks for your excellent explanation on timezone /DST. When using timestamp with UTC time, is there a way to express the timestamp in the UTC format - "<YYYY-MM-DD>T<HH:MI:SS>Z" - (Z being Zulu time in the UTC format.
Example -2010-10-14T14:05:09Z

Tom Kyte
October 15, 2010 - 7:54 am UTC

I don't know what you mean. If you are in UTC - then the timesstamps will be in UTC

ZULU time

Greg, October 15, 2010 - 3:22 pm UTC

if you're just looking to format the timestamp for display, try:
to_char(<timestamp col>,'YYYY-MM-DD"T"HH24:MI:SS"Z"')
using double quotes around the T and Z. My guess as to what you meant - I may be wrong

What happens to timestamp of record inserted at 2 AM EDT (during transition to DST time)

A reader, March 12, 2024 - 1:57 am UTC

Our application Dev team is reporting that, when they query the column which is of TIMESTAMP loaded at 2:36 AM US Eastern Time on 10-MAR-24 (Day Light Saving takes effect on 10-MAR-24 in US EDT).

Should it not report as 03:36 AM US EDT with DST in effect.

Can you please clarify, what should be correct value, 2 AM or 3 AM?

Query output is below-

from  btmsg.inst_merged where instr_id in (583993070,583993083)

SQL> /

------ ------------- ---------------------------------------- --------------------------------------------------------------------------------
-05:00     583993070 10-MAR-24 AM             Typ=180 Len=11: 120,124,3,10,3,37,33,4,242,122,192
-05:00     583993083 10-MAR-24 AM             Typ=180 Len=11: 120,124,3,10,3,43,17,39,224,33,64

Connor McDonald
March 12, 2024 - 8:17 am UTC

A "timestamp" is just that and nothing else. It does not have any timezone associated with it.

It is an "absolute" time for lack of a better term, for example, if you datapump-ed that data into a new database running in Europe, it will *still* be

10-MAR-24 AM
10-MAR-24 AM

Only "timestamp with time zone" will capture the time zone at the time of data creation

Day light savings

A reader, March 14, 2024 - 3:50 am UTC

Hi Connor,

Sorry for the follow-up question.
What happens to timestamp of record inserted at 2 AM EDT (during transition to DST time)

Appreciate if you could clarify this.

Database is on 19.18.

1) Is TIMESTAMP (6) datatype DST aware?

2) Should it report, 2 AM or 3 AM with TIMESTAMP (6) datatype during DST transition?

3) Or should we consider using "TIMESTAMP with TIMEZONE" datatype to handle Day Light Savings.

Thank you.
Connor McDonald
March 18, 2024 - 4:36 am UTC

1) No. It is a "moment in time"

2) No change because it has not time zone, eg

SQL> create table t ( ts timestamp );

Table created.

SQL> insert into t values ( timestamp '2024-04-01 13:00:00');

1 row created.

SQL> select * from t;

01-APR-24 PM

SQL> alter session set time_zone = '+01:00';

Session altered.

SQL> select * from t;

01-APR-24 PM

SQL> alter session set time_zone = '+09:00';

Session altered.

SQL> select * from t;

01-APR-24 PM

SQL> alter session set time_zone = '-05:00';

Session altered.

SQL> select * from t;

01-APR-24 PM

3) If I have a database that needs to know about timezones, I would always opt for UTC in the database, so every single date has a common definition of *when* it occurred, and then return the data in whatever timezone the client might request, because its always the *client* that has particular timezone needs not the database.

Of course, you might choose to *store* the nominated timezone for clients, but I'd store the raw data as UTC - makes like a lot easier.

Or at the very least, have a virtual column that has the UTC data.

Day Light Savings

A reader, March 19, 2024 - 1:09 am UTC

Hi Connor,
thanks for the detailed explanation. this clarified my questions.
Connor McDonald
March 21, 2024 - 12:30 am UTC

Glad we could help.

Rest assured you're not alone - everyone faces challenges with timezone data :-)