Skip to Main Content
  • Questions
  • Storing Date and Time in Separate fields Vs Using the same field

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Darryl.

Asked: November 19, 2001 - 9:53 am UTC

Last updated: July 16, 2013 - 4:46 pm UTC

Version: 7.3.4

Viewed 10K+ times! This question is

You Asked

Hi Tom,

My application uses separate fields for storing time components separately. There are calculations to compute the next available open session based on the particular time of the day. When the sessions (slots) gets filled up, the next available session is allocated whether the same day or the next day. The sessions are pre-defined at the specific time of the day with pre-defined number of slots. I am storing the contact date and time as well as the session date and time in separate fields. Also I am storing the "Session Time" available and number of slots in different fields ie Time_slot Varchar2(4)-- 24 hour format, Slots NUmber. Is it a good idea to store date and time in separate fields or in the same date time fields. What would you suggest out of own experience? It is also required that the time be displayed on the 12 hour format in the forms. I would appreciate your valued help on this.

Thanks



and Tom said...

YUCK

just use a single date field -- 7 bytes. You can display it in ANY format you like using to_char(), you can input with any format you like using TO_DATE.

You can get differences between two dates, sort them, max them, whatever...


Never never use a number or character string to store that which has a native format like a DATE. Don't use two fields either -- they always go together and you can always mask one or the other off for display.

Rating

  (27 ratings)

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

Comments

A reader, November 19, 2001 - 12:46 pm UTC

Thanks Tom

A reader, November 20, 2001 - 2:21 pm UTC

Could you please clarify ? Is there an implication to use DATE data type when you need just the time component for example ( time_scheduled varchar2(4) versus ( time_scheduled date )

Tom Kyte
November 20, 2001 - 2:49 pm UTC

If you have just a time (some representation of hours/minutes or whatever) feel free to store it in a character string/number or date. in Oracle9i we would store this in an INTERVAL type specifically designed to store durations.

YUCK... or just a reflection

Andre Whittick Nasser, November 20, 2001 - 3:15 pm UTC

Tom,

I have to admit you are an onomatopoeic guy ! Your interjections are sometimes exactly what it takes to convey the essence of some questions around here.

Do not think I am abstaining from it... I know I myself have polluted your site with plenty of nonsense. But, is it not what forums are for after all ?

ZAlex, May 07, 2003 - 6:22 am UTC

"You can get differences between two dates,
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^???
sort them, max them, whatever..."

FUNCTION AAAA() Return Number
IS
vlDate_Begin Date;
vlDate_End Date;
vlInterval_between INTERVAL DAY(3) TO SECOND(0);
vlRDV_MIN integer;
BEGIN
vlInterval_between := (vlDate_End - vlDate_Begin);
^^^Error
END;

Ora/doc/9.2/b10501_01/appdev.920/a96624/03_types.htm#41556
ok
Ora/doc/9.2/b10501_01/server.920/a96540/sql_elements2a.htm#54201
not ok
I cant't understand this:
Datetime - Datetime Interval (a)
(a) This operation is not valid for DATE values.

thanks

Tom Kyte
May 07, 2003 - 7:52 am UTC

well, it is quite simply because an interval is what is returned from the difference between two TIMESTAMPS.

A number is what is returned from the difference of two DATES.  Therefore, your code would resemble:


ops$tkyte@ORA920> create or replace function AAAA( vlDate_Begin in timestamp, vlDate_End timestamp )
  2  Return interval day to second
  3  IS
  4    vlInterval_between INTERVAL DAY(3) TO SECOND(0);
  5  BEGIN
  6    vlInterval_between := (vlDate_End - vlDate_Begin);
  7    return vlInterval_between;
  8  END;
  9  /

Function created.


OR


ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace function bbbb( vlDate_Begin in date, vlDate_End in date )
  2  Return number
  3  IS
  4    vlRDV_MIN number;
  5  BEGIN
  6    vlRDV_MIN := (vlDate_End - vlDate_Begin);
  7    return vlRDV_MIN;
  8  END;
  9  /

Function created.


<b>and this is what you would expect:</b>


ops$tkyte@ORA920>
ops$tkyte@ORA920> select aaaa( sysdate, sysdate+.4321 ),
  2         bbbb( sysdate, sysdate+.4321 )
  3    from dual;

AAAA(SYSDATE,SYSDATE+.4321)                                                 BBBB(SYSDATE,SYSDATE+.4321)
--------------------------------------------------------------------------- ---------------------------
+000000000 10:22:13.000000000                                                                .432094907

ops$tkyte@ORA920>


<b>the interval shows there are 10 hours, 22 minutes and 13 seconds difference between the two, whereas the date shows there is .4321 (approximately) between the two.
</b>


 

... and before anyone asks

A reader, May 07, 2003 - 8:51 am UTC

... (sysdate+0.4321)-sysdate evaluates to .432094907 and not 0.4321 because Oracle calculates the required time to the nearest second. So in this case it's 10 hours, 22 minutes 13 seconds, while 0.4321 is acually 10 hours, 22 minutes 13.44 seconds.



How to store time in a separate column without any data-type convertion

Praveen KV, June 30, 2003 - 10:08 am UTC

Hi Tom,

1) I understand your explanation of storing date & time together. But if I use only the time part in my database (and never the date part), dont you think it is a waste of space to store the date in the default format (ie both date and time together)?

2) How can I store date and time parts in separate fields? ie without converiting them into any other data type.

3) If I use the default date format, how can I display the date and time part separately? Here I may even need to compare the time part with the time value contained in a variable.

Your help is highly appreciated.

Thanks and regard

Praveen



Tom Kyte
June 30, 2003 - 10:20 am UTC

1) in oracle9i, there is an interval type. No, I don't think it is a waste -- if you store it in a character string, hh:mi:ss will take more space then a date. if you store it in a number, you might save 2 bytes or so per record. sort of insignificant. (and an interval in 9i, while only storing some period of time, will consume even more bytes but anyway)

2) you cannot, not in 8i. in 9i, you can store a date and an interval (period of time) separately if you like -- but if space is your overriding concern, that would take even more room.

3) you won't be using the default date format then.

select to_date( dt, 'dd-mon-yyyy' ), to_date( dt, 'hh24:mi:ss' ) from t;




useful but would like to have more info if you can.

Lebon Mathew, June 30, 2003 - 11:03 am UTC

We use Oracle Forms as our front end. We created a diffault forms out of the date field . What we need is if forms did a query for a particular date we want all the records for that date displayed. this can be attained if we store the dates with out time .. in trigger put a (trunc (sysdate)) , but at the same time we need to make calculations on howmany mins it took to finish the Service call so that we can messure the productivity of that call agent. At this case we are storing date in one field and and datetime in another ... Is it ok ? is there any simple way in forms by which we can display all the records for a particular date from the datetime field.

Tom Kyte
June 30, 2003 - 11:16 am UTC

so, you would search:

where dt >= trunc(:dt_input) dt < trunc(:dt_input)+1

that gets everything in that day.


you want A SINGLE, SOLE (eg: one) field.

Using datetime vs. number

Peter Turányi, October 07, 2005 - 6:25 am UTC

You wrote: You can get differences between two dates, sort them, max them, whatever...

1. But you cannot sum, add...
You must use INTERVAL.
With number you can do everything and convert to date.

2. Why:
>where dt >= trunc(:dt_input) dt < trunc(:dt_input)+1
>that gets everything in that day.

where dt = trunc(:dt_input)
gets everything in that day too and use index



Tom Kyte
October 07, 2005 - 8:43 am UTC

1) eh???? don't get it - what do you mean? What does it mean to "sum" a date? to add two dates??

You can use numbers OR intervals to add time to a date.

2) umm, dt = trunc(:dt_input) does not. That gets every record that is "as of midnight"

try for example,

where SYSDATE = trunc(:dt_input)

if you do that at 11am, you'll never get a row - because the time is stripped from the :dt_input.


where dt >= trunc(:dt_input) dt < trunc(:dt_input)+1

gets all rows such that DT is on that day - DT may include a TIME component.

Using datetime vs. number

Peter Turányi, October 10, 2005 - 9:25 am UTC

1) I use datetime datatype for storing time as you advice :ô)
2) If I store date as date not datetime then it will be allways midnight.

Tom Kyte
October 10, 2005 - 10:54 am UTC

1) I don't understand what you mean still.


2) that assumption was never stated - therefore, I never really made that assumption.

You would have to say:

IF you are using a date field AND you are only storing they YYYY-MM-DD component, so that the hh24:mi:ss = 00:00:00 in all cases then the between can obviously be simplified to:


where dt = trunc(:dt_input)

but in fact, the trunc is probably not necessary either as the input would not have any time associated with it.



Using datetime vs. number

Peter Turányi, October 11, 2005 - 9:23 am UTC

1) I mean this: Please advice me with this
I store arival time and departure time as datetime datatype
it is simply. I must keep delay time, loading time, unloading time too but but this is not timestamp but lasting of work (interval). How can I use databype interval with this? How I define table column?
2) OK. I agree.

Tom Kyte
October 11, 2005 - 3:34 pm UTC

1) as a table with timestamps and intervals?? not sure what you mean again.

why would "numbers" be "superior" to the right types?

Using datetime vs. number

Peter Turányi, October 12, 2005 - 6:30 am UTC

Imagine example:
you store information about lorries.
Lorry1 went at 08:00am from garage and came to store at 08:30am. Then load cargo 15 minutes.
Went at 08:30am + 15 min (arrival+loading time) and arrive to client at 09:15am. Then unload cargo 20 minutes and delay 5 minutes by formalities.
So I need calculate times and intervals.
I need calculate drive time (substract datetimes=number)
and summary it, summary (un)loading times, delay times etc..


Tom Kyte
October 12, 2005 - 7:35 am UTC

if you substract dates, you can get INTERVALS (15 minutes, 2 days, whatever).


date arithmetic can result in intervals - units of time. I'm still not getting your "point"? You can use an interval to store a duration of time, you can use dates to store dates, you can use numbers to store -- well, numbers of course.




Another reason for the right data type

Dan, October 12, 2005 - 10:10 am UTC

Suppose Lorry 1 leaves at 11:45PM and comes back at 12:15AM the next day...

Suppose Lorry 1 leaves at 11:45PM and breaks down, doesn't get back until 10:00AM 2 days later...

The query to get the elapsed time, with anything other than dates will be really nasty.



Using intervals and number

Peter Turányi, October 13, 2005 - 2:29 am UTC

I see INTERVAL datatype as good decision but I don't know how define column with datatype INTERVAL.
I use Oracle9i Release 9.0.1.1.1.

To Dan: With Number(6) you can store elapsed time up to 2 years with precision minutes.

Tom Kyte
October 13, 2005 - 10:33 am UTC

 
ops$tkyte@ORA9IR1> create table t ( x interval day to second );
 
Table created.
 

answer was very good just need more info

Gopesh Sharma, November 02, 2005 - 4:16 pm UTC

My req only to store time (cut off time etc)
I have few doubts about this data type
1.Do I have to always use TO_DSINTEVAL to insert data in to this filed
2. How should the default time format 12HR or 24 HR be specified for this.and how to do that in TO_DSINTERVAL again should I use NLS params
Thanks for advice otherwise the first thing that occurs is store time in varchar2

Tom Kyte
November 03, 2005 - 6:49 am UTC

1) no, there are many other interval functions, you can use timestamp arithmetic, you can use string literals/variables.

2) did not understand?

B-Tree Date Indexes

Yuan, November 30, 2005 - 9:42 am UTC

I was strongly in your camp on this topic, but a colleague of mine brought up a point that I don't know how to argue.

1. Using "where dt >= trunc(:dt_input) dt < trunc(:dt_input)+1" would utilize any index dt, but wouldn't the index on dt (with time) tend to be much larger than an index on dt_only (trunc(dt))? If so, would this be a compelling reason to store both dt_only and dt?

2. How about a function based index on trunc(dt) instead? Would that be very expensive during bulk load operations?

Tom Kyte
November 30, 2005 - 2:52 pm UTC

date and trunc(date) both occupy......

7 bytes.


You can use a function based index, yes. But many people either don't want to, or don't "know" to.

If you never use the time component, perhaps it should never have been loaded ;)

B-Tree Date Indexes (continued)

Yuan, November 30, 2005 - 7:26 pm UTC

Well actually, by a larger index, I mean one that has more branches and levels. I don't see why keeping the time portion or not should make a difference, but that is his argument.

Tom Kyte
November 30, 2005 - 9:18 pm UTC

ops$tkyte@ORA10GR2> create table t ( d1 date, d2 date );

Table created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> insert /*+ APPEND */ into t
  2  select sysdate + rownum/24/60/60,
  3         trunc( sysdate + rownum/24/60/60, 'd')
  4    from big_table.big_table;

1000000 rows created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create index t1_idx on t(d1);

Index created.

ops$tkyte@ORA10GR2> create index t2_idx on t(d2);

Index created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec show_space( 'T1_IDX', user, 'INDEX' );
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................           2,658
Total Blocks............................           2,816
Total Bytes.............................      23,068,672
Total MBytes............................              22
Unused Blocks...........................             105
Unused Bytes............................         860,160
Last Used Ext FileId....................               4
Last Used Ext BlockId...................          17,289
Last Used Block.........................              23

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> exec show_space( 'T2_IDX', user, 'INDEX' );
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................           2,660
Total Blocks............................           2,816
Total Bytes.............................      23,068,672
Total MBytes............................              22
Unused Blocks...........................             103
Unused Bytes............................         843,776
Last Used Ext FileId....................               4
Last Used Ext BlockId...................           7,817
Last Used Block.........................              25

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> select count(distinct d1), count(distinct d2) from t;

COUNT(DISTINCTD1) COUNT(DISTINCTD2)
----------------- -----------------
          1000000                 3


ops$tkyte@ORA10GR2> create or replace function is_number( p_str in varchar2 ) return number
  2  as
  3          l_number number;
  4  begin
  5          l_number := p_str;
  6          return 1;
  7  exception
  8          when others then return 0;
  9  end;
 10  /

Function created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> analyze index t1_idx validate structure;

Index analyzed.

ops$tkyte@ORA10GR2> create table idx_stats as select * from index_stats;

Table created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> analyze index t2_idx validate structure;

Index analyzed.

ops$tkyte@ORA10GR2> insert into idx_stats select * from index_stats;

1 row created.

ops$tkyte@ORA10GR2> column cname format a25
ops$tkyte@ORA10GR2> column t1 format a20
ops$tkyte@ORA10GR2> column t2 format a20
ops$tkyte@ORA10GR2> select a.cname,
  2         decode( is_number(a.val),0,a.val,round(a.val,2)) t1,
  3         decode( is_number(b.val),0,b.val,round(b.val,2)) t2,
  4             case when is_number(a.val) = 1 and is_number(b.val) = 1
  5                  then to_char( decode(a.val,'0',null,round(b.val/a.val*100,2) ), '9,999.00' )
  6                   end pct
  7    from table( cols_as_rows( q'|select *
  8                                   from idx_stats
  9                                  where name = 'T1_IDX' |' ) ) a,
 10         table( cols_as_rows( q'|select *
 11                                   from idx_stats
 12                                  where name = 'T2_IDX' |' ) ) b
 13       where a.cname = b.cname
 14  /

CNAME                     T1                   T2                   PCT
------------------------- -------------------- -------------------- ---------
HEIGHT                    3                    3                       100.00
BLOCKS                    2816                 2816                    100.00
NAME                      T1_IDX               T2_IDX
PARTITION_NAME
LF_ROWS                   1000000              1000000                 100.00
LF_BLKS                   2653                 2653                    100.00
LF_ROWS_LEN               19000000             19000000                100.00
LF_BLK_LEN                7996                 7996                    100.00
BR_ROWS                   2652                 2652                    100.00
BR_BLKS                   6                    8                       133.33
BR_ROWS_LEN               39736                55671                   140.10
BR_BLK_LEN                8028                 8028                    100.00
DEL_LF_ROWS               0                    0
DEL_LF_ROWS_LEN           0                    0
DISTINCT_KEYS             1000000              3                          .00
MOST_REPEATED_KEY         1                    604800               #########
BTREE_SPACE               21261556             21277612                100.08
USED_SPACE                19039736             19055671                100.08
PCT_USED                  90                   90                      100.00
ROWS_PER_KEY              1                    333333.33            #########
BLKS_GETS_PER_ACCESS      4                    166670.17            #########
PRE_ROWS                  0                    0
PRE_ROWS_LEN              0                    0
OPT_CMPR_COUNT            0                    1
OPT_CMPR_PCTSAVE          0                    41

25 rows selected.


Might be time to rethink that argument.  


Things that are said are typically testable - it is really easy to measure many things....

(and please don't ask why the index with the full date+time is actually 2 blocks smaller :) 

Thanks!

Yuan, December 01, 2005 - 7:10 am UTC

I love being right. Thanks!

Tom Kyte
December 01, 2005 - 12:45 pm UTC

don't gloat :) that'll just make them mad.

Index size of date vs trunc(date)

Bob B, December 01, 2005 - 1:14 pm UTC

As shown above, index size is basically the same for date vs trunc(date). What would change and is worth looking at, is the statistics. The optimizer will see the date index as very selective and with a high number of distinct entries (generally). For the trunc( date ) index, it will see a much less selective index with less distinct entries.

Personally, I think its better to keep the time in there. If you want to run a lot of "TRUNC( DATE ) between" queries, then an FBI on TRUNC( DATE ) will get you the idx you're looking for without any loss of data.

Tom Kyte
December 02, 2005 - 9:54 am UTC



Personally - I think if the time is stored, then by all means keep the time in there. If the time wasn't relevant to anyone, it never would have been stored in the first place.

Index size

Vivek Gandhi, December 02, 2005 - 11:36 am UTC

Hi Tom

This is in reference to the above test on index size comparision for the date columns -- trunc(date) v/s date

The table you created had

select count(distinct d1), count(distinct d2) from t;

COUNT(DISTINCTD1) COUNT(DISTINCTD2)
----------------- -----------------
1000000 3


My understanding of btree indexes is that the size depends on the unique values on the indexed column.
If the there is so much difference in the count of unique values, why is it that the there is not much difference in the index size ? Please help me understand this.
Thanks in advance
Vivek


Tom Kyte
December 02, 2005 - 11:45 am UTC

... My understanding of btree indexes is that the size depends on the unique values on the indexed column. ...

apparently not :)


Now, if I had used COMPRESS 1 - perhaps, sure, but a regular normal b*tree - apparently not.

A date is a fixed width 7 byte field.

Followup for B-Tree Date Indexes November 30, 2005

Sathish, June 28, 2006 - 5:59 am UTC

So Tom it is better to just store the date portion in a date field if you are not going to use the time portion. Will the queries run faster that way?
When i did a small test to query the table using date index vs date time index, the number of consistent gets were the same.


Tom Kyte
June 28, 2006 - 7:48 am UTC

a date is a fixed with 7 byte datatype. period, you are storing the time there whether you want to or not. It'll be stored as midnight.



How Many Bytes In a Timestamp Field

Jay, July 30, 2007 - 2:50 pm UTC

Tom,

How many bytes are in a timestamp field? Also, is it different for timestamp with time zone and timestamp with local time zone? We need to factor the sizes into consideration for the datatype selections.

Thanks,
Jay
Tom Kyte
July 30, 2007 - 5:53 pm UTC

<quote src=Expert Oracle Database Architecture>
TIMESTAMP Type

The TIMESTAMP type is very much like the DATE, with the addition of support for fractional seconds and time zones. We¿ll look at the TIMESTAMP type in the following three sections: one with regard to just the fractional second support but no time zone support, and the other two with regard to the two methods of storing the TIMESTAMP with time zone support.

TIMESTAMP
The syntax of the basic TIMESTAMP datatype is straightforward:

TIMESTAMP(n)

where N is optional and is used to specify the scale of the seconds component in the timestamp and may take on values between 0 and 9. If you specify 0, then a TIMESTAMP is functionally equivalent to a DATE and in fact stores the same values in the same manner:

ops$tkyte@ORA10G> create table t
  2  ( dt   date,
  3    ts   timestamp(0)
  4  )
  5  /
Table created.
 
ops$tkyte@ORA10G> insert into t values ( sysdate, systimestamp );
1 row created.
 
ops$tkyte@ORA10G> select dump(dt,10) dump, dump(ts,10) dump
  2    from t;
 
DUMP                                 DUMP
------------------------------------ ------------------------------------
Typ=12 <b>Len=7</b>: 120,105,6,28,11,35,41  Typ=180 Len=7: 120,105,6,28,11,35,41

The datatypes are different (the Typ= field indicates that), but the manner in which they store data is identical. The TIMESTAMP datatype will differ in length from the DATE type when you specify some number of fractional seconds to preserve, for example:

ops$tkyte@ORA10G> create table t
  2  ( dt   date,
  3    ts   timestamp(9)
  4  )
  5  /
Table created.
 
ops$tkyte@ORA10G> insert into t values ( sysdate, systimestamp );
1 row created.
 
ops$tkyte@ORA10G> select dump(dt,10) dump, dump(ts,10) dump
  2    from t;
 
DUMP                                  DUMP
------------------------------------- -------------------------------------
Typ=12 Len=7: 120,105,6,28,11,46,21   Typ=180 <b>Len=11:</b> 120,105,6,28,11,46,21
                                      ,44,101,192,208


Now the TIMESTAMP consumes 11 bytes of storage, and the extra 4 bytes at the end contain the fractional seconds, which we can see by looking at the time that was stored:
ops$tkyte@ORA10G> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
Session altered.
 
ops$tkyte@ORA10G> select * from t;
 
DT                   TS
-------------------- --------------------------------
28-jun-2005 10:45:20 28-JUN-05 10.45.20.744866000 AM

ops$tkyte@ORA10G> select dump(ts,16) dump from t;
 
DUMP
--------------------------------------------------
Typ=180 Len=11: 78,69,6,1c,b,2e,15,2c,65,c0,d0
 
ops$tkyte@ORA10G> select to_number('2c65c0d0','xxxxxxxx') from dual;
 
TO_NUMBER('2C65C0D0','XXXXXXXX')
--------------------------------
                       744866000

We can see the fractional seconds that were stored are there in the last 4 bytes. We used the DUMP function to inspect the data in HEX this time (base 16) so we could easily convert the 4 bytes into the decimal representation.

....

TIMESTAMP WITH TIME ZONE Type

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@ORA10G> create table t
  2  (
  3    ts    timestamp,
  4    ts_tz timestamp with time zone
  5  )
  6  /
Table created.
 
ops$tkyte@ORA10G> insert into t ( ts, ts_tz )
  2  values ( systimestamp, systimestamp );
1 row created.
 
ops$tkyte@ORA10G> select * from t;
 
TS                           TS_TZ
---------------------------- -----------------------------------
28-JUN-05 01.45.08.087627 PM 28-JUN-05 01.45.08.087627 PM -04:00
 
ops$tkyte@ORA10G> select dump(ts), dump(ts_tz) from t;
 
DUMP(TS)
-------------------------------------------------------------------------------
DUMP(TS_TZ)
-------------------------------------------------------------------------------
Typ=180 Len=11: 120,105,6,28,14,46,9,5,57,20,248
Typ=181 <b>Len=13:</b> 120,105,6,28,18,46,9,5,57,20,248,16,60

So, as you can see, upon retrieval the default TIMESTAMP format included the time zone information (I was on East Coast U.S. time during daylight saving 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 ¿18,46,9¿ for the hour, minutes, and seconds (in excess-1 notation, so that is 17:45:08), whereas the TIMESTAMP field stored simply ¿14,46,9¿, which is 13:45:09¿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.
</quote>

Re: How Many Bytes in a Timestamp

Jay, July 31, 2007 - 9:49 am UTC

Thanks, Tom, you're the best!

Following your lead, I included a field of type Timestamp with Local Time Zone 

  1  create table t4
  2  (ts timestamp,
  3   ts_tz timestamp with time zone,
  4*  ts_ltz timestamp with local time zone)
jz@test> /

Table created.

jz@test> insert into t4 values (systimestamp, systimestamp, systimestamp);

1 row created.

jz@test> select ts, dump(ts, 10) dmp,
jz@test>  ts_tz, dump(ts_tz, 10) dmp,
jz@test>  ts_ltz, dump(ts_ltz, 10) dmp
jz@test> from t4;

TS
---------------------------------------------------------------------------
DMP
--------------------------------------------------------------------------------

TS_TZ
---------------------------------------------------------------------------
DMP
--------------------------------------------------------------------------------

TS_LTZ
---------------------------------------------------------------------------
DMP
--------------------------------------------------------------------------------

31-JUL-07 09.34.59.151672 AM
Typ=180 Len=11: 120,107,7,31,10,35,60,9,10,84,192
31-JUL-07 09.34.59.151672 AM -04:00
Typ=181 Len=13: 120,107,7,31,14,35,60,9,10,84,192,16,60
31-JUL-07 09.34.59.151672 AM
Typ=231 Len=11: 120,107,7,31,9,35,60,9,10,84,192


1 row selected.

Based on above test, TIMESTAMP WITH LOCAL TIME ZONE uses 11 bytes, the same as bare TIMESTAMP. The hour-min-second values are 9,35,60, or 8:34:59, one hour less than timestamp. Is this because I'm in EDT (-4:00) and TIMESTAMP WITH LOCAL TIME ZONE actually records the EST (without daylight saving) time?

Thanks again,
Jay

Tom Kyte
August 02, 2007 - 9:54 am UTC

the with local time zone stuff is stored in a canonical format.

<quote from the same book as above....>

TIMESTAMP WITH LOCAL TIME ZONE Type
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@ORA10G> 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@ORA10G> insert into t (dt, ts1, ts2)
  2  values ( timestamp'2005-06-05 17:02:32.212 US/Pacific',
  3           timestamp'2005-06-05 17:02:32.212 US/Pacific',
  4           timestamp'2005-06-05 17:02:32.212 US/Pacific' );
1 row created.

ops$tkyte@ORA10G> select dbtimezone from dual;
 
DBTIMEZONE
----------
US/Eastern

Now, when we dump those values as follows:
ops$tkyte@ORA10G> select dump(dt), dump(ts1), dump(ts2) from t;
 
DUMP(DT)
------------------------------------
DUMP(TS1)
------------------------------------
DUMP(TS2)
------------------------------------
Typ=12 Len=7:   120,105,6,5,18,3,33
Typ=181 Len=13: 120,105,6,6, 1,3,33,12,162,221,0,137,156
Typ=231 Len=11: 120,105,6,5,21,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 5-Jun-2005 17: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 seven hours off UTC. Therefore, the stored date/time was 6-Jun-2005 00:02:32.212. It advanced our input time by seven 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, 17:02:32 US/Pacific is 20:02:32 US/Eastern, so that is what was stored in the bytes ¿21,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@ORA10G> select ts1, ts2 from t;
 
TS1
----------------------------------------
TS2
----------------------------------------
05-JUN-05 05.02.32.212000 PM US/PACIFIC
05-JUN-05 08.02.32.212000 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 17:02:32 US/Pacific on the way in is now displayed as 8:00 pm East Coast time on the way out.
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 databases time zone is ¿frozen,¿ and you will not be able to change it:
ops$tkyte@ORA10G> 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 
           TIMESTAMP WITH LOCAL TIME ZONE columns

ops$tkyte@ORA10G> !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
//          TIMESTAMP WITH LOCAL TIME ZONE columns.
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!


</quote>

just storing time?

Sharon Kovac, May 06, 2008 - 2:44 pm UTC

What datatype would you recommend for just storing time? I want to create a table that stores employees' work hours. For example, employee_id, start_shift, end_shift where start_shift is 08:30 and end_shift is 17:00.

Thanks,
Sharon
Tom Kyte
May 07, 2008 - 1:02 am UTC

an interval might be useful here - but it depends on how you most wanted to use it.

I think of the interval simply because you can add that to a timestamp (without any time bits set yet) to get the start and end times for any day.

Splitting the Timestamp

Matt, June 04, 2009 - 10:43 pm UTC

Tom.

We have a Timestamp field in our Fact Table in the Datawarehouse.

The user requirement is to run FACT reports and drill down to an hourly level. But the user also wants to see Dates/Times returned where there was no fact value for the Day/Hour in question etc.. in an Outer Join style.

In this instance (and using our BI tool) we need a driving Time Dimension to join to. It would be impractical to hold it at the Timestamp level, due to the number of rows generated (One for every hour for every day)

I was considering splitting out into a DATES Dimension Table which holds every DATE (DD-MON-YYYY) combination for the reporting period, and a TIMES Dimension Table for 24 hour reporting purposes (using an Interval). We could go down to a lower granularity (Minute) if required.

Using this method, rather than CASTing the TIMESTAMP to DATE or TIME on the fly for the joins, I propose splitting it into two columns. So Source_Timestamp becomes Source_Date and Source_Interval.

We could now define FK from the FACT table to the new Dimensions and index as required.

I see this as being one time where splitting the column is useful. Would you agree?
Tom Kyte
June 08, 2009 - 11:45 am UTC

I have no idea how splitting this would solve anything?

and I don't know why casting would come into play?

flesh this out, I'm not following what you are proposing - give example?

Varchar2 to Interval Data type migration

Sangeetha, July 15, 2013 - 11:26 am UTC

Hi Tom,

We have a legacy system in MSaccess in which interval is stored in a varchar2 field. eg: 11:45 ( hour: minue) .
We are migrating the appliation to oracle and the column type I have as interval data type.

I am trying the migration like this example:
INSERT INTO INT_TABLE
(a
)
SELECT interval sta hour TO minute
FROM
(SELECT TO_CHAR(STA,'hh24:mi') FROM DELAYS@AMPPS.DB.QRGRP.LOCAL
);

But this is throwing exception :
ORA-00923: FROM keyword not found where expected
00923. 00000 - "FROM keyword not found where expected"
But if I try with like this it will work fine.
INSERT INTO INT_TABLE
(a
)
SELECT interval '11:55' hour TO minute
FROM
(SELECT TO_CHAR(STA,'hh24:mi') FROM DELAYS@AMPPS.DB.QRGRP.LOCAL
);

Why it is failing to take the column name though it is accepting the hard coded value in the same format?



Table script:
CREATE TABLE INT_TABLE
( A INTERVAL DAY (0) TO SECOND (0),
B INTERVAL DAY (0) TO SECOND (0)
) ;

I tried various formatting like extract, to_char etc. But still it is failing. Could you please let me know what I am doing wrong.

Thanks and Regards
Sangeetha

Tom Kyte
July 16, 2013 - 4:46 pm UTC

well, there is no column 'sta' being selected anywhere - not that the outer query can see anyway..

ops$tkyte%ORA11GR2> create table t ( sta varchar2(5) );

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t values ( '11:55' );

1 row created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select sta
  2    from (select to_char(sta,'hh24:mi') from t)
  3  /
select sta
       *
ERROR at line 1:
ORA-00904: "STA": invalid identifier


and even if we fix that:

ops$tkyte%ORA11GR2> select sta
  2    from (select to_char(sta,'hh24:mi') sta from t)
  3  /
  from (select to_char(sta,'hh24:mi') sta from t)
                       *
ERROR at line 2:
ORA-01722: invalid number





which happens because you are trying to to_char a CHAR!!!!! they are trying to accommodate that but they are trying to to_number sta by default - so it'll fail.

the second query of yours works since the to_char'ed column is just optimized away - we know we do not need it.


and the interval keyword is for use with interval literals, not expressions.



ops$tkyte%ORA11GR2> select to_dsinterval( '0 ' || sta || ':00' )
  2    from t
  3  /

TO_DSINTERVAL('0'||STA||':00')
---------------------------------------------------------------------------
+000000000 11:55:00.000000000



will work.

Varchar2 to Interval Data type migration

Sangeetha, July 15, 2013 - 12:40 pm UTC

Thanks a lot Tom. I used this function hh24mi_to_interval and it is working fine.

Thanks again
Sangeetha

another case

nguyen, August 22, 2014 - 7:20 am UTC

hi Tom,
i need to store date and/or time
i mean, i have probability 4 cases:
+date and time are not null
+date and time are null
+date = null and time <> null
+date <> null and time = null

(for me, one value = null => it means, it has no value
ie: time=00:00:00 is not null)

what type would you recommande to me? and how?
thanks a lot