Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: August 14, 2002 - 4:19 pm UTC

Last updated: March 14, 2024 - 2:20 am UTC

Version: 8.1.6

Viewed 100K+ times! This question is

You Asked

I would like to know if it is possible to configure the Oracle data format to also capture the timezone that date and time orginated.

and Tom said...

In Oracle9i release 1 (9.0) and up -- yes. There is a datatype TIMESTAMP WITH TIMEZONE that does that.

Before that -- no, the Oracle DATE datatype cannot store that, nor is it timezone aware.

Rating

  (106 ratings)

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

Comments

date fomat containing timezone data

Jasbir Kular, August 14, 2002 - 8:30 pm UTC

Thanks for the info. Since my company is using 8i and they are sloooowly migrating to 9i, I might just make my own data type that contains a date and a timezone field. Then I can use this user defined data type within my tables.

Tom Kyte
August 15, 2002 - 7:55 am UTC

Well, you might consider using this technical need as a push to migrate to 9i (you are after all doing some NEW DEVELOPMENT HERE, this isn't maintenance on an existing system, this is new development -- either a brand new app or version 2 of an existing one)

Just having the TZ stored with a date is virtually useless unless you write the thousands of lines of code needed to

o support date arithmetic, subtract 5pm EST from 7pm PST. That should be 5 hours, not 2

o support TZ conversion (and with umpteen timezones -- each with different rules -- good luck)



No point having TZ datatype column

Sajid Anwar, August 15, 2002 - 9:19 am UTC

Hi Tom,
As you mentioned two points about TZ. I dont see any reason why one would go for TimeZone datatype unles and until he/she finds the way to do arithmetic on it. It really surprised me that ORACLE hasnt given anything for it. Could you please tell us atleast two good reasons WHY SHOULD WE USE TimeZone datatype?

Regards,
Sajid Anwar

Tom Kyte
August 15, 2002 - 7:04 pm UTC

Umm, Oracle9i -- there is "TIMESTAMP WITH TIMEZONE" -- it does all of that. So, for the last two releases of Oracle -- you have had this capability.

problem inserting timestamp timezone

A reader, October 24, 2002 - 3:30 pm UTC

Hi

I am following some examples from ILT guide and I get this error

SQL> CREATE TABLE times (
  2   ts TIMESTAMP
  3   , tsz TIMESTAMP WITH TIME ZONE
  4   , tlz TIMESTAMP WITH LOCAL TIME ZONE
  5   , oldtime DATE );

SQL> INSERT INTO times VALUES
  2  ( '21-MAY-01 10:00:00.25 AM'
  3  , '21-MAY-01 10:00:00.25 AM CET'
  4  , '21-MAY-01 10:00:00.25 AM'
  5  , '21-MAY-01' );
( '21-MAY-01 10:00:00.25 AM'
  *
ERROR at line 2:
ORA-01830: date format picture ends before converting entire input string

SQL> SELECT DBTIMEZONE, SESSIONTIMEZONE from dual;

DBTIME SESSIONTIMEZONE
------ ---------------------------------------
+02:00 Europe/London

SQL> select * from nls_session_parameters;

PARAMETER                      VALUE
------------------------------ -------------------------------
NLS_LANGUAGE                   ENGLISH
NLS_TERRITORY                  UNITED KINGDOM
NLS_CURRENCY                   £
NLS_ISO_CURRENCY               UNITED KINGDOM
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                YYYY-MON-DD HH24:MI:SS
NLS_DATE_LANGUAGE              ENGLISH
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH24.MI.SSXFF
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH24.MI.SSXFF
NLS_TIME_TZ_FORMAT             HH24.MI.SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH24.MI.SSXFF TZR
NLS_DUAL_CURRENCY              €
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

Anything wrong with my session?!?!?

 

Tom Kyte
October 24, 2002 - 4:18 pm UTC

it is your nls_timestamp_format.  You have it with HH24 -- but you use a string with HH and AM:

ops$tkyte@ORA920.US.ORACLE.COM> alter session set NLS_TIMESTAMP_FORMAT = 'DD-MON-RR HH24.MI.SSXFF';

Session altered.

ops$tkyte@ORA920.US.ORACLE.COM> select localtimestamp from dual;

LOCALTIMESTAMP
---------------------------------------------------------------------------
24-OCT-02 16.22.39.517361

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> INSERT INTO times VALUES
  2  ( '21-MAY-01 10:00:00.25 AM'
  3  , '21-MAY-01 10:00:00.25 AM CET'
  4  , '21-MAY-01 10:00:00.25 AM'
  5  , '21-MAY-01' );
( '21-MAY-01 10:00:00.25 AM'
  *
ERROR at line 2:
ORA-01830: date format picture ends before converting entire input string


ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> alter session set NLS_TIMESTAMP_FORMAT = 'DD-MON-RR HH.MI.SSXFF AM';

Session altered.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> INSERT INTO times VALUES
  2  ( '21-MAY-01 10:00:00.25 AM'
  3  , '21-MAY-01 10:00:00.25 AM CET'
  4  , '21-MAY-01 10:00:00.25 AM'
  5  , '21-MAY-01' );

1 row created.

ops$tkyte@ORA920.US.ORACLE.COM> 

wierd result

A reader, October 25, 2002 - 1:00 am UTC

format iw returns wierd result.
My query is as follows:

SQL>select to_char(to_date('&dt','dd-mon-yyyy'),'iw') from dual;
Enter value for dt: 31-dec-2002
old   1: select to_char(to_date('&dt','dd-mon-yyyy'),'iw') from dual
new   1: select to_char(to_date('31-dec-2002','dd-mon-yyyy'),'iw') from dual

TO
--
01  

SQL> /
Enter value for dt: 30-dec-2002
old   1: select to_char(to_date('&dt','dd-mon-yyyy'),'iw') from dual
new   1: select to_char(to_date('30-dec-2002','dd-mon-yyyy'),'iw') from dual

TO
--
01 (but weeks belongs to year 2002 here it returns week of year 2003)

SQL> /
Enter value for dt: 29-dec-2002
old   1: select to_char(to_date('&dt','dd-mon-yyyy'),'iw') from dual
new   1: select to_char(to_date('29-dec-2002','dd-mon-yyyy'),'iw') from dual

TO
--
52

So any weekly report taken on 30/31 of dec will show a wrong result?Please discuss.
 

Tom Kyte
October 25, 2002 - 6:13 am UTC

depends on the number of weeks in the year -- the ISO guys say some days from last year belong to a week in this year.

it is not wrong. it is in fact correct.

to_char works to_date doesn't

Ram, January 20, 2003 - 7:00 pm UTC

Tom,
When I tried in sql/plsus, I'm getting the following error:

SQL> select to_char(sysdate,'CCYYMMDDHHMMSSmmmmmm') from dual; -----> this works

TO_CHAR(SYSDATE,'CCY
--------------------
21030120030129010101

SQL> select to_date('21030120030129010101','CCYYMMDDHHMMSSmmmmmm') from dual;
select to_date('21030120030129010101','CCYYMMDDHHMMSSmmmmmm') from dual
                                      *
ERROR at line 1:
ORA-01820: format code cannot appear in date input format


My vendor exchanges dates in this format.
How to solve this problem.
thanks
Ram 

Tom Kyte
January 20, 2003 - 7:22 pm UTC

well, just to be clear -- both to_char and to_date are working -- it is just that the format used for OUTPUT cannot be used for INPUT.


you'll have to subtract 100 years I suppose.  Maybe if you told me how you need to process this - I would be able to answer better.

ops$tkyte@ORA920> select add_months( to_date(substr('21030120030129010101',1,14),'YYYYMMDDHH24MISS'),
  2  -1200 ) from dual
  3  /

ADD_MONTH
---------
20-JAN-03
 

date format

Ram, January 21, 2003 - 1:15 pm UTC

Hi,
The purpose of this is, vendor sends a respone with date and time portion as mentioned in the above format, which we would like to store in our db (date column).
If the date and time I have received is more than what I have, I have to update some info. in that table.
let me know if it's possible.
thanks


Tom Kyte
January 21, 2003 - 2:23 pm UTC

I gave you the function to do that? now what?

timezone !!

A reader, March 04, 2003 - 1:45 pm UTC

Hi tom,

I know from above discussion 9i is the way to go for timezone info. but i don't have 9i in company.

I have 8i (8.1.7). and our application has needs to store that info and sysc sydny,tokyo, london time,perform calculations and output the data analysis..

so could you please suggest your view to store data for this type of app.

I was thinking to have everything in GMT in time dim. and
create another flake dim for conversion

table 1 : time
--------------
timekey number
...
other time info

table 2 : timezone
-------------------
zone varchar2
name varchar2
converzion_fn varchar2

sample values
zone name converzion_fn
EST EST GMT-5 or (timekey -5)


what do you say ?

any better solution for 8.1.7 ?




any solution to above question ?

A reader, March 04, 2003 - 9:30 pm UTC


Tom Kyte
March 05, 2003 - 9:04 am UTC

My gosh -- give me a brief moment of time to put it together sometimes!

Ok, we went back to our 817 code for a web calendar we built that needed timezone support. We did code up a package with extensive timezone support:

</code> http://asktom.oracle.com/~tkyte/timedate.zip <code>

It was authored by Joel Kallman and covers pretty much the globe as far as we know. The main routine after you install is:

create or replace package timedate_pkg
as

--
-- For any installation, the following package global variable
-- should be set to the locale of the server where this package
-- is installed (e.g., '1' implies that the database is running in
-- timezone/locale America/New York)
--
g_server_locale constant number := 1;



This converts a date into GMT -- you get the p_locale numbers from the ID column of the TIMEDATE_LOCALES$ table

function to_gmt_date(
p_local_date in date,
p_locale in number )
return date;


Converts gmt to some other timezone

function to_local_date(
p_gmt_date in date,
p_locale in number )
return date;

sort of like SYSDATE but returns SYSDATE adjusted to be in the timezone you ask for

function cur_local_date(
p_locale in number )
return date;

obvious


function cur_gmt_date
return date;
--

end timedate_pkg;
/

Thanks !!

A reader, March 07, 2003 - 4:10 pm UTC

thanks tom it's great !!

I am late to check back ain't i ?
This is great thanks !!

Looking for examples

Sikandar Hayat, April 23, 2003 - 1:30 pm UTC

Hi TOM,
Sorry for disturbance. I am trying to understand new time zone feature in 9i but unable to understand it as lack of some good examples.

I am reading about,
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
What is the difference in these and any examples to easily understand these.

I am currently reading the following,

</code> http://www.csis.gvsu.edu/GeneralInfo/Oracle/server.920/a96529/ch3.htm#50740 http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/whatsnew.htm#977988 <code>

Oracle 9i New Features by Robert G. Freeman


Tom Kyte
April 23, 2003 - 7:49 pm UTC

timestamp -- that doesn't have a timezone.  It is much like DATE is in Oracle8i except it supports fractional seconds.  

When you insert the time, the timezone is not saved with it -- it is just a "timestamp".  It'll be a value like "Wed April 4, 2003 10:05:02.123456 am"


A timestamp with timezone stores the timezone as well -- meaning if you were in California and I was in Virginia (i am, but I've no clue what TZ pakistan is in ;) and we both inserted that same time, yours would be:

Wed April 4, 2003 10:05:02.123456 am PACIFIC

and mine would be:

Wed April 4, 2003 10:05:02.123456 am EASTERN

and if we subtracted them from eachother -- the difference would be 3 hours since that is the difference between our time zones.  If we just used timestamp (without timezone) it would have be "0 hours" difference.


With LOCAL time zone just means that 

o Data is normalized to the database time zone when it is stored in the database. 

o When the data is retrieved, users see the data in the session time zone.


consider (my database is US/Eastern by the way...)

ops$tkyte@ORA920.US.ORACLE.COM> create table t ( x timestamp, y timestamp with time zone, z timestamp with local time zone )
  2  /

Table created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> alter session set time_zone = 'US/PACIFIC'
  2  /

Session altered.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> insert into t values ( systimestamp, systimestamp, systimestamp );

1 row created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select * from t;

X
---------------------------------------------------------------------------
Y
---------------------------------------------------------------------------
Z
---------------------------------------------------------------------------
23-APR-03 07.47.16.451473 PM
23-APR-03 07.47.16.451473 PM -04:00
23-APR-03 04.47.16.451473 PM



X is without timezone
Y is with timezone and that is 7.47 with a GMT minus 4 offset (4 hours from GMT)

Z is with timezone and the 7.47 that was inserted was "normalized", or input as eastern time (since systimestamp is the SERVERS time) and upon retrieval converted to MY timezone -- pacific -- for me.



ops$tkyte@ORA920.US.ORACLE.COM> delete from t;

1 row deleted.


ops$tkyte@ORA920.US.ORACLE.COM> insert into t values ( localtimestamp, localtimestamp, localtimestamp );

1 row created.

ops$tkyte@ORA920.US.ORACLE.COM> select * from t;

X
---------------------------------------------------------------------------
Y
---------------------------------------------------------------------------
Z
---------------------------------------------------------------------------
23-APR-03 04.50.06.474172 PM
23-APR-03 04.50.06.474172 PM US/PACIFIC
23-APR-03 04.50.06.474172 PM


these all come out the same since I used my own timezone but...

ops$tkyte@ORA920.US.ORACLE.COM> alter session set time_zone = 'US/CENTRAL';

Session altered.

ops$tkyte@ORA920.US.ORACLE.COM> select * from t;

X
---------------------------------------------------------------------------
Y
---------------------------------------------------------------------------
Z
---------------------------------------------------------------------------
23-APR-03 04.50.06.474172 PM
23-APR-03 04.50.06.474172 PM US/PACIFIC
23-APR-03 06.50.06.474172 PM


you can see the difference now with the timezone vs local timezone... 

One more

Sikandar Hayat, April 24, 2003 - 8:11 am UTC

Great examples to understand timestamp feature. Now one more question is if in "Timestamp with Local time zone" there is no zone

LOCALTIMESTAMP
------------------------------
24-APR-03 05.06.36.000001 PM

So how it is recognizing on client and giving the correct time or it is placing time zone info hidden.

Say on the server I have set time zone with '-05:00' while on client it is '+05:00'.

Tom Kyte
April 24, 2003 - 9:26 am UTC

clients have a timezone (see my alter session) and the database has a timezone.

The database takes the date in its timezone and converts it to whatever timezone the client asked for (has set)

that is the reason for the difference when I set the client timezone to US/Central for example -- the database returned the timestamp in a time relative to MY local timezone -- adjusted from the databases timezone.

Great...

Sikandar Hayat, April 24, 2003 - 12:20 pm UTC

Thanks TOM I got your point and timestamp concept.

A reader, June 02, 2003 - 9:11 am UTC

Hi Tom,
What is the differene between systimestamp and localtimestamp function ?

Thanks

Tom Kyte
June 02, 2003 - 9:30 am UTC

documentation is amazing for stuff like this:

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96540/functions64a.htm#999875

LOCALTIMESTAMP returns the current date and time in the <b>session time zone</b> in a value of datatype TIMESTAMP. The difference between this function and CURRENT_TIMESTAMP is that LOCALTIMESTAMP returns a TIMESTAMP value while CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value.

http://docs.oracle.com/docs/cd/B10501_01/server.920/a96540/functions130a.htm#999454 <code>

SYSTIMESTAMP returns the system date, including fractional seconds and time zone of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE.

v$timezone_names

phil, June 23, 2003 - 2:13 am UTC

in the view v$timezone_names , what does the TZABBREV actually represent ?

how can America/New_York and Australia/Sydney be EST ?

if i wanted to create a record with a "timestamp with time zone" for sydney ... how can i use the abbreviation. When I insert a record with an EST timezone the db tells me that I am -4 hours .... which is not sydney !

thanks



Tom Kyte
June 23, 2003 - 8:14 am UTC

Australia uses it too. is the the abbreviation and there are many "duplicates". It takes a locale and a tz abbreviation to know what is really means.

suggestion -- use long names to avoid any sense of confusion.

ora-01830

Tatiane, July 23, 2003 - 4:36 pm UTC

Tom, I am getting an intermittent ora-01830 when I run the code below. It may run several times without an error, but sometimes there it is, for the SAME data.

There are occasions when I run it, the error comes up -- I reload the page (using the refresh button on the browser) and then the page appears without the error. Very strange, isn't it ?

This code has run in development, uat and this error happens only in production. They are all 9.2.0.1. I searched Metalink for some clue, but I was not able to find anything suggestive.

This code generated the error:

BEGIN pkg_cad_eqpto.insere_eqpto( '2', 'prm - safs2', '192.252.250.090', '', '', '23/07/2003', '1', 'PGR/SAFS', '46', '1342', '921', '1342', '', '', '1', '3', '165', :id_eqpto, :cod_erro, :msg_erro); END;

CREATE OR REPLACE PACKAGE BODY "API_EQUIP"."PKG_CAD_EQPTO" is

-- ----- insere_eqpto -----------

procedure insere_eqpto
(
p_id_status number,
p_nome varchar2,
p_ip varchar2,
p_ip_nat varchar2,
p_end_no varchar2,
p_d_ativ varchar2,
p_id_tipo number,
p_sig_centro varchar2,
p_id_modelo number,
p_id_propriet number,
p_id_cliente number,
p_id_emp_manut number,
p_versao_sw varchar2,
p_obs varchar2,
p_caract varchar2,
p_finalidade varchar2,
p_login varchar2,
p_id_eqpto out number,
p_cod out number,
p_msg out varchar2
)
is

v_caract pkg_global.table_of_number:=pkg_global.table_of_number();
v_finalidade pkg_global.table_of_number:=pkg_global.table_of_number();

v_d_ativ date := to_date(p_d_ativ,'DD/MM/YYYY');
v_id_eqpto number;
v_id_op number;
v_id_eqpto_hist number;
v_cnt number;

nome_existe exception;

begin
.
.
.

Apparently, the error ocurrs in:
v_d_ativ date := to_date(p_d_ativ,'DD/MM/YYYY');

Thanks for your help.



Tom Kyte
July 23, 2003 - 9:28 pm UTC

no versions or anything -- but -- contact support for this one.

ora-01830

Tatiane, July 24, 2003 - 9:12 am UTC

I said above:

They are all 9.2.0.1.

You said above:

no versions or anything -- but -- contact support for this one.

:(

one more question

bachina, July 24, 2003 - 10:23 am UTC

i am getting the date as yyyyww i.s 200330 (year and week of the year) i want to change it to ddmmyyyy where ddmm are the first date of that week and month is the month in which that week is?
can you help me in this regard

Tom Kyte
July 24, 2003 - 4:17 pm UTC

PLEASE, test this -- here are 3 different ways - they'll all behave differently on boundary values, TEST this to make sure the one you pick is the one that works:

ops$tkyte@ORA920LAP> column d new_val D
ops$tkyte@ORA920LAP> select to_char( sysdate, 'yyyyww' ) d from dual;

D
------
200330

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select trunc(to_date( substr('&d',1,4), 'yyyy' ), 'y') from dual;
old   1: select trunc(to_date( substr('&d',1,4), 'yyyy' ), 'y') from dual
new   1: select trunc(to_date( substr('200330',1,4), 'yyyy' ), 'y') from dual

TRUNC(TO_
---------
01-JAN-03

ops$tkyte@ORA920LAP> select trunc(trunc( to_date( substr('&d',1,4), 'yyyy' ),'y') + (substr('&d',5)-1)*7,'w') from dual;
old   1: select trunc(trunc( to_date( substr('&d',1,4), 'yyyy' ),'y') + (substr('&d',5)-1)*7,'w') from dual
new   1: select trunc(trunc( to_date( substr('200330',1,4), 'yyyy' ),'y') + (substr('200330',5)-1)*7,'w') from dual

TRUNC(TRU
---------
22-JUL-03

ops$tkyte@ORA920LAP> select trunc(trunc( to_date( substr('&d',1,4), 'yyyy' ),'y') + (substr('&d',5)-1)*7,'iw') from dual;
old   1: select trunc(trunc( to_date( substr('&d',1,4), 'yyyy' ),'y') + (substr('&d',5)-1)*7,'iw') from dual
new   1: select trunc(trunc( to_date( substr('200330',1,4), 'yyyy' ),'y') + (substr('200330',5)-1)*7,'iw') from dual

TRUNC(TRU
---------
21-JUL-03

ops$tkyte@ORA920LAP> select next_day(trunc( to_date( substr('&d',1,4), 'yyyy' ),'y') + (substr('&d',5)-2)*7,'sun') from dual;
old   1: select next_day(trunc( to_date( substr('&d',1,4), 'yyyy' ),'y') + (substr('&d',5)-2)*7,'sun') from dual
new   1: select next_day(trunc( to_date( substr('200330',1,4), 'yyyy' ),'y') + (substr('200330',5)-2)*7,'sun') from dual

NEXT_DAY(
---------
20-JUL-03
 

restricting the week form 1-52

srinivasa rao bachina, July 25, 2003 - 5:38 am UTC

Hi Tom,
Thanks for quick reply,

my req will suit with

select trunc(trunc( to_date( substr('&d',1,4), 'yyyy'
),'y') + (substr('&d',5)-1)*7,'iw') from dual;

and one more constraint in that is the week should be in bet 1-52.
if we give 209972 it should give the error,but is returning 10/5/2100
can you please help me in this case also?

Tom Kyte
July 25, 2003 - 7:09 am UTC

add a CASE or DECODE statement in there to do that


case when substr( '&d',5) between 1 and 52 then trunc(trunc(.....)
else to_date( '99999999', 'mmddyyyy' )
end

for example

A reader, August 05, 2003 - 3:24 pm UTC


current_timestamp

pasko, November 10, 2003 - 3:40 pm UTC

Hi Tom,

thanks for a Great thread above..
I have a similar problem , whereby a coleague of mine had coded something like this:

------
select to_char( current_timestamp , 'YYYY-MM-DD HH24:MI:SS.FF') into l_local_variable
from dual ;
------
First of all, why do you discourage selecting from Dual, like my colleague did above, because
I have read in your site that ,
the above should be coded as follows in the Declaration Section:
------
--i want Timestamp as a String because this will be padded --with some other Characters later:

l_local_variable varchar2(32) :=
to_char( current_timestamp , 'YYYY-MM-DD HH24:MI:SS.FF') ;
-----

So , whats the trouble with the selecting from Dual ?

--
But my Real problem is that there are different clients which are connecting to Oracle 9iR2 Database from different machines, all located also in Germany...
and we have observed that , this function stores wrong Timestamps..sometimes 1 Hour ahead.

So i read the Docs and told him that current_timestamp is also influenced by session or Database Time zones and that he should have used systimestamp instead.

I tried to test by connecting through all the Clients locally using sqlplus , but i couldn't reproduce the wrong timestamps.

So , we were wondering where the this error could be coming from.

If the client Time-Zones are not set, how does this function behave ?
Does it take the dbtimezone instead?

Actually , after we get the timestamp value , we have to further convert it to GMT , taking into account the daylight savings...

I have searched for some Time-Zone Names from the View :
v$timezone_names , but could not find the one suitable for Germany.

In JAVA, i had once used Time-Zone 'Europe/Berlin', but this is not listed in the View.

Do you have any idea as to what Time-Zone should be used in Germany ?

I want to later use the Function : FROM_TZ to convert my Timestamps to GMT.

I hope you will consider my Question.

Thanks in advance.

With Best Regards.





















Tom Kyte
November 10, 2003 - 4:01 pm UTC

when a direct assignment would work:

  x := y;

why would you confuse the issue by selecting it?

  select y into x from dual;

when:

  x := y;

is what you want -- that is what you should code.



My server is in EST, my client is faking to be in PST:

[tkyte@tkyte-pc tkyte]$ echo $TZ
PST
[tkyte@tkyte-pc tkyte]$ sqlplus scott/tiger@ora920
 
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Nov 10 20:57:18 2003
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
 
scott@ORA920PC> select current_timestamp, systimestamp from dual;
 
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
SYSTIMESTAMP
---------------------------------------------------------------------------
10-NOV-03 08.57.22.615300 PM +00:00
10-NOV-03 03.57.22.615241 PM -05:00
 
 
scott@ORA920PC> select to_char( current_timestamp , 'YYYY-MM-DD HH24:MI:SS.FF'),
  2   to_char( systimestamp, 'YYYY-MM-DD HH24:MI:SS.FF') from dual;
 
TO_CHAR(CURRENT_TIMESTAMP,'YY TO_CHAR(SYSTIMESTAMP,'YYYY-MM
----------------------------- -----------------------------
2003-11-10 20:57:42.347983    2003-11-10 15:57:42.347958


See the differences -- BUT -- if i use a direct connect (fork/exec the dedicated server), it picks up my TZ:


[tkyte@tkyte-pc tkyte]$ echo $TZ
PST
[tkyte@tkyte-pc tkyte]$ plus
 
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Nov 10 20:58:30 2003
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
 
ops$tkyte@ORA920PC> select dbtimezone from dual;
 
DBTIME
------
-05:00
 
ops$tkyte@ORA920PC> select current_timestamp, systimestamp from dual;
 
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
SYSTIMESTAMP
---------------------------------------------------------------------------
10-NOV-03 08.58.49.140554 PM +00:00
10-NOV-03 08.58.49.140502 PM +00:00
 
<b>so, make sure you use a dedicated server with the "right" TZ when you are testing</b>


not sure what TZ you want to use in germany -- do the 3 character codes look familar?  do you use daylight savings (if not, the Etc/GMT-n ones look good)
 

what if the $TZ is not set

pasko, November 11, 2003 - 7:38 am UTC

Hi Tom,

Thank you very much for the above Response..

Actually, i also wanted to know how would the current_timestamp behave when my  $TZ is not set .

Would it then consider the dbtimezone ?

I note  that my dbtimezone is false :  it should be +01:00

SQL> select sessiontimezone , dbtimezone from dual ;

SESSIONTIMEZONE
---------------------------------------------------------------------------
DBTIME
------
+01:00
+02:00

In Germany ,  we use Time-Zone  MEZ.

Some machines have :
CET for Central European Time or
MET for Mid-European Time

Example :

--Our $TZ is not set 
SQL> host echo $TZ


SQL> host date 
Tue Nov 11 13:22:53 MET 2003

--in another machine,a few minutes later ,
SQL> host date
Tue Nov 11 13:26:18 CET 2003

SQL> 


Another question , is that, would the Daylight Savings Time be taken into account , if i set the DBTIMEZONE explicitly with the alter database command ?

I have seen the View v$timezone_names has 'MET' and 'CET' in it , but i am not sure if this will take into account the Daylight Savings Time and also not sure which one to use if i want to set the dbtimezone for Germany.

Thanks in advance.

Regards.


 

Tom Kyte
November 11, 2003 - 8:07 am UTC

we get it from the OS -- whatever the OS reports back to us as the time, thats the time.

for dst to work, you use a timezone setting that obeys dst, then it just happens, you need not set anything.

sorry - i'm not a "timezone expert" at all -- don't follow all of the rules and such. if MET or CET automagically flips times on your computers OS clock, they would be what you want. You might want to talk to someone who understands the nuances of timezones in your area of the world.

Date format picture ends before converting

Jamil, January 13, 2004 - 6:00 am UTC

Dear Sir
I am getting this message when I run my report

REP-0613: value does not match mask DD/MM/YYYY
ORA-01830: Date format picture ends before converting entries input string

Please tell me how can I solve this problem


Best regards

Jamil


Tom Kyte
January 13, 2004 - 5:37 pm UTC

make sure your date format picture does not end before converting the string...

it means your input string -- is not in the format dd/mm/yyyy

ops$tkyte@ORA9IR2> select to_date( '01/01/2004 12', 'dd/mm/yyyy' ) from dual;
select to_date( '01/01/2004 12', 'dd/mm/yyyy' ) from dual
                *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string


(EG: you have bad data) 

time zone question

James Su, February 09, 2004 - 5:43 pm UTC

hi Tom,

I am using Oracle 9i, and I have some date type columns (not timestamp type) storing GMT datetimes. My users log on from different time zones, how can I provide a view for them to see these columns displayed as their local time(also date type,not timestamp)? Daylight savings should be taken in account. I am thinking of adding a time zone column to my user table and assign it to a package value when a user logs on. What kind of data should I store in this time zone column?

Thank you!

Tom Kyte
February 09, 2004 - 8:40 pm UTC

check out
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:5011677391274#8218209147129 <code>

(just a tad UP in this page in fact :)

You can use that to do the conversions.

solution in 9i

James Su, February 11, 2004 - 3:14 pm UTC

CREATE OR REPLACE FUNCTION to_localtime(
p_date in date, -- GMT datetime
p_tzr in varchar2 -- timezone name
) return date -- local datetime
AS
v_localts TIMESTAMP WITH TIME ZONE;
BEGIN
v_localts:= FROM_TZ(CAST(p_date AS TIMESTAMP),'Greenwich') AT TIME ZONE p_tzr;

return to_date(to_char(v_localts,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS');
END to_local;
/

hi Tom,

the package is great, but table timedate_locales$ does not
contain a complete list timezone names as V$TIMEZONE_NAMES.TZNAME. since I am using 9i, I wrote a function for conversion.


9i date conversion

A reader, April 20, 2004 - 4:42 am UTC

Hi Tom,

sorry - I am a little confused, I should be able to work this out from the this thread.

what is the simplist generic way in 9i to convert a date that is stored in UTC to a nominated date in a different timezone? My dates are stored as dates and not with timezones.

date1 is 07-Apr-2004 23:00:00

I would like to apply for example Australian Eastern Standard Time (+10) to it and the new date should be 08-Apr-2004. Or Eastern Daylight Saving Time (-4) would be still 07-Apr-2004.







Tom Kyte
April 20, 2004 - 8:43 am UTC

read about new_time(). builtin function. limited timezone support, but it might be enough.

if it is not enough, search for timedate.zip on this page for a plsql package we wrote to provide the support.

Mark, April 21, 2004 - 5:08 pm UTC

Tom,  I have two questions on Timestamp w/ Timezone

First:  Here is my env.

Win 2000  Oracle 9.2.0.4

SQL> select * from NLS_SESSION_PARAMETERS;

PARAMETER                      VALUE
--------------------------- -----------------------------
NLS_LANGUAGE                AMERICAN
NLS_TERRITORY               AMERICA
NLS_CURRENCY                $
NLS_ISO_CURRENCY            AMERICA
NLS_NUMERIC_CHARACTERS      .,
NLS_CALENDAR                GREGORIAN
NLS_DATE_FORMAT             DD-MON-RR
NLS_DATE_LANGUAGE           AMERICAN
NLS_SORT                    BINARY
NLS_TIME_FORMAT             HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT        DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT          HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT     DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY           $
NLS_COMP                    BINARY
NLS_LENGTH_SEMANTICS        BYTE
NLS_NCHAR_CONV_EXCP         FALSE

17 rows selected.

1)  how do you do date math without losing Timezone
i.e

SQL> DESC TEST_TIME;
 Name                Type
 ------------------- ----------------------------
 TZ                  VARCHAR2(25)
 NEWDATE             TIMESTAMP(6) WITH TIME ZONE


select to_char(newdate,'DD-MON-YYYY HH24:MI:SS TZD') from test_time;


TO_CHAR(NEWDATE,'DD-MON-YYY
---------------------------
21-APR-2004 10:18:56 EDT
21-APR-2004 09:19:12 CDT
21-APR-2004 08:19:44 MDT
21-APR-2004 07:19:58 PDT

SQL> insert into test_time (select TZ, newdate + 1 from test_time);

4 rows created.


SQL> select to_char(newdate,'DD-MON-YYYY HH24:MI:SS TZD') from test_time;

TO_CHAR(NEWDATE,'DD-MON-YYY
---------------------------
21-APR-2004 10:18:56 EDT
21-APR-2004 09:19:12 CDT
21-APR-2004 08:19:44 MDT
21-APR-2004 07:19:58 PDT
22-APR-2004 10:18:56
22-APR-2004 09:19:12
22-APR-2004 08:19:44
22-APR-2004 07:19:58

-- The four new records lost their timezone


2) When selecting a column of "timestamp with time zone"
Do you have to use a to char, or is there a defaul format like for date.


SQL> DESC TEST_TIME_OLD
 Name                Type
 ------------------------------------------------
 TZ                  VARCHAR2(25)
 OLD_DATE            DATE

SQL> SELECT OLD_DATE FROM TEST_TIME_OLD;

OLD_DATE
---------
21-APR-04

SQL> SELECT NEWDATE FROM TEST_TIME;
ERROR:
ORA-01877: string is too long for internal buffer


Thanks 

Tom Kyte
April 21, 2004 - 9:26 pm UTC

when you run this, what happens

ops$tkyte@ORA9IR2> create table t ( tz varchar2(25), newdate timestamp(6) with time zone );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t
  2  select 'xx', localtimestamp
  3    from dual;
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select to_char(newdate,'DD-MON-YYYY HH24:MI:SS TZD') from t;
 
TO_CHAR(NEWDATE,'DD-MON-YYY
---------------------------
21-APR-2004 21:27:54 EDT
 
ops$tkyte@ORA9IR2> insert into t select tz, newdate+1 from t;
 
1 row created.
 
ops$tkyte@ORA9IR2> select to_char(newdate,'DD-MON-YYYY HH24:MI:SS TZD') from t;
 
TO_CHAR(NEWDATE,'DD-MON-YYY
---------------------------
21-APR-2004 21:27:54 EDT
22-APR-2004 21:27:54 EDT
 

Mark, April 26, 2004 - 11:43 am UTC

That worked:

SQL> create table t ( tz varchar2(25), newdate timestamp(6) with 
  2  time zone );

Table created.

SQL>  insert into t
  2  select 'xx', localtimestamp
  3  from dual;

1 row created.

SQL> select to_char(newdate,'DD-MON-YYYY HH24:MI:SS TZD') from t;

TO_CHAR(NEWDATE,'DD-MON-YYY
---------------------------
26-APR-2004 10:26:07 CDT

SQL> insert into t select tz, newdate+1 from t;

1 row created.

SQL> select to_char(newdate,'DD-MON-YYYY HH24:MI:SS TZD') from t;

TO_CHAR(NEWDATE,'DD-MON-YYY
---------------------------
26-APR-2004 10:26:07 CDT
27-APR-2004 10:26:07 CDT


Not sure what the problem was before.  Another thing you could clear up for me is the TZ formating.  Take a look at this:

  1  declare
  2  v_tz1    timestamp with time zone;
  3  v_tz2    timestamp with time zone;
  4  v_tz3    timestamp with time zone;
  5  v_str1  varchar2(100);
  6  v_str2  varchar2(100);
  7  v_x     varchar2(100) := 'DD-MON-YYYY HH24:MI:SS';
  8  begin
  9  -- CREATE THREE DIFF OBJ WITH THE SAME DATE,TIME AND TIMEZONE
 10  v_tz1 := to_timestamp_tz(to_char(sysdate,v_x)||' US/Central',v_x||' TZR');
 11  v_tz2 := to_timestamp_tz(to_char(sysdate,v_x)||' CDT',v_x||' TZD');
 12  v_tz3 := to_timestamp_tz(to_char(sysdate,v_x)||' -05:00',v_x||' TZH:TZM');
 13  -- DISPLAY THE OBJECTS IN DIFFERENT WAYS
 14  dbms_output.put_line('DATA FOR TZ1 Created using US/Central');
 15  dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz1,'TZR'));
 16  dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz1,'TZD'));
 17  dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz1,'TZH:TZM'));
 18  dbms_output.put_line('-----------------------------------------------');
 19  dbms_output.put_line('DATA FOR TZ2 Created using CDT');
 20  dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz2,'TZR'));
 21  dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz2,'TZD'));
 22  dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz2,'TZH:TZM'));
 23  dbms_output.put_line('-----------------------------------------------');
 24  dbms_output.put_line('DATA FOR TZ3 Created using -05:00');
 25  dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz3,'TZR'));
 26  dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz3,'TZD'));
 27  dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz3,'TZH:TZM'));
 28* end;
SQL> /
DATA FOR TZ1 Created using US/Central
Time Zone = US/CENTRAL
Time Zone = CDT
Time Zone = -05:00
-----------------------------------------------
DATA FOR TZ2 Created using CDT
Time Zone = US/CENTRAL
Time Zone = CDT
Time Zone = -05:00
-----------------------------------------------
DATA FOR TZ3 Created using -05:00
Time Zone = -05:00
Time Zone =
Time Zone = -05:00


Can you help me understand why I can't pull the TZName 'US/Central' from a "Timestamp with time zone" when it is created using TZH:TZM?  

Thanks
(DB 9.2.0.4)
 

I'm Sorry

Mark, April 26, 2004 - 12:03 pm UTC

Tom, Please disregard the above.  It was from a 9.2.0.1 Database.

My 9.2.0.4 and 9.2.05 Databases both fail your test.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL> create table t ( tz varchar2(25), newdate timestamp(6) with 
  2  time zone );

Table created.

SQL> 
SQL>  insert into t
  2  select 'xx', localtimestamp
  3  from dual;

1 row created.

SQL> 
SQL> 
SQL> select to_char(newdate,'DD-MON-YYYY HH24:MI:SS TZD') from t;

TO_CHAR(NEWDATE,'DD-MON-YYY
---------------------------
26-APR-2004 11:00:18

SQL> 
SQL> 
SQL> insert into t select tz, newdate+1 from t;

1 row created.

SQL> 
SQL> 
SQL> select to_char(newdate,'DD-MON-YYYY HH24:MI:SS TZD') from t;

TO_CHAR(NEWDATE,'DD-MON-YYY
---------------------------
26-APR-2004 11:00:18
27-APR-2004 11:00:18

Also the test for TO_CHAR give the following on both.

SQL> declare
  2  v_tz1    timestamp with time zone;
  3  v_tz2    timestamp with time zone;
  4  v_tz3    timestamp with time zone;
  5  v_str1  varchar2(100);
  6  v_str2  varchar2(100);
  7  v_x     varchar2(100) := 'DD-MON-YYYY HH24:MI:SS';
  8  begin
  9  -- CREATE THREE DIFF OBJ WITH THE SAME DATE,TIME AND TIMEZONE
 10  v_tz1 := to_timestamp_tz(to_char(sysdate,v_x)||' US/Central',v_x||' TZR');
 11  v_tz2 := to_timestamp_tz(to_char(sysdate,v_x)||' CDT',v_x||' TZD');
 12  v_tz3 := to_timestamp_tz(to_char(sysdate,v_x)||' -05:00',v_x||' TZH:TZM');
 13  -- DISPLAY THE OBJECTS IN DIFFERENT WAYS
 14  dbms_output.put_line('DATA FOR TZ1 Created using US/Central');
 15  dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz1,'TZR'));
 16  dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz1,'TZD'));
 17  dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz1,'TZH:TZM'));
 18  dbms_output.put_line('-----------------------------------------------');
 19  dbms_output.put_line('DATA FOR TZ2 Created using CDT');
 20  dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz2,'TZR'));
 21  dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz2,'TZD'));
 22  dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz2,'TZH:TZM'));
 23  dbms_output.put_line('-----------------------------------------------');
 24  dbms_output.put_line('DATA FOR TZ3 Created using -05:00');
 25  dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz3,'TZR'));
 26  dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz3,'TZD'));
 27  dbms_output.put_line('Time Zone = '||TO_CHAR(v_tz3,'TZH:TZM'));
 28  end;
 29  /
DATA FOR TZ1 Created using US/Central
Time Zone = US/CENTRAL
Time Zone = CDT
Time Zone = -05:00
-----------------------------------------------
DATA FOR TZ2 Created using CDT
Time Zone = -05:00
Time Zone =
Time Zone = -05:00
-----------------------------------------------
DATA FOR TZ3 Created using -05:00
Time Zone = -05:00
Time Zone =
Time Zone = -05:00

PL/SQL procedure successfully completed.

Again here are my NLS_SESSION_PARAMETERS

SQL> select * from NLS_SESSION_PARAMETERS;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE


Thanks for your help 

Tom Kyte
April 26, 2004 - 2:24 pm UTC

i won't be able to look at this for a while (traveling in iceland, don't have an answer off of the top of my head, not able to reproduce in my 9204 instance so it is not the release in as much as something different between your 3 instances. compare them and see what you see.

Randall, April 28, 2004 - 11:48 am UTC

Tom,  Maybe I am missing something, but this looks like a bug.

Using the TZNAME from V$TIMEZONE_NAMES and the TZ_OFFSET function.

Offset for 'US/Pacific' correct..
SQL> select TZ_OFFSET('US/Pacific') FROM DUAL;

TZ_OFFS
-------
-07:00

Offset for 'Asia/Hong_Kong' correct..
SQL> select TZ_OFFSET('Asia/Hong_Kong') FROM DUAL;

TZ_OFFS
-------
+08:00


But using the TZNAME 'ETC/GMT+8' returns the following:
SQL> select TZ_OFFSET('ETC/GMT+8') FROM DUAL;

TZ_OFFS
-------
-08:00

SQL> select TZ_OFFSET('ETC/GMT-8') FROM DUAL;

TZ_OFFS
-------
+08:00

Is this wrong or am I missing something.

Database is 9.2.0.4

Thanks
 

Tom Kyte
April 28, 2004 - 7:06 pm UTC

i don't know, I don't know what "etc/gmt+8" means personally.  sorry....

not a timezone expert.  I just look at the clocks when I land at the airport and change my watch :)

according to

http://www.gsp.com/support/virtual/admin/unix/tz/gmt/

I'm not so sure.  At 19:00pm EDT on April 28, that page said 

Etc/GMT+8      Wed-28-Apr-2004      15:00:38      GMT+8       
Etc/GMT-0      Wed-28-Apr-2004      23:00:38      GMT  
Etc/GMT-8      Thu-29-Apr-2004      07:00:38      GMT-8 

so, GMT+8 was 8 hours BEFORE gmt and GMT-8 was 8 hours *after* gmt.  That would be -8 and +8 repectively...  the database agreed:


  1* alter session set time_zone ='ETC/GMT-8'
ops$tkyte@ORA9IR2> /
 
Session altered.
 
ops$tkyte@ORA9IR2> select localtimestamp from dual;
 
LOCALTIMESTAMP
------------------------------------------------------------------------
29-APR-04 07.04.16.913456 AM
 
ops$tkyte@ORA9IR2> alter session set time_zone = 'GMT';
 
Session altered.
 
ops$tkyte@ORA9IR2> select localtimestamp from dual;
 
LOCALTIMESTAMP
------------------------------------------------------------------------
28-APR-04 11.04.47.495581 PM
 
ops$tkyte@ORA9IR2> alter session set time_zone = 'ETC/GMT+8';
 
Session altered.
 
ops$tkyte@ORA9IR2> select localtimestamp from dual;
 
LOCALTIMESTAMP
------------------------------------------------------------------------
28-APR-04 03.05.25.597151 PM
 


 

Same issue as Mark, can't get TZR or TZD to display

Justis, May 27, 2004 - 5:27 pm UTC

I am seeing a similar issue to Mark where using a date format of TZD or TZR does not display the correct format string.

HP-UX B.11.00 U 9000/800

SQL*Plus: Release 9.2.0.1.0 - Production on Thu May 27 13:26:32 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> select * from nls_session_parameters
  2  /

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

17 rows selected.

SQL> create table time01(
  2    d date,
  3    ts timestamp,
  4    tstz timestamp with time zone,
  5    tsltz timestamp with local time zone
  6  )
  7  /

Table created.

SQL> insert into time01 values( sysdate, systimestamp, systimestamp, systimestamp )
  2  /

1 row created.

SQL> column ts format a35
SQL> column tstz format a35
SQL> column tsltz format a35
SQL> select * from time01
  2  /

D
---------
TS
-----------------------------------
TSTZ
-----------------------------------
TSLTZ
-----------------------------------
27-MAY-04
27-MAY-04 01.54.06.271429 PM
27-MAY-04 01.54.06.271429 PM -07:00
27-MAY-04 01.54.06.271429 PM


SQL> drop table time01
  2  /

Table dropped.

SQL> select to_char(systimestamp,'tzd  tzr tzh:tzm') from dual
  2  /

TO_CHAR(SYSTIMESTAMP,'TZDTZRTZH:TZM')
-----------------------------------------------
  -07:00 -07:00
 

Tom Kyte
May 27, 2004 - 8:53 pm UTC

i hate timezones but -- what where you expecting to see?


Expectations

Justis, May 28, 2004 - 6:31 pm UTC

Oracle Docs:
<quote>
TZD Yes Daylight savings information. The TZD value is an abbreviated time zone string with daylight savings information. It must correspond with the region specified in TZR.

Example: PST (for US/Pacific standard time); PDT (for US/Pacific daylight time).

TZR Yes Time zone region information. The value must be one of the time zone regions supported in the database.

Example: US/Pacific
</quote>

According to the documentation, I guess I was expecting to see PST for TZD and US/Pacific or America/Los Angeles or something like that for TZR. Perhaps those format strings are only used for converting a literal to a timestamp with timezone. No business problem here, just trying to gain knowledge.


Tom Kyte
May 28, 2004 - 8:13 pm UTC

but you have offsets from GMT, not timezones -- you don't seem to be using timezones, but offsets?

-5:00 - what is that? East Coast time or parts of Indiana during the winter?



Resolved

Justis, May 28, 2004 - 7:16 pm UTC

After looking through the documentation a bit more I came across

<quote>
The ambiguity arises because the time zone numerical offset is provided in the expression, and that numerical offset may map to more than one time zone region.
</quote>

which explains why the time zone names can't be displayed. I think this applies to Mark's case above also.

Tom Kyte
May 28, 2004 - 8:16 pm UTC

(and i didn't even read this before commenting above... :)

TIMESTAMP WITH LOCAL TIME ZONE

Raju, November 09, 2004 - 11:46 pm UTC

Tom,

is "TIMESTAMP WITH LOCAL TIME ZONE" data type
useful only for client server applications..? because in web applications, as clients(from the browser) don't make direct database calls, only the application server makes the database calls, so we always get the datetime of application server's timezone.

please correct if i am wrong..

thanks as always.

Tom Kyte
November 10, 2004 - 7:04 am UTC

well, there are alter sessions so that clients connected to a middle tier could have their local timezone set for them (eg: your application says "oh, it is tom, tom lives on the east coast, set tom's timezone to X" -- and laster it says "oh, it is mary, she lives on the west coast......")

so, no, I don't think it is only useful in client server.

Time in milliseconds

Patrick, November 18, 2004 - 5:06 pm UTC

Thanks Tom for your help, this web site is the best.

And now the question.

We are using Oracle with java and we want to keep the GMT time in the database. I know that we can use timestamp with timezone to keep specific time from all around the world, but is there an easy way with Oracle to convert time in milliseconds in a readable format.

(In java, the start point of the time in milliseconds is 01-JAN-1970)

For example:

to_date( timeInMilliSec, 'YYYY-MM-DD HH24:MI:SS' ) or another function.

Tom Kyte
November 19, 2004 - 10:02 am UTC

why would you want to do something so wrong?

the timestamp data type stores the data with the precision you need, please use the correct and proper datatype.

Client vs server time

A reader, April 05, 2005 - 11:31 am UTC

So if a user in California connects to a Oracle database in NY and does

select localtimetamp,systimestamp,sysdate from dual;

Shoudnt the first one (localtimestamp) be 3 hours behind the other 2? Thats not what I see.

What am I missing? Thanks

Tom Kyte
April 05, 2005 - 12:34 pm UTC

they should see the setting their client set for local, the setting for the database from the database.


I'm in central right now, my database in eastern.
but unless I actually setup my client and db timezones, oracle doesn't know that.

A reader, April 05, 2005 - 1:07 pm UTC

Not sure what you mean by setting. What exactly should I set where?

Insert timestamp

Maverick, June 09, 2005 - 3:16 pm UTC

Tom, I am trying to insert a timestamp value(with milli seconds) into a timestamp column and i'm getting error message

How do i insert timestamp with this kind of input?

myschema@myDB10G> create table test(mytime timestamp);

Table created.

myschema@myDB10G> insert into test values
2 ('09-Jun-2005 12:34:45.56');

1 row created.

myschema@myDB10G> insert into test values (to_date('06/09/2005 12:34:45.56','mm/dd/yyyy hh:mi:ss.ff
'));
insert into test values (to_date('06/09/2005 12:34:45.56','mm/dd/yyyy hh:mi:ss.ff'))
*
ERROR at line 1:
ORA-01821: date format not recognized

My NLS_PARAMETER Setting for timestamp
NLS_TIMESTAMP_FORMAT --> DD-MON-RR HH.MI.SSXFF AM
Thanks,

Tom Kyte
June 09, 2005 - 6:36 pm UTC

to timestamp


tkyte@ORA9IR2W> select to_date('06/09/2005
2 12:34:45.56','mm/dd/yyyy hh:mi:ss.ff
3 ') from dual;
12:34:45.56','mm/dd/yyyy hh:mi:ss.ff
*
ERROR at line 2:
ORA-01821: date format not recognized


tkyte@ORA9IR2W> 1
1* select to_date('06/09/2005
tkyte@ORA9IR2W> c/date/timestamp
1* select to_timestamp('06/09/2005
tkyte@ORA9IR2W> l
1 select to_timestamp('06/09/2005
2 12:34:45.56','mm/dd/yyyy hh:mi:ss.ff
3* ') from dual
tkyte@ORA9IR2W> /

TO_TIMESTAMP('06/09/200512:34:45.56','MM/DD/YYYYHH:MI:SS.FF')
-----------------------------------------------------------------
09-JUN-05 12.34.45.560000000 PM


That's Great

Maverick, June 09, 2005 - 9:39 pm UTC

Thanks Tom. This I never knew. You are "the Oracle GURU".

Thanks Again.

But pls. clarify me

A reader, June 23, 2005 - 1:24 pm UTC

Hi Tom,

I create a table on the database with the following :
create table test3
(
col1 timestamp with time zone,
col2 timestamp with local time zone
)

insert into test3 values (systimestamp,systimestamp)

I set the following on my TOAD session (client).

alter session set time_zone = 'Asia/Calcutta'
select dbtimezone,sessiontimezone from dual
Dbtime Session Time
-04:00 Asia/Calcutta

I run the following SELECT

select * from test3

Col1 Col2
6/23/2005 1:11:42.095137 PM -04:00 6/23/2005 1:11:42.095137 PM

I am seeing the same values in both columns, only difference being -4.00. My understanding was that Col2 should get the data in the session timezone, but is not happening. Pls. explain why this is happening. I am using 9.2.0.6.0



Tom Kyte
June 23, 2005 - 7:02 pm UTC

in sqlplus, what do you see.


ops$tkyte@ORA10GR1> create table t
  2  (
  3    col1 timestamp with time zone,
  4    col2 timestamp with local time zone
  5  )
  6  /
 
Table created.
 
ops$tkyte@ORA10GR1> insert into t values (systimestamp,systimestamp);
 
1 row created.
 
ops$tkyte@ORA10GR1> select * from t;
 
COL1
---------------------------------------------------------------------------
COL2
---------------------------------------------------------------------------
23-JUN-05 07.02.24.106003 PM -04:00
24-JUN-05 04.32.24.106003 AM
 
 
ops$tkyte@ORA10GR1> select dbtimezone,sessiontimezone from dual;
 
DBTIME
------
SESSIONTIMEZONE
---------------------------------------------------------------------------
+00:00
Asia/Calcutta
 
 
ops$tkyte@ORA10GR1> alter session set time_zone = 'Asia/Calcutta';
 
Session altered.
 
ops$tkyte@ORA10GR1> select dbtimezone,sessiontimezone from dual;
 
DBTIME
------
SESSIONTIMEZONE
---------------------------------------------------------------------------
+00:00
Asia/Calcutta
 
 
ops$tkyte@ORA10GR1> select * from t;
 
COL1
---------------------------------------------------------------------------
COL2
---------------------------------------------------------------------------
23-JUN-05 07.02.24.106003 PM -04:00
24-JUN-05 04.32.24.106003 AM
 
 
ops$tkyte@ORA10GR1>

 

difference between 2 timestamp columns

A reader, November 11, 2005 - 12:02 pm UTC

Hi Tom,

How can I get the difference(in seconds) between 2 timestamp columns located in a table ?



Tom Kyte
November 12, 2005 - 8:43 am UTC

<quote src=expert oracle database architecture>

Getting the Difference Between Two TIMESTAMPs

This is where the DATE and TIMESTAMP types diverge significantly. Whereas the results of subtracting a DATE from a DATE was a NUMBER, the result of doing the same to a TIMESTAMP is an INTERVAL:

ops$tkyte@ORA10G> select dt2-dt1
  2    from (select to_timestamp('29-feb-2000 01:02:03.122000',
  3                              'dd-mon-yyyy hh24:mi:ss.ff') dt1,
  4                 to_timestamp('15-mar-2001 11:22:33.000000',
  5                                           'dd-mon-yyyy hh24:mi:ss.ff') dt2
  6            from dual )
  7  /
 
DT2-DT1
---------------------------------------------------------------------------
+000000380 10:20:29.878000000

The difference between two TIMESTAMP values is an INTERVAL, and this shows us the number of days and hours/minutes/seconds between the two. If we desire to have the years, months, and so forth, we are back to using a query similar to the one we used with dates:

ops$tkyte@ORA10G> select numtoyminterval
  2         (months_between(dt2,dt1),'month')
  3             years_months,
  4         dt2-add_months(dt1,trunc(months_between(dt2,dt1)))
  5                 days_hours
  6    from (select to_timestamp('29-feb-2000 01:02:03.122000',
  7                              'dd-mon-yyyy hh24:mi:ss.ff') dt1,
  8                 to_timestamp('15-mar-2001 11:22:33.000000',
  9                              'dd-mon-yyyy hh24:mi:ss.ff') dt2
 10            from dual )
 11  /
 
YEARS_MONTHS  DAYS_HOURS
------------- -----------------------------
+000000001-00 +000000015 10:20:30.000000000
 

A reader, November 14, 2005 - 9:58 am UTC

Thanks

Confusion with RR Format

Yuan, December 09, 2005 - 8:32 am UTC

The behavior for the RR mask seems to differ between date and timestamp data types:

SQL> select to_date('1-DEC-2005', 'DD-MON-RR HH'), to_timestamp('1-DEC-2005', 'DD-MON-RR HH')
  2  from dual;

TO_DATE('
---------
TO_TIMESTAMP('1-DEC-2005','DD-MON-RRHH')
---------------------------------------------------------------------------
01-DEC-05
01-DEC-20 05.00.00.000000000 AM 

Tom Kyte
December 09, 2005 - 12:57 pm UTC

agreed, please contact support with this simple test case.

DBTIMEZONE vs SESSIONTIMEZONE

Russell, January 19, 2006 - 11:08 pm UTC

Hi Tom,

We are using an Oracle 9.2.0.5 database on a HP UNIX 11 box.

The server is physically located in Sydney Australia. I am physically located in Melbourne Australia. Sydney and Melbourne are both in the same timezone (EST + 10:00), and both have daylight saving from end of October to end of March (EDST + 11:00). We telnet into the Unix box, and use SQLPLUS from there.

We have the situation where the majority of data imported into our database is entered in local time to the user (Australia has at least 5 timezones). A small percentage of our data is entered in "Database" time based on the fact that the database where information is gathered from is also in Melbourne.

Last year using information found on Ask Tom, I put together the necessary code, to convert the Database time to local time (whatever that is). The portion of the query I am using at the moment to convert is:

FROM_TZ(CAST( DATECREATED AS TIMESTAMP), DBTIMEZONE) AT TIME ZONE REAL_TZ

This query is converting times from DBTIMEZONE to what the timezone should be (where datecreated, the field being converted, is an Oracle date type, and REAL_TZ is derived from a lookup table, converting from the Application Timezone names to the Internal Oracle timezones. (This bit works ok).


One of our users noticed there were conflicts in timezone calculations from Brisbane (also on the Eastern coast of Australia, but no Daylight Savings, so at the moment they are +10:00).

Issuing the following statement at the moment on my database

SELECT DBTIMEZONE, SESSIONTIMEZONE from dual;
Returns
+10:00 +11:00

1) Shouldn't these two display the same number given the information above?

2) If a database is created with a timezone in Australian EST, will that database always be in Australian EST (hence DBTIMEZONE figure above), even if the database's physical location experiences daylight savings, and the UNIX server's time displayed changes with Daylight saving

3) If q2 above is yes, than should i use SESSIONTIMEZONE in my calculations, to display Daylight savings (or lack thereof in Brisbane)?

4) How would I find out what timezone the database had been created in (or the current timezone name), if for half of the year, both Australia/Brisbane and Australia/Sydney timezones would have shown +10:00, and now the database only shows the numerical offset in SQLPLUS?

Thanks for your time

Russell




Tom Kyte
January 20, 2006 - 10:10 am UTC

ops$tkyte@ORA10GR2> SELECT DBTIMEZONE, SESSIONTIMEZONE from dual;
 
DBTIME
------
SESSIONTIMEZONE
---------------------------------------------------------------------------
+00:00
-05:00


No, not in general.  My dbtimezone is UTC, my session however is currently in EST (east coast of the US EST)


the client has control over the timezone (sqlplus will pick this up from your environment)

Whether you should use sessiontimezone or dbtimezone is something you'll need to figure out.  If the goal is to show what time this event took place according to the wall clock on the end users wall - likely sessiontimezone is correct.


That is your current timezone - +10, unless you change it to one of the "string" names. 

Learnt a lot from this thread but stumped converting between timezones in SQL

Tim Onions, June 28, 2006 - 12:17 pm UTC

Having read through this thread I thought I knew enough about timezone conversions but cannot get

FROM_TZ(CAST( DATECREATED AS TIMESTAMP), DBTIMEZONE) AT TIME ZONE v_TZ

to work in SQL - it will work in PL/SQl but errors in SQL statements. Here is my test case:

This works:
select dbtimezone from dual;

DBTIMEZONE
-----------
US/Eastern

declare
l_tz varchar2(64):='America/New_York';
l_tim timestamp;
begin
l_tim:=CAST(to_date('14-nov-2004 18:24:11') AS TIMESTAMP with time zone) AT TIME ZONE l_tz;
dbms_output.put_line(l_tim);
end;
/

But this does not:
select dbtimezone from dual;

DBTIMEZONE
-----------
US/Eastern

declare
l_tz varchar2(24):='America/New_York';
begin
for r in (select FROM_TZ(CAST(to_date('01-Apr-2006 18:24:11') AS TIMESTAMP), DBTIMEZONE ) AT TIME ZONE l_tz c1
from dual) loop
dbms_output.put_line(r.c1);
end loop;
end;
/
declare
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
ORA-06512: at line 4

The error is the same on 9.2.0.7 and 10.2.0.1. I've probably missed something obvious but I've googled and metalunked and foud nothing. Any help you can afford would be appreciated.


Tom Kyte
June 28, 2006 - 4:55 pm UTC

Looks like SQL does not support a bind variable right there:


ops$tkyte@ORA9IR2> select FROM_TZ(CAST(to_date('01-Apr-2006 18:24:11','dd-mon-yyyy hh24:mi:ss')
  2         AS TIMESTAMP), DBTIMEZONE ) AT TIME ZONE 'America/New_York' c1 from dual;

C1
---------------------------------------------------------------------------
01-APR-06 06.24.11.000000 PM AMERICA/NEW_YORK

ops$tkyte@ORA9IR2> variable x varchar2(25)
ops$tkyte@ORA9IR2> exec :x := 'America/New_York';

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select FROM_TZ(CAST(to_date('01-Apr-2006 18:24:11','dd-mon-yyyy hh24:mi:ss')
  2         AS TIMESTAMP), DBTIMEZONE ) AT TIME ZONE :x c1 from dual;
       AS TIMESTAMP), DBTIMEZONE ) AT TIME ZONE :x c1 from dual
                                      *
ERROR at line 2:
ORA-00923: FROM keyword not found where expected

but it looks like it "should"

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/expressions006.htm#SQLRF00401

since "expression" is permitted... 

So not allowing expr in SQL is a bug

Tim Onions, June 29, 2006 - 12:28 pm UTC

Looks like I need to file a SR (TAR) then, aw shucks!

How to add a new timezone?

A reader, July 11, 2006 - 6:36 pm UTC

We stored different timezones in a table and passing them to from_tz functioned based on user’s timezone. One of the users using ‘Africa/Harare’ timezone and the query is failing with ORA-00904 as it is not available from v$timezone_names.

How to fix this problem? Is it possible to add new timezone info to Oracle ?



Tom Kyte
July 12, 2006 - 3:28 pm UTC

use 9i and above, full timezone support with the new timestamp with timezone datatypes.

No, you cannot add new timezones for new_tz, from_tz and such (old functions).

But comprehensive timezone support is there in 9i.

Update

A reader, July 11, 2006 - 7:40 pm UTC

This is the correct error message I got for the above problem.

ORA-01882: timezone region not found

Thanks

A reader, July 12, 2006 - 7:13 pm UTC

Thanks for your comments.

We are using 9.2.0.5 and this timezone is not available. It seems that Java and Oracle are following different standards. We are planning to change the code to map the nearest Oracle timezone.

Timezone Time Conflict

Arindam Mukherjee, August 21, 2006 - 3:39 am UTC

Respected Mr. Tom,

First off, I have read “
http://asktom.oracle.com/~tkyte/timedate.zip”
file. In the tables.sql, you did not consider any date type column.

Please Note - I am working for AMD project from India using AMD Database server located in Austin – USA.  “Dbtimezone” = -05:00 in the database and I have altered “sessiontimezone” to -08:00 by alter session. The following are my output.

SQL> ALTER SESSION SET TIME_ZONE = '-5:00';

Session altered.

SQL> SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
LOCALTIMESTAMP
---------------------------------------------------------------------------
21-AUG-06 02.07.14.315824 AM -05:00
21-AUG-06 02.07.14.315824 AM

1 row selected.

SQL> ALTER SESSION SET TIME_ZONE = '-8:00';

Session altered.

SQL>
SQL> SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
LOCALTIMESTAMP
---------------------------------------------------------------------------
20-AUG-06 11.08.36.120820 PM -08:00
20-AUG-06 11.08.36.120820 PM

1 row selected.

Dramatically date has been changed from 21 to 20 August. 

Now I have created one table as follows,
create table arin_1
(atz       timestamp with time zone,
 altz      timestamp with local time zone,
 at        timestamp,
 ad        date
)
/

insert into arin_1 values(sysdate, sysdate,sysdate,sysdate)
/
insert into arin_1 values(systimestamp, systimestamp,systimestamp,sysdate)
/
insert into arin_1 values(localtimestamp, localtimestamp,localtimestamp,sysdate)
/
insert into arin_1 values(CURRENT_TIMESTAMP, CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,sysdate)
/

Now I have created one table as follows,
create table arin_1
(atz       timestamp with time zone,
 altz      timestamp with local time zone,
 at        timestamp,
 ad        date
)
/

insert into arin_1 values(sysdate, sysdate,sysdate,sysdate)
/
insert into arin_1 values(systimestamp, systimestamp,systimestamp,sysdate)
/
insert into arin_1 values(localtimestamp, localtimestamp,localtimestamp,sysdate)
/
insert into arin_1 values(CURRENT_TIMESTAMP, CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,sysdate)
/

I could not find any difference except “timestamp with time zone” datatype where “-05:00” has been added to spice up. I executed several times those insert statements, the result seems conflicting. I am not saying it’s wrong rather I must confess I am not able to understand the usage of these two new timestamp “timestamp with time zone” and “timestamp with local time zone” as well. My question stems from “Daylight Savings support” and then I get trapped in this vicious circle. Now I request you to read Oracle 9i Database Concept chapter 12, the example where San Francisco Database is referred. I have already read another thread "daylight savings" to this site.

Hope I have got across my message to you and you please explain it.

 

Tom Kyte
August 27, 2006 - 9:07 am UTC

if you have 9i, you would not use the timezone.zip 8i plsql package to implement rudimentary timezone support - since 9i has it built in.


I don't understand this comment at all:

"Dramatically date has been changed from 21 to 20 August. "

why dramatically?  You set your offset from UTC back far enough to become "yesterday"

Here is an excerpt from my book "Expert Oracle Database Architecture" regarding this datatype:

<quote>
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 Len=13: 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.
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 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@ORA10G> create table t
  2  ( ts1  timestamp with time zone,
  3    ts2  timestamp with time zone
  4  )
  5  /
Table created.
 
ops$tkyte@ORA10G> insert into t (ts1, ts2)
  2  values ( timestamp'2005-06-05 17:02:32.212 US/Eastern',
  3           timestamp'2005-06-05 17:02:32.212 US/Pacific' );
 
1 row created.
and perform correct TIMESTAMP arithmetic on them:
ops$tkyte@ORA10G> select ts1-ts2 from t;
 
TS1-TS2
---------------------------------------------------------------------------
-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 17: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.
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> 

Logically corrupted data

A reader, September 18, 2006 - 2:50 pm UTC

Hi Tom:
Excellent thread.

Is it not possible to logically corrupt the data in the date datatype column if the schema is moved from a d/b in GMT time zone to a d/b in EST(USA) time Zone?

How should we handle this scenario?

Thanks
Ma$e

Tom Kyte
September 19, 2006 - 2:24 am UTC

no, it would not. the date would be moved as a 7 byte binary field - it would not change.

The date datatype never had a timezone associated with it, so it would not be "corrupt".

if it was 5am on Sept 19th, 2006 when you started...
it'll be 5am on Sept 19th, 2006 when you finish.

If that is not what you want, only you can describe what you really would like to have happen and then we can implement that.

Moving schema across time zones

A reader, September 19, 2006 - 1:37 pm UTC

Here what I had in mind that could lead to logical corruption.

Let's say we have a table with a date column and this existed on a d/b in the UK with the dbtimezone set to GMT.
All data that was entered in that table reflected the date and time according to GMT time zone.

Now when we move that table to a database that is set for GMT-4 (US-EST), is the date data in that table not logically corrupt? All that data is now associated with a database in the EST time zone.

True the original data and the new data never had timezone associated with it.

Unless I was aware (mentally) that this column in this table has data that originated from GMT timezone, the results I get from my sql would be incorrect. Correct?

Thanks for your assistance.

Ma$e

Tom Kyte
September 19, 2006 - 2:55 pm UTC

it is only logically corrupt if you deem it so - the 7 byte date format has no timezone, it just just a date/time.

Only if you deem it to need an update would it be "logically corrupt" and if so, you know what to do (update it!)

the results from your sql would not be incorrect, unless and until you say they are - in which case, you need to update your data to be in East Coast time.


Brad, November 19, 2006 - 12:26 am UTC

Tom,
We have date data in the following format..This is as it comes from the source system...I need to extract the time portion of it and round it to the
nearest half hour...the sample data is as shown below
CREATE TABLE test
(
START_TIME CHAR(19 BYTE) NOT NULL,
END_TIME CHAR(19 BYTE) NOT NULL
);

Insert into TEST
(START_TIME, END_TIME)
Values
('2006-11-08T15:53:52', '2006-11-08T15:53:53');
Insert into TEST
(START_TIME, END_TIME)
Values
('2006-11-08T16:51:46', '2006-11-08T16:51:56');
Insert into TEST
(START_TIME, END_TIME)
Values
('2006-11-08T16:58:23', '2006-11-08T16:58:25');
Insert into TEST
(START_TIME, END_TIME)
Values
('2006-11-08T07:05:01', '2006-11-08T07:05:01');
Insert into TEST
(START_TIME, END_TIME)
Values
('2006-11-08T07:05:01', '2006-11-08T07:05:01');
Insert into TEST
(START_TIME, END_TIME)
Values
('2006-11-08T07:05:01', '2006-11-08T07:05:01');
Insert into TEST
(START_TIME, END_TIME)
Values
('2006-11-08T07:05:01', '2006-11-08T07:05:01');
Insert into TEST
(START_TIME, END_TIME)
Values
('2006-11-10T16:05:13', '2006-11-10T16:05:52');
Insert into TEST
(START_TIME, END_TIME)
Values
('2006-11-10T16:18:34', '2006-11-10T16:18:35');
Insert into TEST
(START_TIME, END_TIME)
Values
('2006-11-10T16:11:35', '2006-11-10T16:12:05');
COMMIT;



But i need to round the data to the nearest half hour...like 1320 should be rounded to 1330 and 1310 should be rounded to 1300 ( 0-15 should be rounded to 0 and
15-30 rounded to 30..30-45 should be rounded tp 30 and 45-60 should be rounded to the next hour...)
basically i need the output data to be like this (0,30,100,130,200,230,...2330) as numbers?

select
to_char(to_date(substr(start_time,12),'hh24:mi:ss'),'hh24mi') from
test..
i got this far,....any suggestions?


Tom Kyte
November 20, 2006 - 2:43 am UTC

I have no idea why

a) you would use char, stop using that type, it is a "bad" type.
b) you would use a string to store a date.

how can

0-15 round to 0
15-30 round to 30

same with 45?

Timestamp with timezone not populated correctly

Amit, January 28, 2007 - 11:27 pm UTC

Hi,
I have the following timestamp with timezone issue.

From java program I am passing the datetime with timezone value to database (INTERVALTIME (column name)).

PrepareStatemenet statement;
..
...
java.util.Date dt = ISODateUtil.parse("2006-06-21T18:00:00+00:00");
SimpleTimeZone zone = new SimpleTimeZone(0,"IST");
Calendar cal = converting the dt to calendar;
cal.setTimeZone(zone);
statement.setTimestamp(col, new Timestamp(cal.getTimeInMillis()),cal);

In database INTERVALTIME (column name) coulmn type is TiMESTAMP WITH TIMEZONE.Database is in PDT (GMT-8) timezone.

Now the problem is :
Even if i send the datetime of IST timezone, it is displaying in PDT (GMT-8) timezone.Any clues, suggestions will be highly appriciated. In Database we are seeing date as
2006-12-02T15:00:00.000-08:00
but we are expecting "2006-12-02T15:00:00.000-00:00"

How can i resolve this issue? Tom please suggest as this is an urgent issue.

How to get timezone region from database time zone.

Shivdeep Modi, February 12, 2007 - 5:50 am UTC


Hi Tom,

How to get timezone region from database time zone?

Value should be one of the following :

select distinct(TZABBREV) from V$TIMEZONE_NAMES;

Regards,
Shivdeep Modi
Tom Kyte
February 12, 2007 - 10:37 am UTC

depends on how the database was setup, not all databases have one - some just have offsets from UTC - see dbtimezone.

how to calculate break fast,lunch and dinner time between two times

AM, April 03, 2007 - 7:00 am UTC

Hi

i have this type of data in my table

OT_DATE FROM_TIME TO_TIME FROM_ TO_TIME
10-JAN-07 01-APR-07 01-APR-07 17:30 19:30
11-JAN-07 01-APR-07 01-APR-07 17:30 19:30
12-JAN-07 01-APR-07 01-APR-07 18:30 20:30
13-JAN-07 01-APR-07 01-APR-07 08:00 17:30
14-JAN-07 01-APR-07 01-APR-07 08:00 17:30
15-JAN-07 01-APR-07 01-APR-07 17:30 19:30

I need to calculate 3 more columns breakfast ,lunch and dinner.The rule is from 07:00 to 08:00 one breakfast will be counted.from 13:00 to 14:00 one lunch will be counted
and from 20:00 to 00:00(12pm) one dinner will be counted

Against each row i need to calculate number of bfast,lunch and dinner if from_time and to_time contains any of time slots alloted for each meal then return 1 for respective column else return 0 for that column.Is it possible through sql.
Regards
Tom Kyte
April 04, 2007 - 9:44 am UTC

it is likely possible.

but we won't consider looking at it without an easy to enter example.

no create table
no insert into
no look


and a better (much better) explanation of what is represented by the data that exists - it doesn't compute. I cannot make sense of the data displayed.

eXPLANATION

A reader, April 04, 2007 - 11:49 am UTC

Sorry for not being meaningful
create table OVERTIME(
ftime VARCHAR2(5),
to_time VARCHAR2(5));

INSERT INTO OVERTIME VALUES('17:30','19:30');
INSERT INTO OVERTIME VALUES('08:00','17:30');
INSERT INTO OVERTIME VALUES('17:30','19:30')
INSERT INTO OVERTIME VALUES('06:00','10:30')
INSERT INTO OVERTIME VALUES('11:30','12:30')




Tom Kyte
April 04, 2007 - 12:06 pm UTC

that is even less meaningful?

since it added no text to explain what you meant

and it doesn't even match the first table.

Review

A reader, April 04, 2007 - 1:36 pm UTC

considering my last tabe i.e overtime,i have a table in which from_time and to_time is entered

I need to calculate 3 more columns breakfast ,lunch and dinner.The rule is from 07:00 to 08:00 one breakfast will be counted.from 13:00 to 14:00 one lunch will be counted
and from 20:00 to 00:00(12pm) one dinner will be counted
Now as the data is entered according to hours worked(from hours to to_hours),the duration may contain time slots aloted for different meals.

Against each row i need to calculate number of bfast,lunch and dinner if from_time and to_time contains any of time slots alloted for each meal then return 1 for respective column else return 0 for that column.

say if from_time is 0700 and to_time is 1500 it contain one time slot for breakfast i.e 07:00 to 08:00 and one for lunch i.e 13:00 to 14:00 so i need to display 1 in breakfast and 1 in lunch and 0 in dinner column.
Thanks for your patience
Regards

Tom Kyte
April 04, 2007 - 3:43 pm UTC

answer Michel's question below, since I would obviously have the same question.

What happens with the partial overlaps.

To Atif (A reader)

Michel Cadot, April 04, 2007 - 2:45 pm UTC


You still don't answer to my question on OraFAQ forum:

If from_time is 7:30 and to_time is 13:30, what have we to count? A breakfast, a lunch, nothing?

Regards
Michel

Answer

Atif, April 05, 2007 - 7:58 am UTC

If from_time is 7:30 and to_time is 13:30 then we will have one break fast, one lunch and zero dinner.
because 7:30 lies between 07:00 to 08:00(time slot for bfast) and 13:30 lies between 13:00 to 14:00(time slot for lunch).
Regards
Tom Kyte
April 05, 2007 - 11:12 am UTC

ops$tkyte%ORA10GR2> select ftime, to_time,
  2         case when to_date( ftime,'hh24:mi') <= to_date( '08:00', 'hh24:mi' ) and to_date(to_time,'hh24:mi') >=  to_date( '07:00', 'hh24:mi' )
  3              then 'Y'
  4              else 'N'
  5          end breakfast,
  6         case when to_date( ftime,'hh24:mi') <= to_date( '14:00', 'hh24:mi' ) and to_date(to_time,'hh24:mi') >=  to_date( '13:00', 'hh24:mi' )
  7              then 'Y'
  8              else 'N'
  9          end lunch,
 10         case when to_date( ftime,'hh24:mi') <= to_date( '23:59:59', 'hh24:mi:ss' ) and to_date(to_time,'hh24:mi') >=  to_date( '20:00', 'hh24:mi' )
 11              then 'Y'
 12              else 'N'
 13          end dinner
 14    from overtime
 15  /

FTIME TO_TI B L D
----- ----- - - -
17:30 19:30 N N N
08:00 17:30 Y Y N
17:30 19:30 N N N
06:00 10:30 Y N N
11:30 12:30 N N N
11:30 23:30 N Y Y

6 rows selected.

To share the answer I gave in OraFAQ

Michel Cadot, April 05, 2007 - 12:02 pm UTC


(The test case given by Atif was not the same one, so results are different.)

<quote>
I choose that at least one minute should be in the period to take the meal into account.
If you don't agree then change the boundaries.
SQL> select o_date, ftime, to_time,
  2         case when   ftime between '0700' and '0759' 
  3                  or to_time between '0701' and '0800' 
  4                  or ( ftime < '0700' and to_time > '0800' )
  5                then 'Y'
  6              else 'N'
  7         end breakfast,
  8         case when   ftime between '1300' and '1359' 
  9                  or to_time between '1301' and '1400' 
 10                  or ( ftime < '1300' and to_time > '1400' )
 11                then 'Y'
 12              else 'N'
 13         end lunch,
 14         case when   ftime between '2000' and '2359' 
 15                  or to_time between '2001' and '2359' 
 16                  or to_time = '0000'
 17                then 'Y'
 18              else 'N'
 19         end dinner
 20  from overtime
 21  order by 1, 2
 22  /
O_DATE      FTIME TO_TI B L D
----------- ----- ----- - - -
01-JAN-2007 0730  1400  Y Y N
02-JAN-2007 0700  1200  Y N N
03-JAN-2007 1110  1525  N Y N
04-JAN-2007 1720  2300  N N Y
05-JAN-2007 0800  2300  N Y Y

5 rows selected.


</quote>

Regards
Michel

Diffrence between 2 timestamps in minutes

A reader, July 31, 2007 - 3:11 pm UTC

Tom,

I have the following 2 tables
***********************

create table A (taskid number, task_start_timestamp varchar2(30),TASK_END_TIMESTAMP varchar2(30));

create table B (taskid number, duration number(9,2));
************************
Table A stores the timestamp in a varchar2 datatype (I have to live with it, cannot change the datatype), a new task is inserted into A with timestamp in start column and upon completion the end column is populated. Now i need to calculte the diffrence between the end and start time in minutes and insert it into table B under duration.

Here is some sample data:
******************
insert into a values (1,'2007-07-08 21:48:10.264','2007-07-08 21:48:10.264');
insert into a values (2,'2007-07-08 21:48:10.264','2007-07-08 22:30:25.683');
insert into a values (3,'2007-07-08 22:01:58.749','2007-07-08 22:42:55.489');
commit;

********************

Here is the query that I am currently using to get the value in minutes.

********

select
trunc((extract (day from (to_timestamp(TASK_END_TIMESTAMP,'yyyy-mm-dd HH24:MI:SS.FF')-to_timestamp(task_start_timestamp,'yyyy-mm-dd HH24:MI:SS.FF')))*24*60)+
(extract (hour from (to_timestamp(TASK_END_TIMESTAMP,'yyyy-mm-dd HH24:MI:SS.FF')-to_timestamp(task_start_timestamp,'yyyy-mm-dd HH24:MI:SS.FF')))*60)+
extract (minute from (to_timestamp(TASK_END_TIMESTAMP,'yyyy-mm-dd HH24:MI:SS.FF')-to_timestamp(task_start_timestamp,'yyyy-mm-dd HH24:MI:SS.FF')))+
(extract (second from (to_timestamp(TASK_END_TIMESTAMP,'yyyy-mm-dd HH24:MI:SS.FF')-to_timestamp(task_start_timestamp,'yyyy-mm-dd HH24:MI:SS.FF')))/60),2)
FROM
A

******

Is there any other simple and elegant way to do it?



Tom Kyte
August 02, 2007 - 10:38 am UTC

holy cow, what is up with your datatype selection. why do people do this, why why why. I'll never, as long as I live, understand this.

ops$tkyte%ORA10GR2> select (to_date( substr( task_end_timestamp,1,19),   'yyyy-mm-dd hh24:mi:ss')
  2         -to_date( substr( task_start_timestamp,1,19), 'yyyy-mm-dd hh24:mi:ss'))*24*60 mins
  3    from a
  4  /

      MINS
----------
         0
     42.25
     40.95

wether daylight savings was applicable on the day

umesh_kasturi, September 25, 2007 - 2:44 am UTC

Tom
My servers are in U.S of A. The creation_date column contains the date and time created of the user
Can I know wether on any given date , wether daylight savings was present ?
Tom Kyte
September 26, 2007 - 9:13 pm UTC

whether....


it entirely depends on whether the server itself was setup with timezones...

mine for example is running GMT...

now, you could twiddle around a bit with timestamp functions -but what does

select dbtimezone from dual

return and are you SURE it'll be that way forever?

whether daylight present

umesh_kasturi, September 27, 2007 - 5:08 am UTC

SQL> select dbtimezone from dual ;

DBTIME
------
+01:00

This remains the same. 
If I simplify the question I think I am looking for :
For any given year which are the 2 dates , between which the daylight savings is presnt ( in a PST zone) 

Tom Kyte
September 27, 2007 - 7:19 am UTC

your database is in a fixed timezone - it is not in a regional one - so daylight savings is not used.

your answer is simple: it is never daylight savings. Many regions do not observe it - your database is +1 from GMT.

time zone

umesh_kasturi, September 27, 2007 - 7:31 am UTC

Sorry I was not clear The problem for me is I store dates in the DB in PST. I access them to service customers in India. What I want is to get the date on which the day light saving is used and accordingly do the logic in Java code to add or subtract the hours required
Hope I am clear this time

Tom Kyte
September 28, 2007 - 3:09 pm UTC

well, do you always store them in PST or do you store them in Pacific time - meaning, some are PST and some are PDT.

try these methods

Pasko, September 27, 2007 - 11:10 am UTC

Hi Umesh,

try one of these methods to convert the dates between
different time zones.
Ask your DBA to load the correct Time-zone files if some
time zone names are missing.

to see all time zones use:
select * from V$TIMEZONE_NAMES



select NEW_TIME (
to_date ('1999-12-01 11:00:00', 'YYYY-MM-DD HH:MI:SS'),
'EST',
'PST'
) "West Coast Time method_1",
FROM_TZ (
cast (to_date ('1999-12-01 11:00:00', 'YYYY-MM-DD HH:MI:SS') as timestamp),
'America/New_York'
) at time zone 'America/Los_Angeles' "West Coast Time method_2"
from dual


Tom Kyte
September 28, 2007 - 4:07 pm UTC

best to find out if they really meant what they said - that the time is stored in a canonical format - using PST.

I'd be really surprised if the answer is "yes", rather it is stored in PST and PDT - and without a timezone attribute, it is pointless to try and adjust some of the times - because you simply do not know what timezone they were in during the overlap period.

Getting dat acomponent from TIMESTAMP

Bob, September 28, 2007 - 12:39 pm UTC

Hi Tom,

How do I get the date component from a TIMESTAMP for use in a where clause. When I do

select a,
b
from c
where trunc(cast(a.x as date)) >= '13-AUG-2007'
and trunc(cast(a.y as date)) <= '04-SEP-2007'

I get misleading results (i.e more records) if I switch
'04-SEP-2007' to '03-SEP-2007'.

Any ideas to get round this would be appreciated. Thanks
Tom Kyte
September 28, 2007 - 6:05 pm UTC

why are you comparing a date to a string?

anyway, no create table, no inserts, no look

comparing TIMESTAMP

Bob, September 28, 2007 - 6:33 pm UTC

Hi Tom - sorry for being ambiguity. Here are the details:

desc T_JACSanctions

Name Null? Type
----------------------------------------- -------- -------
SANCTIONSREF NOT NULL NUMBER
PAYMENTREF NOT NULL NUMBER
ORIGINATINGSITEREF NOT NULL NUMBER
SITEREF NOT NULL NUMBER
SANCTIONDATE NOT NULL TIMESTAMP(6)
TOTALTOPRIME NUMBER
TOTALFROMPRIME NUMBER
POSSIBLEVIOLATION NUMBER
EAPBLOCKED NUMBER
EAPREJECTED NUMBER
EAPPENDED NUMBER
EAPWAIVED NUMBER
EAPINSPECT NUMBER
EAPARCHIVED NUMBER
EAPINSPECTED NUMBER
EAPRETURNED NUMBER
EAPREFERRED NUMBER
EAPOFACSENT NUMBER
CREATEDBY NOT NULL NUMBER
SANCTIONEDREASON VARCHAR2(4000)
USERCOMMENT VARCHAR2(4000)

We insert into the table via a procedure that is called from Java. Thus:

CREATE OR REPLACE PROCEDURE p_jacpopulatesanctions (ipaymentstatus IN NUMBER,
ipossibleviolation IN NUMBER,
itotaltoprime IN number,
itotalfromprime IN NUMBER,
ipaymentref IN NUMBER,
iorigsiteref IN NUMBER,
icurrsiteref IN NUMBER,
iuserref IN NUMBER,
ireason IN VARCHAR2,
icomment IN VARCHAR2,
ostatus OUT NUMBER)
IS
payment_status_not_found EXCEPTION;
BEGIN

SAVEPOINT sanctions_start;

IF ipaymentstatus NOT IN(13,15,16,17,18,19,20,21,22,23) THEN
RAISE payment_status_not_found;
END IF;

INSERT INTO t_jacsanctions
VALUES
(s_jacsanctionsref.NEXTVAL,
ipaymentref,
iorigsiteref,
icurrsiteref,
sysdate,
nvl(itotaltoprime,0),
nvl(itotalfromprime,0),
NVL(ipossibleviolation,0),
DECODE(ipaymentstatus,16,1,0) , --BLOCKED
DECODE(ipaymentstatus,17,1,0) , --REJECTED
DECODE(ipaymentstatus,18,1,0) , --PENDED
DECODE(ipaymentstatus,20,1,0) , --WAIVED
DECODE(ipaymentstatus,13,1,0) , --INSPECT
DECODE(ipaymentstatus,19,1,0) , --ARCHIVED
DECODE(ipaymentstatus,15,1,0) , --INSPECTED
DECODE(ipaymentstatus,22,1,0) , --RETURNED
DECODE(ipaymentstatus,21,1,0) , --REFERRED
DECODE(ipaymentstatus,23,1,0) , --OFACSENT
iuserref,
ireason,
icomment
);

ostatus := 0;

EXCEPTION
WHEN payment_status_not_found THEN
ROLLBACK TO sanctions_start;
ostatus := -1;
WHEN OTHERS THEN
ROLLBACK TO sanctions_start;
ostatus := -2;
END;
/

We are reporting from this table using the following SQL query:

SELECT
rowid,
createdby,
sanctionsref,
b.sanctiondate,
DECODE(NVL(b.possibleviolation,0), -1, 0, NVL(b.possibleviolation,0) ) viol,
eapinspect,
eapreferred,
rn
FROM (SELECT b.*,
ROW_NUMBER() OVER
(PARTITION BY b.paymentref
ORDER BY sanctiondate DESC) rn
FROM t_jacsanctions b,
t_jacpayment c
WHERE c.paymentref = b.paymentref
and to_timestamp(b.sanctiondate) between timestamp '2007-08-13 00:00:00' and timestamp'2007-09-04 00:00:00'
AND b.createdby IN(2585,2788,2430,2208,2497,2584,2496,2713,2789)
) b
WHERE RN < 2

The date range predicate misses out a record dated:

14th August 2007 5:48pm. This record is within the range of the predicate. My question is why and what am I doing wrong? Thanks very much Tom.
and to_char(cast(b.sanctiondate as date), 'YYYYMMDD') = '20070814'
Tom Kyte
October 03, 2007 - 1:29 pm UTC

I cannot being to tell you how much I DESPISE your exception block.

the rollback to savepoint - would not be necessary if you let the exception propagate.

the use of "return codes" is so 1980 - I hate when others, I really wish it did not exist.

the 21st century language you are invoking this from - Java - it does this exception stuff, plsql does it, most real languages do it - use it the right way. I hope your java isn't filled with try catch blocks that catch any exception and hide them too.

(I won't even comment on the usefullness of defining and catching your own exception like that, lets just use a goto in the future, it would be more obvious what you are trying to do!!!)

and your insert - you are missing the list of columns...

if you use a timestamp column in the table, why would not you insert systimestamp? instead of sysdate.

In any case - since August 14th happened months ago - I obviously do NOT have your data.


How about this:

create table ( just the column(s) needed )
insert into table ( the precise values to reproduce your issue )
select * from t where <condition that doesn't return what you think it should>


try to_timestamp instead of Timestamp '...'

A reader, September 30, 2007 - 5:00 am UTC

Hi Bob,

i have also seen this Timestamp '...' format that you have used in Oracle Docs, but i prefer to use the Function to_timestamp explicitly.

@Tom, is this Timestamp '...' format missing the HH24:MI:SS section?

eg:
SQL> create table t(sanctiondate timestamp(6) ) ;

Tabelle wurde erstellt.

SQL>
SQL> insert into t values(systimestamp);

1 Zeile wurde erstellt.

SQL> /

1 Zeile wurde erstellt.

SQL> /

1 Zeile wurde erstellt.

SQL> commit ;

Transaktion mit COMMIT abgeschlossen.


SQL> select * from t;

SANCTIONDATE
---------------------------------------------------------------------------
30.09.07 10:44:59,078000
30.09.07 10:45:01,593000
30.09.07 10:45:03,156000

SQL> select count(*) from t
     where sanctiondate between 
 to_timestamp('2007-09-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
  and to_timestamp('2007-09-30 23:59:59','YYYY-MM-DD HH24:MI:SS');

  COUNT(*)
----------
         3

TIMESTAMP in a WHERE clause

Bob, September 30, 2007 - 2:56 pm UTC

Hiya - I have tried using to_timestamp with the format mask:

'DD-MON-YYYY HH24:MI:SS.FF'

But it doesn't make any difference I still get that particular record (stated in my last post missing). I looked on Oracle Metalink and they have a workaround:

https://metalink.oracle.com/metalink/plsql/f?p=130:14:11583055226743621763::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,3116276.8,1,1,1,helvetica

But that doesn't work for me either - I am a bit stumped!!

TIMESTAMP in WHERE clause

Bob, September 30, 2007 - 3:49 pm UTC

BTW - should have mentioned from the outset, I am on Oracle 9.2.0.7.0. I think they have fix for this problem in Oracle 11 using TRUNC with TIMESTAMP.

Cheers,
Bob

TIMESTAMP in WHERE clause

Bob, September 30, 2007 - 3:49 pm UTC

BTW - should have mentioned from the outset, I am on Oracle 9.2.0.7.0. I think they have fix for this problem in Oracle 11 using TRUNC with TIMESTAMP.

Cheers,
Bob

why do you need trunc?

Pasko, October 01, 2007 - 5:56 am UTC

Hi Bob,
in your query i see:
...
...and to_timestamp(b.sanctiondate) between timestamp '2007-08-13 00:00:00' and
timestamp'2007-09-04 00:00:00'

why do you need a trunc or to_timestamp on b.sanctiondate?

i think it's not a good idea to put a function on a DB Column on the left hand side of a where condition because this will prevent the use of an index on b.sanctiondate,
unless if you have a function-based index on that ;)

have you tried:

...and b.sanctiondate between
to_timestamp('2007-08-13 00:00:00','YYYY-MM-DD HH24:MI:SS') and
to_timestamp('2007-09-04 00:00:00','YYYY-MM-DD HH24:MI:SS')

Hope this helps.

TIMESTAMP and Where clause

Bob, October 01, 2007 - 9:26 am UTC

Hi Pasko - thanks for your advice. Yes, I have tried doing that, but no joy.

BTW, you are absolutely spot on with disabling my index with a function and FBI's.

I think in Oracle 9.2 we are fairly limited by how we can manipulate a TIMESTAMP in a where clause. I had to revert back to modifying the column as a DATE. I would be very interested to know if there is a FIX for this.

Cheers,
Bob

TIMESTAMP AND TRUNC

Bob, October 07, 2007 - 5:08 pm UTC

Hi Tom,

Thanks for your comments - I am afraid I come from a IBM Mainframe/COBOL background - hence the status codes which get fed to the Java. And on the use of GOTOS's, they can be used intelligently in PARA's in COBOL!

There was a bug in the code. The situation I had was if there were two payments:

e.g dated 14 Aug 2007
03 Sep 2007

I should pick up 03 Sep 2007. But when the start and end date condition are inside the inline view it picks up the 14 Aug 2007 (I shouldn't see this). So the condition had to be outside the inline view to work...

weird result with daylight saving

A reader, February 20, 2008 - 10:35 am UTC

Tom,
We are running  10.2.0.3.0 release of Oracle.

I run into this weird problem when i try to go over a "Daylight Saving" day..
---
If I try to select a non-existant hour in march(when it jumps from 1:00AM to 3:00 AM), I get correct results:

SQL>  select to_timestamp_tz('03/11/2007 02:13:23 US/Central', 'mm/dd/yyyy hh24:mi:ss tzr') from dual;
 
ORA-01878: specified field not found in datetime or interval



But when I try to do the same by adding an interval to 1 AM, it lets me do that :(

SQL> select to_timestamp_tz('03/11/2007 01:00:00 US/Central', 'mm/dd/yyyy hh24:mi:ss tzr')
  2   + to_dsinterval ('0 01:13:23')
  3   from dual
  4  /
 
TO_TIMESTAMP_TZ('03/11/200701:
-----------------------------------------------------------
11-MAR-07 02.13.23.000000000 AM US/CENTRAL

What am I doing wrong? Please help!

Thanks a lot 

Tom Kyte
February 20, 2008 - 12:39 pm UTC

that might be an issue, I ran in 11g

  1* select to_timestamp_tz('03/11/2007 01:00:00 US/Central', 'mm/dd/yyyy hh24:mi:ss tzr')+ to_dsinterval ('0 01:13:23') from dual
ops$tkyte%ORA11GR1> /

TO_TIMESTAMP_TZ('03/11/200701:00:00US/CENTRAL','MM/DD/YYYYHH24:MI:SSTZR')+T
---------------------------------------------------------------------------
11-MAR-07 03.13.23.000000000 AM US/CENTRAL


please utilize support.

timestamp

A reader, February 26, 2008 - 2:41 pm UTC

Tom:

How do you decide between column type of "date" or "timestamp".

I need to store a server heartbeat (Date/time) the pulse/message is sent from that server using http.

would you use just "date" type? when do you use that timestamp type.

thanks
Tom Kyte
February 26, 2008 - 3:16 pm UTC

do you need precision beyond second? Yes = timestamp

do you need timezone support? Yes = timestamp


if you get here, use whichever one makes you feel better, a timestamp(0) (no precision after seconds) is just like a date. Timestamps have different functions for them than dates, but if you like dates - no reason not to use them at this point.

at time zone query bind variable

A reader, March 20, 2009 - 12:18 pm UTC

tom,

i am also facing the bind variable issue discussed above
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5011677391274#67042255195840

the query SELECT SYSTIMESTAMP AT TIME ZONE :tz NYTIME FROM DUAL does not work

any option to get it work ?

can i use the following as an alternative..any issues with this approach

SELECT SYSTIMESTAMP AT TIME ZONE TO_CHAR(:TZ) NYTIME FROM DUAL

SELECT SYSTIMESTAMP AT TIME ZONE (SELECT :TZ FROM DUAL) NYTIME FROM DUAL

DB Version-Oracle Database 10g Release 10.2.0.1.0

thanks
jyo


timedate_pkg

A reader, April 21, 2009 - 7:34 am UTC

Hi,

I'm trying to use timestamp_pkg in order to convert between local time and GMT.

Thereby I noticed that the packaged is based on the assumption that you are running with nls_territory = AMERICA where sunday is week day no 1.

So if you (like me) don't use that setting, you'll have to compensate for that. Where I am (Denmark), day no 1 of the week is monday.

/Michael

A reader, November 16, 2009 - 7:14 am UTC

If we want to show user of his local time then we should use timestamp with local time zone?

we have a web application how we get time zone of user from OS? The insertion process will be to get time zone from user OS and set session parameter and simply insert.

On retrieval what will be done for each user. for example user inserted record with +1 time zone and then a user comes with +3 time zone how we show his time.


CREATE TABLE SCOTT.DATE_TABLE
(
TIME_STAMP_TZ TIMESTAMP(6) WITH TIME ZONE,
TIME_STAMP_LTZ TIMESTAMP(6) WITH LOCAL TIME ZONE
);

Insert into DATE_TABLE
(TIME_STAMP_TZ, TIME_STAMP_LTZ)
Values
(TO_TIMESTAMP_TZ('11/16/2009 12:06:25.156597 PM +01:00','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM TZH:TZM'), TO_TIMESTAMP_TZ('11/16/2009 4:06:25.156597 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
COMMIT;


Tom Kyte
November 23, 2009 - 9:58 am UTC

... we have a web application how we get time zone of user from OS? ...

good question, you would absolutely not get it from ... the database. So, I guess you would have to ask the middle tier developers how to do this.


you would set your sessions timezone and select.


ops$tkyte%ORA11GR2> create table t
  2  ( x timestamp,
  3    y timestamp with time zone,
  4    z timestamp with local time zone
  5  )
  6  /

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t values ( systimestamp, systimestamp, systimestamp );

1 row created.

ops$tkyte%ORA11GR2> select dbtimezone, sessiontimezone, t.* from t;

DBTIME
------
SESSIONTIMEZONE
---------------------------------------------------------------------------
X
---------------------------------------------------------------------------
Y
---------------------------------------------------------------------------
Z
---------------------------------------------------------------------------
+00:00
-05:00
23-NOV-09 10.56.42.020873 AM
23-NOV-09 10.56.42.020873 AM -05:00
23-NOV-09 10.56.42.020873 AM


ops$tkyte%ORA11GR2> alter session set time_zone = '-08:00';

Session altered.

ops$tkyte%ORA11GR2> select dbtimezone, sessiontimezone, t.* from t;

DBTIME
------
SESSIONTIMEZONE
---------------------------------------------------------------------------
X
---------------------------------------------------------------------------
Y
---------------------------------------------------------------------------
Z
---------------------------------------------------------------------------
+00:00
-08:00
23-NOV-09 10.56.42.020873 AM
23-NOV-09 10.56.42.020873 AM -05:00
23-NOV-09 07.56.42.020873 AM




the first one, X is just a timestamp, no timezone information at all.

The second one is of a fixed timzone, it'll be GMT-5 - you can convert it to another timezone, but it'll come back by default as GMT-5

The third one will look at the timezone of the session and adjust the value as appropriate.


A reader, November 24, 2009 - 3:32 am UTC

In my web application user access the application from any where in the world. application server and data base server on same location (NJ). how can i store the time zone in my DB and show the data to user according to his time zone.I am using spring in my middle tier and connection object is not availabel. Application server manges the connection pooling. How can I set the D.B session time zone according to client. OR I have to always send the date with time zone from front end and not use systimstamp
Tom Kyte
November 24, 2009 - 11:18 am UTC

.. how can i
store the time zone in my DB and show the data to user according to his time
zone. ..

you are the developer - you tell me?


You have to tell the database what timezone you would like to have data displayed in (not the other way around) and if your application framework doesn't permit you to issue an alter session or call a stored procedure to do so - there is nothing I can do to help you, your framework isn't a framework as much as a barrier to being productive.



You would have to figure out what timezone your client would like to see (how you do that is entirely and utterly up to you, yahoo for example has me set my timezone as a preference and they use that). Then you would issue the alter session and data would magically appear in that timezone (if you use the timezone type demonstrated above)

Time Zone lost when TRUNC( )

A reader, February 04, 2011 - 4:56 am UTC

Hi Tom,

how can I preserve the time zone info after TRUNC( ) time column to HH24/DD/WI ?
When I do TRUNC(TSZ,'HH24') I am getting 01-01-2011 10:30:00
I am want to get : 01-01-2011 10:00:00 +5:30
Is there is way to preserve the TZ after TRUNC?
Basicaly we want know from which time zone this data belongs even after TRUNC.



alter session set NLS_TIMESTAMP_TZ_FORMAT='DD-MM-YYYY HH24:MI:SS TZd';
drop table jetload.TestTZ;
CREATE TABLE JETLOAD.TESTTZ (COUNTRY VARCHAR(10), tsz TIMESTAMP WITH TIME ZONE );

INSERT INTO JETLOAD.TESTTZ VALUES('INDIA', TIMESTAMP' 2011-01-01 10:30:00 +5:30');

SELECT COUNTRY, TSZ FROM JETLOAD.TESTTZ;
SELECT COUNTRY, TRUNC(TSZ,'HH24') FROM JETLOAD.TESTTZ;


alter session set succeeded.
drop table jetload.TestTZ succeeded.
CREATE TABLE succeeded.
1 rows inserted
COUNTRY TSZ
---------- -------------
INDIA 01-01-2011 10:30:00 +05:30

COUNTRY TRUNC(TSZ,'HH24')
---------- -------------------------
INDIA 01-01-2011 10:00:00

Tom Kyte
February 04, 2011 - 9:59 am UTC

trunc takes either a DATE or a NUMBER, in this case your timestamp is implicitly cast as a date (no TZ).

try this approach

ops$tkyte%ORA11GR2> CREATE TABLE TESTTZ (COUNTRY VARCHAR(10), tsz TIMESTAMP WITH TIME ZONE );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> INSERT INTO TESTTZ VALUES('INDIA',  TIMESTAMP' 2011-01-01 10:30:00 +5:30');

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> SELECT  COUNTRY,  TSZ,
  2          to_timestamp_tz( to_char( tsz, 'yyyymmddhh tzh:tzm'), 'yyyymmddhh tzh:tzm') FROM  TESTTZ;

COUNTRY
----------
TSZ
---------------------------------------------------------------------------
TO_TIMESTAMP_TZ(TO_CHAR(TSZ,'YYYYMMDDHHTZH:TZM'),'YYYYMMDDHHTZH:TZM')
---------------------------------------------------------------------------
INDIA
01-JAN-11 10.30.00.000000 AM +05:30
01-JAN-11 10.00.00.000000000 AM +05:30


Time Zone lost when TRUNC( )

A reader, February 08, 2011 - 2:39 am UTC

Hi Tom,

i made small plsql function as given below.

CREATE OR REPLACE FUNCTION JETLOAD.TZ_TRUNC ( tsz TIMESTAMP WITH TIME ZONE, DATE_TRUNC_FROMAT IN VARCHAR2
) RETURN TIMESTAMP WITH TIME ZONE AS
BEGIN
RETURN TO_TIMESTAMP_TZ( TRUNC(TSZ,DATE_TRUNC_FROMAT)||TO_CHAR( TSZ, ' tzh:tzm'), 'DD-MM-YYYY HH24:MI:SS tzh:tzm');
END TZ_TRUNC;

we are using TZ_TRUNC() to create Hr/day/week summery tables , this function is called for millions of rows.
i like to know what is the best way to get good performance.
Tom Kyte
February 09, 2011 - 7:21 am UTC

get rid of the function and just use the to_* functions directly. If you want to "hide it", use a view (10g or before) or virtual column (11g and above)

Partition on TIMESTAMP WITH TIME ZONE

A reader, February 15, 2011 - 12:39 am UTC

Hi Tom,

I try to create PARTITION on "TIMESTAMP WITH TIME ZONE" column its not supported.

create table OMC.TESTTZ
(COUNTRY VARCHAR(10),
TZ TIMESTAMP WITH TIME ZONE
)
partition by RANGE (TZ)
(
PARTITION DEC_2008 VALUES LESS THAN (TO_TIMESTAMP_TZ('2009-01-01
11:00:00 -8:00','YYYY-MM-DD HH:MI:SS TZH:TZM'))
)
SQL Error: ORA-03001: unimplemented feature
03001. 00000 - "unimplemented feature"


So I created virtual column Timestamp as below.

drop table OMC.TESTTZ;
create table OMC.TESTTZ
(COUNTRY VARCHAR(10),
TZ TIMESTAMP WITH TIME ZONE,
ts TIMESTAMP generated always as ( to_char(tz, 'DD-MON-YYYY HH:MI:SS')) virtual
)
partition by RANGE (ts)
(
PARTITION P2011010108 VALUES LESS THAN (TIMESTAMP' 2011-01-01 11:00:00'),
PARTITION P2011010109 VALUES LESS THAN (TIMESTAMP' 2011-01-01 11:00:00'),
PARTITION P2011010110 VALUES LESS THAN (TIMESTAMP' 2011-01-01 11:00:00'),
PARTITION P2011010111 VALUES LESS THAN (TIMESTAMP' 2011-01-01 11:00:00'),
PARTITION "PM_MAXVALUE" VALUES LESS THAN (MAXVALUE)
);

In our DW application we need to have partition & display the data info with TZ

This sql is hitting right partition :

SELECT COUNTRY,TZ
FROM OMC.TESTTZ F
WHERE
(F.TS >= TIMESTAMP' 2011-01-01 09:00:00'
AND F.TS < TIMESTAMP' 2011-01-01 10:00:00')


Our application is meta model design in such a that we can have only one TIME dimension.
Sql is generated based on this meta model.
So in a sql I can have TZ column alone. ( i.e. TZ is needed to show time zone info & TS to hit the partition )

Is there is any trick I can use to hide TS column completely in my application sql still hit right partition.

Thanks

Tom Kyte
February 15, 2011 - 8:30 am UTC

think about what happens with daylight savings - when there is an hour repeated.

What will happen to your data in those cases.

Will the data be right for you? Is daylight savings a concern to you?

Partition on TIMESTAMP WITH TIME ZONE

A reader, February 15, 2011 - 5:46 am UTC

hi Tom,

some typo in my post.

my requirement is :
can I hide 'TS' column completely in my application, but still hit correct partition.

Partition on TIMESTAMP WITH TIME ZONE

A reader, February 15, 2011 - 9:45 am UTC

hi Tom,

daylight savings is not a concern.
Data is more for analysis & reporting purpose there is no transaction.

Tom Kyte
February 15, 2011 - 12:18 pm UTC

well, then think about what will happen if you put in data with different time zones.

I put in these two TZ times, the corresponding TS times are:

ops$tkyte%ORA11GR2> select tz, ts from testtz;

TZ
---------------------------------------------------------------------------
TS
---------------------------------------------------------------------------
01-JAN-09 11.00.00.000000 AM -08:00
01-JAN-09 11.00.00.000000 AM

01-JAN-09 11.00.00.000000 AM -05:00
01-JAN-09 11.00.00.000000 AM



so, is that correct for you


I mean - you are the only one situated to tell us if you are going to get the correct data in response to your query. You'd have to fully define "correct" first.

Time Zone lost when TRUNC( )

A reader, February 18, 2011 - 6:52 am UTC

Hi Tom,

I am not sure I am missing some basic here. its correct for our application.

In our DW application we are collecting telecom network traffic.
We do summery report on traffic for every hour /day /week.. i.e. group by TRUNC( time,'HH24')

We want to see 10:00 traffic of East & 10:00 traffic Pacific as independently.
there is no relation between East & Pacific time.

As of now we are using "TIMESTAMP" column, to store time by removing time zone info from the data.
we can't use "TIMESTAMP WITH local TIME ZONE" where time is convert to server time.

i.e. Time is a dimension, if its changed then it will change the meaning.
Report will show as traffic is happening on server time that’s not correct.

** Problem we are facing is, user want to see real time traffic, he sits in Central TZ.
User SQL has where clause as his time (Central Time -1min ) , for this SQL we are getting older data of East & no data from West :-)).


Say user fires as query at 10 AM of central time, query gets older data of east, its already 11 AM in east & no data from west its only 8:00 AM in west.

I don't know how to solve this problem.
Ideally this will get solved if store the time in "TIMESTAMP WITH local TIME ZONE" column but this has problem of changing dimension value I said above.

Thanks
Tom Kyte
February 18, 2011 - 9:11 am UTC

trunc works on dates - not timestamps.

when you call trunc, we implicitly convert the timestamp into a date, thus losing the timezone.

see the comment above

... Time Zone lost when TRUNC( ) February 4, 2011 - 4am Central time zone ...

this was just covered. It shows one possible method of truncating while preserving the time zone.

Partition on TIMESTAMP WITH TIME ZONE

A reader, February 18, 2011 - 9:31 am UTC

Hi Tom,

>> trunc works on dates - not timestamps
-this is very clear to me from your reply.

Can you check & tell me other problem i desc in above thread.
>> February 18, 2011 - 6am Central time zone

Regards
Tom Kyte
February 18, 2011 - 9:42 am UTC

I'm getting confused here.

What is the data type of the column you have in the database right now. The exact datatype.

And I'm assuming you just want to pull records between two timestamps that include timezones. correct?

Partition on TIMESTAMP WITH TIME ZONE

A reader, February 18, 2011 - 10:15 am UTC

Hi Tom,

i am having TIMESTAMP as of now.
we want to have 'TIMESTAMP with Time zone' -- but partition is not supported for this.

we want to pull newly arrived data from different TZ.

regards

Tom Kyte
February 18, 2011 - 11:53 am UTC

then store everything in UTC and partition by that. If all times are stored in a known (single) timezone - we can convert them to any other timezone.

Oleksandr Alesinskyy, February 18, 2011 - 12:44 pm UTC

Partitioning is as well supported for TIMESTAMP WITH LOCAL TIMEZONE.

That said I hardly can understand why Oracle badly mixes 2 unrelated concepts - timestamp storage and timestamp presentation.

Oleksandr Alesinskyy, February 24, 2011 - 2:54 pm UTC

I have one remark (or question?) regarding timestamp-based range partitioning and optimizer.

Today I observed following behavior (11.1.0.7):
1. A table partitioned on TIMESTAMP WITH LOCAL TIMEZONE column (1 partition per day).
2. A query employs BETWEEN on this column with upper and lower border expressed as timestamp literals.
3. If these literals do not include timezone, partition elimination successfully occurs (and it does not matter if the session timezone matches timezone of partition borders).
4. If these literals do include timezone partition elimination does not occurs (and it does not matter if the session timezone matches timezone of partition borders).

Any idea why?

Here is the sample code
CREATE TABLE LOG_ENTRY
(ENTRY_TIMESTAMP TIMESTAMP(3) WITH LOCAL TIME ZONE NOT NULL)
PARTITION BY RANGE (ENTRY_TIMESTAMP) 
( PARTITION P_20110215000000 VALUES LESS THAN (TIMESTAMP' 2011-02-16 00:00:00,000000000Europe/Berlin CET'),  
  PARTITION P_20110216000000 VALUES LESS THAN (TIMESTAMP' 2011-02-17 00:00:00,000000000Europe/Berlin CET'),  
  PARTITION P_20110217000000 VALUES LESS THAN (TIMESTAMP' 2011-02-18 00:00:00,000000000Europe/Berlin CET'),  
  PARTITION PMAX VALUES LESS THAN (MAXVALUE)
);

alter session set time_zone='GMT';
select * from log_entry where entry_timestamp between timestamp'2011-02-17 00:00:00' and timestamp'2011-02-17 23:59:59.999';
select * from table(dbms_xplan.display_cursor);

alter session set time_zone='CET';
select * from log_entry where entry_timestamp between timestamp'2011-02-17 00:00:00' and timestamp'2011-02-17 23:59:59.999';
select * from table(dbms_xplan.display_cursor);

alter session set time_zone='GMT';
select * from log_entry where entry_timestamp between timestamp'2011-02-17 00:00:00 GMT' and timestamp'2011-02-17 23:59:59.999 GMT';
select * from table(dbms_xplan.display_cursor);

alter session set time_zone='CET';
select * from log_entry where entry_timestamp between timestamp'2011-02-17 00:00:00 GMT' and timestamp'2011-02-17 23:59:59.999 GMT';
select * from table(dbms_xplan.display_cursor);

alter session set time_zone='GMT';
select * from log_entry where entry_timestamp between timestamp'2011-02-17 00:00:00 Europe/Berlin CET' and timestamp'2011-02-17 23:59:59.999 Europe/Berlin  CET';
select * from table(dbms_xplan.display_cursor);

alter session set time_zone='CET';
select * from log_entry where entry_timestamp between timestamp'2011-02-17 00:00:00 Europe/Berlin CET' and timestamp'2011-02-17 23:59:59.999 Europe/Berlin  CET';
select * from table(dbms_xplan.display_cursor);


Tom Kyte
February 24, 2011 - 7:02 pm UTC

if you look at the filter when you don't use a timestamp, you see:

   1 - filter(TIMESTAMP' 2011-02-17 00:00:00.000000000'<=TIMESTAMP' 2011-02-17
              23:59:59.999000000')
   3 - filter(("ENTRY_TIMESTAMP">=TIMESTAMP' 2011-02-17 00:00:00.000000000' AND
              "ENTRY_TIMESTAMP"<=TIMESTAMP' 2011-02-17 23:59:59.999000000'))


when you use a timestamp, the filter is:

   2 - filter((SYS_EXTRACT_UTC(INTERNAL_FUNCTION("ENTRY_TIMESTAMP"))>=TIMESTAMP'
              2011-02-17 00:00:00.000000000' AND SYS_EXTRACT_UTC(INTERNAL_FUNCTION("ENTRY_TIMESTAMP"))<
              =TIMESTAMP' 2011-02-17 23:59:59.999000000'))



you can see the functions being applied to the timestamp column - which obviates partition elimination.

a timestamp with local timestamp is really just a timestamp without a timezone. From Expert Oracle Database Architecture:

<quote>
TIMESTAMP WITH LOCAL TIME ZONE: This is a fixed-width 7- or 11-byte date/time datatype just as the TIMESTAMP is; however, it is time zone sensitive. Upon modification in the database, the TIME ZONE supplied with the data is consulted, and the date/time component is normalized to the database time zone. So, if you were to insert a date/time using the time zone U.S./Pacific and the database time zone was U.S./Eastern, the final date/time information would be converted to the Eastern time zone and stored as a TIMESTAMP would be. Upon retrieval, the TIMESTAMP stored in the database would be converted to the time in the session’s time zone.
</quote>

so, the timestamp with local time zone is stored with 7 bytes (or 11 if you have microseconds) and is in effect just a timestamp. When you compare it to a true timestamp with timezone - you need to "promote it" to the timestamp with timezone type - it needs to be promoted (and changed) to the 13 byte representation.


Beware implicit conversions!!! They are evil.

Oleksandr Alesinskyy, February 25, 2011 - 6:47 am UTC

Firstly, I assume that writing if you look at the filter when you don't use a timestamp you meant if you look at the filter when you don't use a timezone

Secondly, I am wondering why Oracle applies functions to the column value instead of converting a specified time-zoned literals to the session timezone (do I understand correctly that timestamp literals specified without timezone interpreted in session timezone)?

Tom Kyte
February 25, 2011 - 8:53 am UTC

yes, that is what I meant - timezone, not timestamp in that context, sorry about that.


Secondly, I am wondering why Oracle applies functions to the column value instead of converting a specified time-zoned literals to the session timezone (do I understand correctly that timestamp literals specified without timezone interpreted in session timezone)?


because implicit conversions tend to go "up"

when we compare a string to a number, the string is converted up - into a more specific type - that of number.

for example. When you compare a timestamp to a timestamp with timezone - the rule goes from the less specific to the more specific type.

As is the case in most implicit conversions I've seen in languages.


Oleksandr Alesinskyy, February 28, 2011 - 9:47 am UTC

From my point of view TIMESTAMP WITH TIMEZONE is neither less nor more specific than TIMESTAMP WITH LOCAL TIMEZONE
Tom Kyte
February 28, 2011 - 11:27 am UTC

You might need to change your perspective.

Timestamps with local timezones are not stored with a timezone at all. They are normalized upon insert - converted into a timestamp with the appropriate time.

but what should be clear is that....


timestamp with timezone is not the same type as timestamp with local timezone.


And the hierarchy is such that timestamp with timezone is a more "precise" one - it represents a date, a time and a timezone - whereas the other represents a date and a time - and gets the timezone base from the database itself. Therefore, when comparing these two different datatypes (they are two different datatypes), one must be converted into the other - and the rule is .... stated above.


Whenever you compare two attributes/expressions that have different datatypes - you MUST beware of the implicit conversions that have to take place.

Which means (to me anyway) that you should code all such comparisons using explicit conversions...

Oleksandr Alesinskyy, February 28, 2011 - 3:31 pm UTC

From my point of view a conversion of TIMESTAMP WITH LOCAL TIME ZONE should be done differently - for the matter of (such)comparison it simply should be treated as a timestamp with the session timezone attached.
Tom Kyte
March 01, 2011 - 8:01 am UTC

But - and this is important:

things work the way they work, not the way "we" would have done it. This is a truism.

They are two different datatypes - completely - different sizes, different meanings. You have to convert one into the other - it is that simple. It is an IMPLICIT conversion. If you don't like the way that particular IMPLICIT conversion is done - then by all means - be explicit about it.

Anytime you compare datatype X to datatype Y - this sort of stuff happens.

TIMESTAMP WITH LOCAL TIMEZONE is one datatype
TIMESTAMP WITH TIMEZONE is (by its very definition) a different one

Otherwise, there would just be a single datatype

timestamp with some timezone




Hide day in date column

banyan384, May 24, 2011 - 4:49 pm UTC

For security purpose, I want to store just month and year instead of the day, month, year and time of date data type column.
I want to use date data type, but want to store month and year only.
I don't want to store 1st of each month also.Can we do any magic hear instead of converting date column to varchar2/number.?

Tom Kyte
May 25, 2011 - 11:21 am UTC

If you do not want to store the 1st of the month (or some other day), then you cannot use a date type.

However, I would encourage you to use the 1st if possible - it'll avoid lots of conversions in the future.

help this

venkata, May 30, 2011 - 10:47 am UTC

Hi Tom,

PS: don't say my code is....

i am trying to get date and time in zurich time format how can i do that. my current time is UK time.


Tom Kyte
May 31, 2011 - 10:55 am UTC

Venkata -

cool it with the "PS" stuff already. If you don't post something so horribly wrong, I won't say "I hate your code". That is all.


ops$tkyte%ORA11GR2> select sysdate,
  2         from_tz( cast(sysdate as timestamp), 'US/Eastern' ),
  3         from_tz( cast(sysdate as timestamp), 'US/Eastern' ) at time zone 'Europe/Zurich'
  4    from dual;

SYSDATE
---------
FROM_TZ(CAST(SYSDATEASTIMESTAMP),'US/EASTERN')
---------------------------------------------------------------------------
FROM_TZ(CAST(SYSDATEASTIMESTAMP),'US/EASTERN')ATTIMEZONE'EUROPE/ZURICH'
---------------------------------------------------------------------------
31-MAY-11
31-MAY-11 11.53.51.000000 AM US/EASTERN
31-MAY-11 05.53.51.000000 PM EUROPE/ZURICH



If you have a date datatype - cast as timestamp and tell us what timezone it is in and use "at time zone".

If you have a timestamp with timezone - just use "at time zone"

Some basic solutions

sftranna, February 29, 2012 - 12:22 am UTC

i found this url as listing many solution
check these as well
http://sqlexpertz.wordpress.com/2012/02/29/time-difference-between-two-date-columns/

Reader, March 19, 2012 - 10:41 am UTC

Tom,

I store time in EST in my table. GMT+10 is standard time and does not count for DST. Please correct me if I am wrong.

I used new_time to convert EST to GMT. Can you please tell me how to convert EST to GMT+10?

select sysdate,
new_time(sysdate,'est','gmt') est_gmt,
dbtimezone,
sessiontimezone
from dual


SYSDATE EST_GMT DBTIME SESSIONTIMEZONE
------------------------ ------------------------ ------ ----------------
19-MAR-12 11:39:42 19-MAR-12 16:39:42 -04:00 -04:00


Tom Kyte
March 19, 2012 - 8:45 pm UTC

I store time in EST in my table. GMT+10 is standard time and does not count for
DST. Please correct me if I am wrong.


I don't know what to say to that?

do you really store EST in your table - or - do you store whatever the OS returns at the current date/time which is subject to the plus or minus one hour shift? GMT is 'fixed', GMT+10 is 'fixed', EST is 'fixed' but most OS's are configured to return the time they see which can be EST or EDT depending on the time of the year.


that'll convert from est to gmt - but I'm not sure if that is what you really want or not.

using SYSDATE , but can't change timezone *help~

A reader, April 27, 2012 - 4:17 am UTC

hi tom,

i am hitting a serious problem.
throughout my applications in the db, i am using sysdate all over.

recently, we migrated to amazon cloud and their machines are running at UMT 0000 wheares we need it to be at +0800hr
and they are unable to change the machine date for us.

Understand that SYSDATE is getting its value from the OS time/date, thus there is no way i can amend the OS date/time.
--------------------------

Q1) should i change all my codes to SYSDATE+8/24 ?

or

Q2) is there anyway i can "hijack" any external or internal application that call SYSDATE in database to add +8 into the result ?

q3) what would you suggest ? do set session timezone to 0800 and use localtimestamp in replace of sysdate ?

but how do i make sure other web components wouldn't call SYSDATE, there are too much codes to change.

Regards,
Noob






Timezone issue

Deepak, December 10, 2012 - 5:49 am UTC

Hello Tom,

We also have similar issue related to time zone. Our client works in Indian time zone(IST) and Singapore time zone (SGT) currently as separate databases. And we want to migrate data from SGT time zone database to IST time zone database.

We exported data from SGT time zone database and imported the same in IST time zone database. We thought the data with SGT dates will be shown as IST dates but the database is imported as is.

Now our thinking is that
a. Treat IST data and SGT data separately. So this will be a problem if we change that for each connection in SGT and IST, for this we want to have 2 separate connection pooling for SGT and IST. For IST connection pooling no changes are required but for SGT connection pooling the OS time zone and database client time zone should be changed to SGT.

Please let us know whether or not our thinking is correct.

Thanks & Regards,
Deepak.

Tom Kyte
December 14, 2012 - 2:33 pm UTC

you don't even tell me what the datatype of the column was or anything :(


oracle data format

cynthia, March 22, 2013 - 11:26 am UTC

please i wish to know if my site could be connected with relevant answer on the topic stated above...www.unn.edu.ng.thanks
Tom Kyte
March 25, 2013 - 2:12 pm UTC

you can link to anything you want, it is the internet after all.

implicit conversion

Lal, August 30, 2013 - 1:10 pm UTC


Tom,
I have the following query which is executed from a java application using bind variables.

select col1,col2 from tab1 where dcol>?

The datatype of column dcol is date, also an index exists on tab1 table with dcol column.

The query does not use index, since the java team is setting a timestamp value for the bind variable, which causes an implicit datatype conversion.

The java team says, they can only set the value as a timestamp, if they need to pass a date and time value for the above query.

To resolve this one option is to convert the date column to a timestamp datatype.

My question is will there be any performance overhead of using a timestamp column over a date column.?

If not can i convert all date columns which stores time to a timestamp datatype rather than a date datatype?

Do you foresee any issues in this?
As always waiting for your expert opinion.

Tom Kyte
September 04, 2013 - 6:27 pm UTC

The java team says, they can only set the value as a timestamp, if they need to
pass a date and time value for the above query.


the java team is either lying or ignorant of how to do it.


of course they can bind a date - OF COURSE they can bind a date. OF COURSE they can. - geez - of course they can. ask them what happened in the 1990's when timestamp as a type didnt' even exist?????????? *of course they can* bind a date.



and binding a date is what they should do. please do not change your schema - teach them that 'binding a date is natural, normal, and correct'. do not accommodate them with a schema change - that would be a slippery slope.


at the very least, if they say "we only want to bind a timestamp" then have them:

where dcol > ( cast ? as date );



GMT Timezone

A Reader, January 30, 2014 - 11:46 am UTC

Hi Tom,

If the database timezone is set to GMT and if the below query is executed in SQL on the 1st of August 2014 (just an example date), what will be displayed?

01-AUG-2014 15:00:00 or 01-AUG-2014 14:00:00

Best Regards

GMT Timezone

A Reader, January 30, 2014 - 2:00 pm UTC

Apologies, please ignore my previous question as I had missed the important bits.

If the database timezone is set to GMT and if the below query is executed in SQL on the 1st of 
August 2014 at 3PM (UK will be in BST time)

SQL> celect sysdate from dual;

what will be displayed?

01-AUG-2014 15:00:00 or  01-AUG-2014 14:00:00

Best Regards

new link for timedate.zip

Praveen Ray, July 10, 2015 - 11:21 am UTC

Could you please, provide me the following .zip file for my curiosity:

http://asktom.oracle.com/~tkyte/timedate.zip

Imam, January 29, 2021 - 9:58 am UTC


Extract time zone from TIMESTAMP column

A reader, March 12, 2024 - 12:16 am UTC

I have inserted 'systimestamp' in TIMESTAMP(6) datatype column.
When i try to query the TIMESTAMP column as is and with 'at time zone 'America/New_York' , I am getting +1 hr for timezone.
Just wondering why this difference is.

Note: Database default uses US EDT time as the server is set to US/Eastern.





SQL> select
RECORDED_DATE_START,
RECORDED_DATE_START at time zone 'America/New_York' RECORDED_DATE_START_TZ
from t1_tab
/  2    3    4    5

RECORDED_DATE_START                                                         RECORDED_DATE_START_TZ
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
11-MAR-24 12.12.22.607188 PM                                                11-MAR-24 01.12.22.607188 PM AMERICA/NEW_YORK

SQL>
<code>




1* create table t1_tab(recorded_date_start TIMESTAMP(6))
SQL> /

Table created.

SQL> insert into t1_tab values(systimestamp) ;

1 row created.

SQL> commit ;

Commit complete.

SQL> select * from t1_tab ;

RECORDED_DATE_START
---------------------------------------------------------------------------
11-MAR-24 12.12.22.607188 PM

Connor McDonald
March 14, 2024 - 2:20 am UTC

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

When you inserted systimestamp into a "timestamp" column the time zone information was dropped/lost, so it will always come back as per the timezone that systimestamp had at insert time.

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

To be useful, a "timestamp" column needs a (single) implicit time zone maintained by your application. The recommended one is UTC, so '+00:00'. Systimestamp returns values in the system time zone, which may be sensitive to daylight-saving time changes. In autumn, you may see one hour repeated when you switch from summer to winter time. Such a repeated hour cannot be interpreted properly. Also, when you change the OS time zone, the systimestamp's time zone changes as well. This is not good for the already stored values. Hence the recommendation is to "assign" UTC as the implicit time zone.

If you decide to normalize "timestamp" column values to UTC, then you may populate the column using the expression SYSTIMESTAMP AT TIME ZONE '+00:00' and query it using the expression FROM_TZ(<column>, '+00:00') AT TIME ZONE <of your choice>.