Skip to Main Content
  • Questions
  • convert database server sysdate to GMT date and daylight saving aware

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Jessica.

Asked: April 22, 2002 - 9:35 am UTC

Last updated: April 15, 2021 - 9:55 am UTC

Version: 9.0.1

Viewed 50K+ times! This question is

You Asked

We need to code a function to convert database server sysdate to GMT date. So we use: alter database set time_zone='EST' to set dbtimezone, and then code something like:
CREATE OR REPLACE FUNCTION FN_GET_GMT_DATE RETURN DATE
IS

v_dbtimezone varchar2(10);

BEGIN
select dbtimezone into v_dbtimezone from dual;

RETURN NEW_TIME(SYSDATE, ltrim(rtrim(v_dbtimezone)), 'GMT');

END FN_GET_GMT_DATE;
/

But this is not daylight saving awarance. What's the best way to convert database sysdate to GMT date and also daylight saving aware?

and we said...

Here is one approach (there are probably others)

ops$tkyte@ORA9I.WORLD> select sysdate,
2 sysdate+(substr(tz_offset(dbtimezone),1,1)||'1')*to_dsinterval('0 '||substr(tz_offset( DBTIMEZONE ),2, 5)||':00')
3 from dual
4 /

SYSDATE SYSDATE+(SUBSTR(TZ_O
-------------------- --------------------
22-apr-2002 09:59:26 22-apr-2002 05:59:26




Rating

  (72 ratings)

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

Comments

I got ORA-01867: the interval is invalid when run the sql

Jessica, April 22, 2002 - 10:33 am UTC

When I run the sql you provide, I got ORA-01867: the interval is invalid. Could you tell me what's wrong?

Tom Kyte
April 22, 2002 - 10:44 am UTC

select out the bits and pieces to see what modifications we might have to accomidate, something like this:

ops$tkyte@ORA9I.WORLD> select tz_offset(dbtimezone) from dual;

TZ_OFFS
-------
-04:00

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> select '0 ' || substr(tz_offset(dbtimezone), 2, 5 ) || ':00' from dual;

'0'||SUBST
----------
0 04:00:00

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> select to_dsinterval( '0 ' || substr(tz_offset(dbtimezone), 2, 5 ) || ':00' ) from dual;

TO_DSINTERVAL('0'||SUBSTR(TZ_OFFSET(DBTIMEZONE),2,5)||':00')
---------------------------------------------------------------------------
+000000000 04:00:00.000000000

ops$tkyte@ORA9I.WORLD> select (substr(tz_offset(dbtimezone),1,1)||'1')*to_dsinterval( '0 ' || substr(tz_offset(dbtimezone), 2, 5 ) || ':00' ) from dual;

(SUBSTR(TZ_OFFSET(DBTIMEZONE),1,1)||'1')*TO_DSINTERVAL('0'||SUBSTR(TZ_OFFSE
---------------------------------------------------------------------------
-000000000 04:00:00.000000000


Thats how i pieced it together in the first place....

 

A reader, April 22, 2002 - 12:51 pm UTC

Is there a way to accomplish this in Oracle 8i.
tz_offset appears to be 9i function

Tom Kyte
April 22, 2002 - 8:46 pm UTC

Not that I am aware of. All of the timezone functionality is new with 9i.

Hope this assists -- a solution for 8i

Tom Wurzbach, April 22, 2002 - 11:50 pm UTC

We needed to do this with 8i -- what we did was place the server in GMT time and create a table:

create table timechange_tab(timezone VARCHAR2(10), gmt_from DATE, gmt_thru DATE, tzoffset NUMBER,
tzabbv VARCHAR2(3));
timezone = key for your timezones, Eastern, Central, Zulu, whatever...
gmt_from = the time, at GMT, at which the tz of interest begins in your timezone
gmt_thru = the time, at GMT, at which the tz of interest ends in your timezone
tzoffset = the time to add to GMT to get the localtime in your timezone
tzabbv = the timezone abbreviation EST, EDT, CDT, etc.

We loaded the table with the times (in GMT) that the times changed for each timezone, e.g.
...
INSERT INTO timechange_tab VALUES('Eastern', '01-APR-2002 07:00:01', '27-OCT-2001 06:00:00', -4/24, 'EST');
INSERT INTO timechange_tab VALUES('Eastern', '27-OCT-2001 06:00:01', '07-APR-2002 07:00:00', -5/25, 'EDT');
...

This allows you to select the current timezone offset, timezone abbreviation and current local time as such:
SELECT tzoffset, tzabbv, SYSDATE+tzoffset localtime
FROM timechange_tab
WHERE timezone='Eastern' AND (SYSDATE BETWEEN gmt_from AND gmt_thru);

We loaded our table with all US timezones and data through 2025--hopefully, I'll be at a different job before I need to add more dates to this table. I'm not at work, so check my numbers for tz start/stop times.

Hope this helps.

What about for existing applications eg: Workflow

Matt, October 20, 2003 - 3:48 am UTC

OK, so I can translate SYSDATE to my timezone and take account of daylight savings using your approach:

select sysdate,sysdate+(substr(tz_offset(dbtimezone),1,1||'1')*to_dsinterval('0'||substr(tz_offset( DBTIMEZONE ),2,5)||':00')
from dual
/

What if I don't have access to the source? For instance, I am using an application that uses Oracle Workflow, which uses SYSDATE, where we need to use "SYSDATE accounted for timezone and daylight savings".

Is this possible?

Basically, we have one machine, one instance, many application schemas, two timezones. We will have two instances of Oracle Workflow. The workflow code will need to use SYSDATE to set a date that accounts fot TZ and DST.

Any suggestions are gratefully received.

Tom Kyte
October 20, 2003 - 8:29 am UTC

I'm not following you here -- what are you trying to do? why does workflow need to use that?

More information...

Matt, October 21, 2003 - 7:57 pm UTC

Here is the situation. System that uses work flow in TZ1, the system processes data from two seperate timezones TZ2 and TZ3. This data arrives with time based data in the local TZ.

Workflow manages the processing of the data.

My question is:

Can the workflow be run in the timezone that different from the OS timezone?

I need to operate on date data as if it were either TZ2 or TZ3, depending on where the data came from.

The workflow background engine seems to use SYSDATE, which was why I raised the question here.

Thanks and Regards,

Tom Kyte
October 21, 2003 - 9:52 pm UTC

i would normalize all inputs to GMT -> local time. then there is no problem. that is, the guy who inputs the workflow -- puts it in in the local time of the DATABASE -- not the client. no worries then.

Fastest way from UTC -> Local time

steve, April 14, 2004 - 9:37 am UTC

Hi Tom,

We need to do the reverse on a huge table (20 million rows).
We need to convert a GMT date to local date.

What would be (one of) the fastest ways to convert
a DATE in GMT format to a local time (taking into account
daylight savings).

Thanks

Steve

Tom Kyte
April 14, 2004 - 10:52 am UTC

taking into account daylight savings.....

what version?

Oracle 9.2.0.1

steve, April 14, 2004 - 10:59 am UTC

Hi Tom,

Sorry about that. It's version 9.2.0.1

BTW: I tried the new_time(). It to 10 seconds for 10 million
rows. Wow. But it seems limited to Continental US and I'm
not sure whether or not it handles daylight savings time.
So I'm looking for another approach.

Steve

Tom Kyte
April 14, 2004 - 1:39 pm UTC

new_time is not tz aware particularly, you'd have to switch from tz to tz as DST kicked in:

ops$tkyte@ORA9IR2> select sysdate, new_time(sysdate,'gmt','edt'), new_time(sysdate,'gmt','est') from dual;
  
 
SYSDATE              NEW_TIME(SYSDATE,'GM NEW_TIME(SYSDATE,'GM
-------------------- -------------------- --------------------
14-apr-2004 11:37:30 14-apr-2004 07:37:30 14-apr-2004 06:37:30


would you be using a TIMESTAMP with TIMEZONE column?  cause we could just leave them as GMT and let the session/db timezone "fix them" on the way out.

 

Only a date column

steve, April 14, 2004 - 1:50 pm UTC

Hi Tom,

The source table has only a date column (no timezone information).

So would I have to, for each date, get the zone information
(e.g. EST in some cases, EDT in others) and then use the
new_time()? But then again, this would not work for other
parts of the world.

Any ideas?

Thanks for you help.

Steve



Tom Kyte
April 14, 2004 - 3:17 pm UTC

what do you mean "it would not work for other parts of the world" -- am I missing something?

If you have only a date column, you'll be converting ALL dates to some timezone -- a single timezone.

If you have people accessing from all over the world -- you don't want to convert into EST/EDT at all.

Can you elaborate?

I should have mentioned

Steve, April 14, 2004 - 3:02 pm UTC

I should have mentioned that the date field all come from
the same time zone region ('Mexico/General'). I realize
there are time differences within the country itself. So
we are willing to accept the fact that the report will all be
relative to one region (e.g. Mexico City).


I have tried:

new_time(answer_time, 'GMT',
to_char( from_tz(cast (ANSWER_TIME as timestamp),
'Mexico/General'),
'TZD' ) )


But I get:

ORA-01878: specified field not found in datetime or interval

for certain dates.

Thanks

Steve

Tom Kyte
April 14, 2004 - 3:44 pm UTC

can you do this but replace my create table t with

create table t as select distinct anwser_time DT from your_table;

it'll help isolate the issue

create table t
as
select to_date( '01-jan-1970' ) +rownum-1 dt
from all_objects
/


declare
dt date;
begin
for x in ( select * from t )
loop
begin
dt := new_time(x.dt, 'GMT', to_char( from_tz(cast (x.dt as timestamp), 'Mexico/General'), 'TZD' ) );
exception
when others then
dbms_output.put_line( x.dt || ' ' || sqlerrm );
end;
end loop;
end;
/

update t set dt = new_time(dt, 'GMT', to_char( from_tz(cast (dt as timestamp), 'Mexico/General'), 'TZD' ) );


Here are the results

steve, April 15, 2004 - 8:34 am UTC

Hi Tom,

Here are the results. All times that threw an exception
were between 2004-04-04 02:00:00 and 2004-04-04 02:59:59


--------------------------------------------------

SQL> create table t parallel nologging as 
     select distinct min_answer_time dt 
     from sub_small;

Table created.

Elapsed: 00:02:39.40


--------------------------------------------------

SQL> select count(*) from sub_small;

  COUNT(*)
----------
  10055628

Elapsed: 00:00:03.45


--------------------------------------------------


SQL> select count(*) from t;

  COUNT(*)
----------
   2165898

Elapsed: 00:00:00.66


--------------------------------------------------



SQL> declare
    dt date;
begin
   for x in ( select * from t )
   loop
       begin
           dt := new_time(x.dt, 'GMT', to_char( from_tz(cast (x.dt as 
timestamp), 'Mexico/General'), 'TZD' ) );
       exception
           when others then
               dbms_output.put_line( x.dt || ' ' || sqlerrm );
       end;
   end loop;
end;
/




2004-04-04 02:00:01 ORA-01878: specified field not found in datetime or interval
2004-04-04 02:00:02 ORA-01878: specified field not found in datetime or interval
.
.
.
2004-04-04 02:59:53 ORA-01878: specified field not found in datetime or interval
2004-04-04 02:59:59 ORA-01878: specified field not found in datetime or interval
 

Tom Kyte
April 15, 2004 - 8:52 am UTC

ahh, sort of makes sense.

we are giving it a timestamp without a timezone.

and asking "what timezone is it"

between 2am and 3am

on the day the clocks change.

the problem is that could be either or -- doesn't know if that should be 2am or 3am in that case.

Guess you could use CASE to isolate these

set c = case when dt between 'bad time' and 'bad time' then assign the tz you want
else new_time......

local time to GMT and GMT to local time conversion usind dbTimeZone

pasko, April 21, 2004 - 4:18 am UTC

Hi Tom,
thanks for the great support you are providing to the Oracle people all around the world !

refering to your first answer to this thread, you used
tz_offset and dbtimezone Function.
From Oracle Globalization Docs, quote:

"The database time zone is relevant only for TIMESTAMP WITH LOCAL TIME ZONE columns. Oracle normalizes all TIMESTAMP WITH LOCAL TIME ZONE data to the time zone of the database when the data is stored on disk. If you do not specify the
SET TIME_ZONE clause, then Oracle uses the time zone of the operating system of the server. If the operating system’s time zone is not a valid Oracle time zone, then the rdatabase time zone defaults to UTC. Oracle’s time zone information is derived from the public domain time zone data available at </code> ftp://elsie.nci.nih.gov/pub/ <code>Oracle’s time zone information may not reflect the most recent time zone data available from this site."

so this means that the usage of tz_offset from dbtimezone is not very reliable if the "SET TIME_ZONE clause" was not used during Database Creation.

Qn.
1.what other options do we have if our requirement is to do the local time to GMT conversion irrespective of dbtimezone and sessiontimezone and that only the operating system time zone should be considered ?
--i note that systimestamp has some time_zone info too, can we use that ? is it guaranteed to always return the OS time zone of the Database server machine ?

2.Quote from Oracle Globalization Docs:
"Two time zone files are included in the Oracle home directory. The default file is oracore/zoneinfo/timezone.dat. It contains the most commonly used time zones. A larger set of time zones is included in
oracore/zoneinfo/timezlrg.dat.
Unless you need the larger set of time zones, use the default time zone file because database performance is better."

3.
what performance implications can be anticipated if we change the default time zone fileto be:
oracore/zoneinfo/timezlrg.dat. ?
is it only for date functions or for the whole Database ?

4.
Do you recommend this approach of changing the Default time zone File ?
i am asking this because using your tz_stuff package from this site , i saw a time zone named: 'Europe/Berlin' ,
but this time zone can not be seen from the Oracle view:
V$TIMEZONE_NAMES.

5.
I know that you are not a Time Zone expert :) but where do we get info about which time zones support day light savings time automatically, for example for a country like Germany.


thanks in advance.






Tom Kyte
April 21, 2004 - 8:07 pm UTC

1) the operating system timezone would be showing at an offset from gmt, you could use that.

3) benchmark it and let us know! set up a test to see if it would affect you in your use of it.

4) recommend which/what approach? I would recommend using the builtin functionality.

5) google it :)

FROM_TZ does not work properly for Boundery Conditions

pasko, April 22, 2004 - 11:45 am UTC

Hi Tom ,
Thanks very much for your answer .
my DBA has just installed the larger time zone file , and the difference is not that big( i mean number of rows )

SQL> select count(*) from v$timezone_names ;

  COUNT(*)
----------
      1286

SQL> select count(*) from v$timezone_names@database_with_smaller_file  ;

  COUNT(*)
----------
       622

I got the time zone that i was looking for though :)

SQL> SELECT * FROM v$timezone_names  WHERE tzname LIKE 'Europe/Berlin' ;

TZNAME               TZABBREV
-------------------- ----------
Europe/Berlin        LMT
Europe/Berlin        CET
Europe/Berlin        CEST
Europe/Berlin        CEMT

so i thought , Europe/Berlin would be DST  aware , because in Germany we use CEST in summer and CET in winter.

but from_tz Function disappointed me because it didn't work within the Boundary Dates:

for example for 2004 : boundary Dates  are:
2004-03-28 02:00:00   and  2004-10-31 03:00:00

Trying this out with the built-in FROM_TZ function :

SQL> CREATE OR REPLACE FUNCTION to_gmt (
  2         p_date IN DATE,      -- Local   datetime
  3         p_tzr  IN VARCHAR2   -- timezone name
  4         ) RETURN DATE        --  GMT   datetime
  5  AS
  6    v_localts   TIMESTAMP WITH TIME ZONE;
  7  BEGIN
  8     v_localts   :=     FROM_TZ(CAST(p_date AS TIMESTAMP)  ,   p_tzr       ) AT TIME ZONE   'Gree
nwich'   ;
  9  
 10     RETURN TO_DATE(TO_CHAR(v_localts,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DDHH24:MI:SS');
 11  
 12  END to_gmt  ;
 13  /

Funktion wurde erstellt.

SQL> SELECT  TO_DATE('2004-03-28 02:00:01',   'YYYY-MM-DD HH:MI:SS')  between_boundary   ,  
  2  to_gmt(     TO_DATE('2004-03-28 02:00:01',   'YYYY-MM-DD HH:MI:SS')    , 'Europe/Berlin')  gmt 

  3  FROM dual ;
to_gmt(     TO_DATE('2004-03-28 02:00:01',   'YYYY-MM-DD HH:MI:SS')    , 'Europe/Berlin')  gmt
*
FEHLER in Zeile 2:
ORA-01878: specified field not found in datetime or interval
ORA-06512: at "WEBMON.TO_GMT", line 8

More Tests:

SQL> SELECT * FROM    TBL_TIMEZONE_FLIPS_GER WHERE yyyy = '2004' ;

YYYY JUST_BEFORE_SUMMER  BEGIN_SUMMER        JUST_BEFORE_WINTER  END_SUMMER          JUST_AFTER_END_SUMM
---- ------------------- ------------------- ------------------- ------------------- -------------------
2004 2004-03-28 01:59:59 2004-03-28 02:00:00 2004-10-31 02:59:59 2004-10-31 03:00:00 2004-10-31 03:00:01


SQL> SELECT
  2  just_before_winter ,
  3  To_Gmt(  just_before_winter , 'Europe/Berlin')   gmt ,
  4  (  just_before_winter - To_Gmt(  just_before_winter , 'Europe/Berlin'  ) )  * 24  AS   diff2
  5  FROM   TBL_TIMEZONE_FLIPS_GER
  6  WHERE yyyy = '2004' ;

JUST_BEFORE_WINTER  GMT                      DIFF2
------------------- ------------------- ----------
2004-10-31 02:59:59 2004-10-31 01:59:59          1   <<<<  Offset should have been 2 Hours here !


 

Tom Kyte
April 23, 2004 - 8:21 am UTC

hows about you just use sql (makes the example more compact) and you include all of the steps (create table, insert data), and you do it piece by piece by piece so we can see where you think it is going wrong exactly (eg: print out more information)

pasko, April 24, 2004 - 3:17 pm UTC

Hi Tom.
here is a Summary of the Tests that i did,may be it will be
a bit clear of what errors i am getting.

First the Winter/Summer Time Zones for Germany.

---------------------------------------------------------
TZ GMT From Date - EndDate
---------------------------------------------------------
CET GMT+01 01. January - 28. March 02:00
CEST GMT+02 28. March 02:00 - 30. October 03:00
CET GMT+01 30. October 03:00 - 31. December
---------------------------------------------------------
General TZ Rule In Germany:
Begin Summer = Last Sunday of March , 02:00
Begin Normal Time = Last Sunday of September , 03:00
---------------------------------------------------------

To get the Test table I just did some selects using
next_day( last_day( sysdate – rownum ) , ‘Sunday’)
and filtered rows for March and October to get Boundary Values.Then subtracted 1 second from the Boundary Dates to get the other just_after*** and just_before*** Columns.

--I do not remember the Query now(not at work) , but in that
regard I know you always have a better Query than mine :)

---------------------------------------
Here is a Summary of My Tests:
---------------------------------------

Test Case 1.( Test for Dates just before Summer: )
Test Date: 2004-03-28 01:59:59
Expected Time: 2004-03-28 01:59:59 - 1/24
Result using FROM_TZ Function: = 2004-03-28 01:59:59 - 1/24
Conclusion: it worked fine!


Test Case: 2.( Test the skipped 1 Hour interval )
Begin Summer at 02:00 advance Clocks to 03:00

From the TZ table above:
anything between 2004-03-28 02:00:00 and 2004-03-28 02:59:59
should never happen in Germany , because of the 1 Hour skipped.

So , using the FROM_TZ function for any dates from
2004-03-28 02:00:00 and 2004-03-28 02:59:59 gives the error:

“ORA-01878: specified field not found in datetime or interval”


Test Case 3 ( from_tz worked but it game a wrong GMT Summer Time)

Winter Time begins again on : 30. October 03:00

So I tested the just before Date: i.e
--------------------------------------------------
Test Date: = 2004-10-31 02:59:59
Expected Time GMT: = 2004-10-31 02:59:59 - 2/24
Result using FROM_TZ Function: = 2004-10-31 01:59:59
Conclusion: Wrong Result:
---------------------------------------------------

The Function from_tz to GMT gave me: 2004-10-31 01:59:59
which is wrong because we are still in summer on 2004-10-31 02:59:59 and that should have been:
2004-10-31 02:59:59 - 2/24 = 2004-10-31 00:59:59

----------------------------------------------------------

what we thought afterwards is create a Function that would look like this:

-----------------------------------------------------------
--convert p_date to GMT Date
function local_2_gmt( p_date IN date --local TZ date )
return date --in GMT
is
l_sunday varchar2(30) :=
to_char( to_date( '18042004','DDMMYYYY') , 'DAY') ;
--your trick :)

l_dateGMT date ;
l_begin_summer date ;
l_end_summer date ;
l_yyyy varchar2(10) := to_char(p_date ,'YYYY') ;

begin
--get last Sunday in March. Summer begins at: 02:00:00
--get older for 1 Hour:) 02:00:00 -> 03:00
l_begin_summer :=
next_day (
to_date( '3103'||l_yyyy||'02:00:00' , 'DDMMYYYYHH24:MI:SS')
- 7 ,
l_sunday ) ;

--get last Sunday in October: Winter begins at: 03:00:00
--get younger again:) 03:00:00 -> 02:00:00
l_end_summer :=
next_day (
to_date( '3110'||l_yyyy||'03:00:00' , 'DDMMYYYYHH24:MI:SS'),
- 7 ,
l_sunday ) ;


if ( p_date between l_begin_summer and
l_end_summer - 1/24/60/60 )
then
dateGMT := p_date - 2/24 ;
--GMT in Summer including 28/03/YYYY 02:00:00 - 02:59:59 )
else
dateGMT := p_date - 1/24 ;
--GMT in winter
end if ;

return dateGMT ;

exception when others
then
raise ;

end local_2_gmt ;


So using this function we will convert all our Dates to GMT.

i hope my Logic to get the Last Sundays in March and October is correct .











Tom Kyte
April 26, 2004 - 5:26 am UTC

if the october 31'st returned the 'wrong answer', that would be bug in the lookup tables -- please file a tar with support on that to get it fixed for 'real'

a liltle bot of correction ...

pasko, April 24, 2004 - 3:21 pm UTC

sorry i made a Typo:)

---------------------------------------------------------
General Winter/Summer Time Conversion Rule In Germany:
Begin Summer = Last Sunday of March , 02:00
Begin Normal Time = Last Sunday of October , 03:00
---------------------------------------------------------

Tom Kyte
April 26, 2004 - 5:29 am UTC

hold on, last sunday in october is the 31st -- not the 30th.

the time, without tz, will get your current timezone. you gave it a time that could happen 'twice' that night. i've a feeling that function will return different answers during different parts of the year (and it is in fact 'correct')

TZ

VA, August 03, 2005 - 5:18 pm UTC

On Solaris 8, my /etc/default/init file has

TZ=US/Eastern

When I do 'date', I get

Wed Aug 3 17:07:00 EDT 2005

(There is no offset from GMT, just the absolute time in EDT)

On Oracle (9.2.0.6), when I do

select dbtimezone from dual, I get '+00:00'

But when I do

select sysdate,new_time(sysdate,'edt','gmt') from dual, I get

8/3/2005 5:13:42 PM 8/3/2005 9:13:42 PM

1. How does Oracle know that EDT+4hours=GMT, is it hardcoded into the Oracle software?

2. How can I set things so that any Oracle clients connecting to this database always get the SYSDATE back in GMT and not EDT?

Thanks

Tom Kyte
August 03, 2005 - 8:23 pm UTC

1) the database knows what timezones are, it has the timezone tables and support for them.

2) I assume you are using 9i? but anyway, set your OS to be in "GMT". Individuals logging into the machine that don't want GMT can set TZ in their .files to set up their environment.

Set OS to GMT

VA, August 03, 2005 - 9:18 pm UTC

Well, I dont really need to set the TZ to GMT for the entire machine (in /etc/default/init), just

export TZ=GMT;dbstart force

should suffice, right?

Would this mean that any clients connecting to this database always get GMT time? Can this be overriden on a per-client basis using Windows registry entries or ALTER SESSION commands?

Thanks





Tom Kyte
August 04, 2005 - 8:14 am UTC

yes, but would be confusing (in my opinion)

you give me NO VERSION info, 9i, you have more sophisticated timezone support you can look at (including the concept of a client timezone separate and distinct from the server timezone)

VA, August 04, 2005 - 9:16 am UTC

Why would it be confusing to do

export TZ=GMT;dbstart force

Why change the entire server to GMT if just Oracle needs it?

I gave you version info right there

"On Oracle (9.2.0.6), when I do ..."

Given that, how can I do ...

"Would this mean that any clients connecting to this database always get GMT time? Can this be overriden on a per-client basis using Windows registry entries
or ALTER SESSION commands?"

Thanks



Tom Kyte
August 04, 2005 - 9:57 am UTC

Because the database server is the database server, it is a database server right?

I'd rather have timestamps on things all be the same -- not have the alert log be timezone A, /var/log/messages be timezone B and so on.

See the alter session command in the doc's -- yes, you can set a client timezone. Windows registry? don't know about that thing.

VA, August 04, 2005 - 10:55 am UTC

Good point about alert logs showing time in GMT vs system logs showing time in EDT, didnt think about that.

Regarding the session time zone, I cant seem to get it work.

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

SQL> select sessiontimezone,dbtimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
DBTIME
------
-04:00
+00:00


SQL> col sessiontimezone format a10
SQL> /

SESSIONTIM DBTIME
---------- ------
-04:00     +00:00

SQL> alter session set nls_date_format='mm/dd/yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
08/04/2005 10:48:08

SQL> alter session set time_zone='GMT';

Session altered.

SQL> select sessiontimezone,dbtimezone from dual;

SESSIONTIM DBTIME
---------- ------
GMT        +00:00

SQL> select sysdate from dual;

SYSDATE
-------------------
08/04/2005 10:48:24

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
04-AUG-05 10.50.03.728496 AM -04:00


Why does my sysdate (or even systimestamp) not change when I change my sessions timezone?

Yes, I see 

SQL> SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;
SESSIONTIM CURRENT_TIMESTAMP
---------- -----------------------------------
GMT        04-AUG-05 02.51.40.470715 PM GMT

That does show GMT, but current_timestamp is a brand-new 9i function. Most apps out there will use SYSDATE and expect to the time in GMT.

Thanks 

Tom Kyte
August 04, 2005 - 11:49 am UTC

sysdate will forever and always be "sysdate, the way it was". That is controlled by the timezone on the server. it is not timezone "aware"

VA, August 04, 2005 - 1:17 pm UTC

"That is controlled by the timezone on the server"

SYSDATE issues a "date" system call to the OS, right?

If I do

$ date
Thu Aug 4 12:59:17 EDT 2005
$ (export TZ=GMT;date)
Thu Aug 4 16:59:24 GMT 2005
$ (export TZ=GMT;dbstart force)

Shouldnt SYSDATE issue a date system call to the OS and get the GMT time as I show above?

Thanks

Tom Kyte
August 04, 2005 - 1:54 pm UTC

no, you changed your environment, not the already up and running Oracle environment.

Now -- this gets tricky, because if you used a DEDICATED SERVER, it would - since YOU fork off the dedicated server, it picks up YOUR environment.

However, if you use the listener, it would not, since the listeners environment is already set and in place and the dedicated server gets the environment from the listener, not you.

and further, if you use shared server -- well, changing YOUR environment or the LISTENERS environment wouldn't count because it is the database servers environment that matters than.

Reason #443432 for having the MACHINE have the timezone you desire. mucking the environment like this is just going to leave people scratching their heads all day.

Changing the O/S time

Kim, August 08, 2005 - 8:53 am UTC

Hi Tom

Can changing the O/S time (not zone) affect an up and running database (8i, 9i, 10G). ?
- Stability, Data (Mviews refresh fx.) or other wise?




Tom Kyte
August 08, 2005 - 7:45 pm UTC

it'll change SYSDATE, anything relying on SYSDATE will be affected. typically going forward is OK in general (sort of like being shutdown).

Going back happens once a year for most people already -- on the scope of an hour -- which typically doesn't affect any/much (well point in time recovery that is done not by SCN but...)




report based on Time in various zones

OracleO, October 04, 2005 - 4:04 am UTC

Hi,

I have a scenario based on different timezones.

I have a table in which rows are inserted from various regions in the world. Every time a row is inserted or updated we take the system time stamp and mark that row with sysdate to determine when the row was last updated or inserted.

Instance, if the row is updated in Australia or Hongkong, i would still save the GMT time in my DB and will be able to show the guy in australia(calculate backwards), when the row was actually inserted on based of client time zone. I believe, this should be doable and should not be difficult.

Now if a user sitting in India runs the same report, will obviously get the timestamp as of India time and will have to do manual calculations to know when in australia or hongkong (as per the actual user) this was actually done.

One thought, to have locale column and calcualte time based on that. that would mean if a guy in australia modified the record the time and zone in two columns and show that in reports.
your thoughts on this.
Hope i am clear.



Tom Kyte
October 04, 2005 - 2:19 pm UTC

i'm not sure what the goal is -- if you use 9i and above, timezone support is there.

Is your requirement to show the person in india

a) the time the row was updated in india time AND
b) the time the row was updated in the timezone of the original user?

if so, put the timestamp in there with the end users local timezone - so the audit timestamp would be in australian time - and when the india user retrieves it - they can see the australian time and convert it to UTC or whatever other timezone they would like.

that way you don't have to have yet another column to say what the original timezone was, you don't need that.

Your thread with "VA" was wonderful!

Bernice, October 14, 2005 - 12:49 pm UTC

It is exactly what I have been looking for! Thank you!

Quick follow-up. The server was originally running with GMT. But the users want the timestamp to read PST (the db holds files and timestamps when the files were uploaded to the database; the timestamps are reading GMT). If I find a way to change the server's timezone, would that confuse the database (for the 7-8 hrs it will 're-experience' because of the changing from GMT -> PDT/PST)? This is a 9.2.0.6 database on a linux RHEL3 OS.

e.g

File Input time
------ ------------
1 Oct 14, 2005 16:41:41

--> now, do time change; so, the current time will be Oct 14, 2005 9:41:41
I assume the values already in the database will stay at GMT. But would anything be confused during the 7 hours that will essentially repeat? (will see time entries in alert log that will repeat, etc)

thanks!
(btw. We are currently testing this app and none of the data is live. So, we are ok).




Tom Kyte
October 14, 2005 - 5:32 pm UTC

Yes, it could see duplicate times since the DATE type doesn't have any timezone information at all.

are you on 9i? why not just use timezone functions to display the data to them in any timezone they want? (or see if new_time() doesn't suite your current needs)

Bernice, October 14, 2005 - 5:42 pm UTC

Hi Tom,

It's a 9i database but it is a 3rd party software so I have no control over the code. The application is also one of the "database independent" applications (cringe). So, I doubt they are using any of the oracle-specific timezone support.
Since all our users are on the PST timezone, I was thinking the brute force way would better (setting the dbserver and thus everything in oracle to PST).

thank you,
Bernice


Tom Kyte
October 14, 2005 - 6:15 pm UTC

well, it could really screw up the third party application.

they are likely NOT expecting time to "go backwards" like that.. I'd want to test out the ramifications first.




Now, totally confused

Bernice, October 14, 2005 - 8:31 pm UTC

Hi Tom,

Don't worry - we have a test system that we can mess with.

But here's the kicker - I went down to the server room and logged into the machine as root to look at the Time Zone Configuration (and getting ready to update the server timezone).

Well, based on the linux gui tool, it claims the server is running at Pacific time!!! And as root, indeed, if you issue 'date', you see the time in PDT.

But as the 'oracle' user, if you issue 'date', you see the time in UTC (no TZ set).

Have you ever seen this before?? I am pulling out my hair and googling for info about that. The 'oracle' user was added using the 'useradd' utility.

If this seems obvious to you, please let me know. If not, I will try to find our unix admin and see if we can open a case with RedHat. If I have anything interesting, I will post on this thread.


Followup - timezone resolution

Bernice, November 01, 2005 - 12:55 pm UTC

For those of you following my thread.
I found the resolution in my case. If you see a similar phenomena, you can try this (but please, do this on your TEST SYSTEM first AND with the blessing from your sys-admin who has a good backup of the system).

Summary of my problem: system timezone is properly set to Pacific Time (PST) but all users other than root sees GMT. What is the cause?


First make sure your system's timezone is properly set as described here
</code> http://kbase.redhat.com/faq/FAQ_35_3981.shtm <code>

Then, check the permissions on /etc/localtime
Insure it is 644 (-rw-r--r--).

Also, redhat support is pretty good - if these steps don't resolve your problem, contact them!



Tom Kyte
November 02, 2005 - 4:52 am UTC

thanks, really appreciate the followup

Time zone conversion from EST to UTC

A reader, November 30, 2005 - 12:22 pm UTC

Tom,

When i tried to convert sysdate from 'EST' to 'UTC', it does not work but conversion 'EST' to 'GMT' works.

IS UTC not a valid timezone recognized by Oracle ?

1* SELECT to_char(new_time(sysdate,'EST','UTC'), 'MM/DD/YY HH24:MI:SS') from dual
/
SELECT to_char(new_time(sysdate,'EST','UTC'), 'MM/DD/YY HH24:MI:SS') from dual
*
ERROR at line 1:
ORA-01857: not a valid time zone


SELECT to_char(new_time(sysdate,'EST','GMT'), 'MM/DD/YY HH24:MI:SS') from dual ;

TO_CHAR(NEW_TIME(
-----------------
11/30/05 17:06:32


Tom Kyte
November 30, 2005 - 8:33 pm UTC

new_time is very very limited in its recognition of timezones - however, in 9i the timestamp with timezone type has comprehensive coverage.

Thank you

A reader, November 30, 2005 - 9:24 pm UTC


java time

Mahomed Suria, April 04, 2006 - 6:17 am UTC

Hi Tom,

I am using java to get the time from the OS using following (thanks to the wealth of information on this site):

CREATE OR REPLACE and COMPILE JAVA SOURCE
NAMED "MyTimestamp"
AS
import java.lang.String;
import java.sql.Timestamp;

public class MyTimestamp
{
  public static String getTimestamp()
  {
    return ( new Timestamp(System.currentTimeMillis())).toString();
  }
};
/
create or replace function javatimestamp
return varchar2
AS LANGUAGE JAVA
NAME 'MyTimestamp.getTimestamp() return java.lang.String';
/

However, I find that javatimestamp is 1 hour behind at present ( summer time in UK).
My operating sytem is HP-UX B.11.23 U ia64 and I am using 9.2.0.2.0. The environment variable TZ is set to GMT0BST.


SQL> select sysdate from dual;

SYSDATE
--------------------
04-APR-2006 10:46:24

SQL> select javatimestamp from dual;

JAVATIMESTAMP
--------------------------------------------------------------------------------
2006-04-04 09:46:35.3

I feel it is a setting that I am missing somewhere. Any help will be welcome.

Thanks


 

Tom Kyte
April 04, 2006 - 9:55 am UTC

just use systimestamp - you don't need to use this in 9i.


that would indicate the environment on the server isn't set up right - the TZ setting or something is wrong.

Java time - followup

Mahomed Suria, April 05, 2006 - 3:52 am UTC

The reason for not using systimestamp is that I am using this in an event logging package and the source is shared for V8 as well.

However, I have noticed that it works fine on an Alpha where the JServer version on that system is 9.2.0.4. So I may possibly need to apply the patches to bring it up to that version.

Tom Kyte
April 05, 2006 - 5:49 pm UTC

no, nothing to do with versions

everything to do with the timezone settings in the environments.


As the hit for calling java is rather hugely high, I would suggest having two implementations here - the slow one for software written last century and the fast one for software written this century. use systimestamp when you can

Is this quey foolproof?

Niks, April 10, 2006 - 2:35 pm UTC

I want to calculate GMT time taking into account the daylight saving also. The following query is based on your first response. The only difference is that I have used to_char(systimestamp,'tzh:tzm') intead of tz_offset. 
(My oracle version - Release 9.2.0.5.0)

select to_char(sysdate, 'MM-DD-YYYY HH24:MI:SS') LOCALDATE, to_char(sysdate + -1* (substr(to_char(systimestamp,'tzh:tzm'),1,1)||'1') * to_dsinterval('0 '||substr(to_char(systimestamp,'tzh:tzm'),2, 5)||':00'),'MM-DD-YYYY HH24:MI:SS') GMTTIME from dual;

LOCALDATE           GMTTIME
------------------- -------------------
04-10-2006 13:07:55 04-10-2006 18:07:55


The reason for not useing tz_offset is that it shows me standard time offset and not the daylight time offset. I am in central time and currently the daylight saving is in effect and so GMT offset is -05:00.
When i execute the following query i get -06:00. For calculation i need -05:00 considering the daylight saving.

SQL> select tz_offset(dbtimezone) from dual;

TZ_OFFS
-------
-06:00

This query gives me the desired result
SQL> select to_char(systimestamp,'tzh:tzm') from dual;

TO_CHA
------
-05:00

My question is - Is this approach foolproof? Will it always give me correct result irrespective of Daylight saving?

Thanks a lot for your help.

 

Tom Kyte
April 11, 2006 - 11:36 am UTC

Think you mean UTC - GMT is 'similar' but slightly different.


my offset accurately reflects -04:00 instead of -05:00 since we are in EDT now?

but since you are using just a DATE, why not new_time?  It handles cst and cdt well.

or sys_extract_utc


ops$tkyte@ORA10GR2> select localtimestamp, sys_extract_utc(localtimestamp) from dual;
 
LOCALTIMESTAMP
---------------------------------------------------------------------------
SYS_EXTRACT_UTC(LOCALTIMESTAMP)
---------------------------------------------------------------------------
11-APR-06 11.07.48.605845 AM
11-APR-06 03.07.48.605845 PM
 
 
ops$tkyte@ORA10GR2> !date; date -u
Tue Apr 11 11:08:00 EDT 2006
Tue Apr 11 15:08:00 UTC 2006
 

Michel Cadot, April 11, 2006 - 12:35 pm UTC


Thanks a lot

Niks, April 12, 2006 - 1:13 pm UTC

Thanks for the suggestion.

GMT offset puzzling

Robert Hanrahan, April 21, 2006 - 6:38 am UTC

I have an issue regarding mktime and GMT offset. I use a partitioned table (instead of deleting rows I drop the oldest partition) with the high_value set to mktime on the column STARTPROCESS:

CREATE TABLE  T 
(
  TASKID           NUMBER                       NOT NULL,
  SOURCENAME       VARCHAR2(255 BYTE)           NOT NULL,
  SOURCETYPE       NUMBER                       NOT NULL,
  REPOSITORYTYPE   NUMBER                       NOT NULL,
  CRC1             NUMBER                       NOT NULL,
  CRC2             NUMBER                       NOT NULL,
  RECORDPROCESSED  NUMBER,
  RECORDFILTERED   NUMBER,
  RECORDANOMALOUS  NUMBER,
  RECOVERYOFFSET   NUMBER,
  STARTPROCESS     NUMBER,
  ENDPROCESS       NUMBER,
  COMPLETEPROCESS  CHAR(1 BYTE),
  REPOSITORYNAME   VARCHAR2(255 BYTE)
)
PARTITION BY RANGE (STARTPROCESS) 
(  
  PARTITION PART01 VALUES LESS THAN (1144620000)
    LOGGING
    NOCOMPRESS,  
  PARTITION PART02 VALUES LESS THAN (1144706400)
    LOGGING
    NOCOMPRESS,  
  PARTITION PART03 VALUES LESS THAN (1144792800)
    LOGGING
    NOCOMPRESS,  
  PARTITION PART04 VALUES LESS THAN (1144879200)
    LOGGING
    NOCOMPRESS,  
  PARTITION PART05 VALUES LESS THAN (1144965600)
    LOGGING
    NOCOMPRESS,  
  PARTITION PART06 VALUES LESS THAN (1145052000)
    LOGGING
    NOCOMPRESS,  
  PARTITION PART07 VALUES LESS THAN (1145138400)
    LOGGING
    NOCOMPRESS
)
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING
ENABLE ROW MOVEMENT;

CREATE INDEX T_IDX ON T
(REPOSITORYTYPE, SOURCETYPE, CRC1, CRC2)
  INITRANS   2
  MAXTRANS   255
LOGGING
LOCAL (  
  PARTITION PART05
    LOGGING
    TABLESPACE MODULES_IDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION PART06
    LOGGING
    TABLESPACE MODULES_IDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION PART07
    LOGGING
    TABLESPACE MODULES_IDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION PART01
    LOGGING
    TABLESPACE MODULES_DATA
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION PART02
    LOGGING
    TABLESPACE MODULES_DATA
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION PART03
    LOGGING
    TABLESPACE MODULES_DATA
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION PART04
    LOGGING
    TABLESPACE MODULES_DATA
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                BUFFER_POOL      DEFAULT
               )
)
NOPARALLEL;

INSERT INTO  T  ( TASKID, SOURCENAME, SOURCETYPE, REPOSITORYTYPE, CRC1, CRC2,
RECORDPROCESSED, RECORDFILTERED, RECORDANOMALOUS, RECOVERYOFFSET, STARTPROCESS, ENDPROCESS,
COMPLETEPROCESS, REPOSITORYNAME ) VALUES ( 
7, '2', 2, 1, 7, 1, 2, 22, 2, 2, 1144101599, 1142981999, 'N', 'TEST'); 
INSERT INTO  T  ( TASKID, SOURCENAME, SOURCETYPE, REPOSITORYTYPE, CRC1, CRC2,
RECORDPROCESSED, RECORDFILTERED, RECORDANOMALOUS, RECOVERYOFFSET, STARTPROCESS, ENDPROCESS,
COMPLETEPROCESS, REPOSITORYNAME ) VALUES ( 
6, '2', 2, 1, 1, 1, 2, 22, 2, 2, 1144187999, 1142981999, 'N', 'TEST'); 
INSERT INTO  T  ( TASKID, SOURCENAME, SOURCETYPE, REPOSITORYTYPE, CRC1, CRC2,
RECORDPROCESSED, RECORDFILTERED, RECORDANOMALOUS, RECOVERYOFFSET, STARTPROCESS, ENDPROCESS,
COMPLETEPROCESS, REPOSITORYNAME ) VALUES ( 
5, '2', 2, 1, 2, 2, 2, 22, 2, 2, 1144274399, 1142981999, 'N', 'TEST'); 
INSERT INTO  T  ( TASKID, SOURCENAME, SOURCETYPE, REPOSITORYTYPE, CRC1, CRC2,
RECORDPROCESSED, RECORDFILTERED, RECORDANOMALOUS, RECOVERYOFFSET, STARTPROCESS, ENDPROCESS,
COMPLETEPROCESS, REPOSITORYNAME ) VALUES ( 
4, '2', 2, 1, 3, 1, 2, 22, 2, 2, 1144360799, 1142981999, 'N', 'TEST'); 
INSERT INTO  T  ( TASKID, SOURCENAME, SOURCETYPE, REPOSITORYTYPE, CRC1, CRC2,
RECORDPROCESSED, RECORDFILTERED, RECORDANOMALOUS, RECOVERYOFFSET, STARTPROCESS, ENDPROCESS,
COMPLETEPROCESS, REPOSITORYNAME ) VALUES ( 
3, '2', 2, 1, 4, 1, 2, 22, 2, 2, 1144447199, 1142981999, 'N', 'TEST'); 
INSERT INTO  T  ( TASKID, SOURCENAME, SOURCETYPE, REPOSITORYTYPE, CRC1, CRC2,
RECORDPROCESSED, RECORDFILTERED, RECORDANOMALOUS, RECOVERYOFFSET, STARTPROCESS, ENDPROCESS,
COMPLETEPROCESS, REPOSITORYNAME ) VALUES ( 
2, '2', 2, 1, 5, 2, 2, 22, 2, 2, 1144533599, 1144533599, 'N', 'TEST'); 
INSERT INTO  T  ( TASKID, SOURCENAME, SOURCETYPE, REPOSITORYTYPE, CRC1, CRC2,
RECORDPROCESSED, RECORDFILTERED, RECORDANOMALOUS, RECOVERYOFFSET, STARTPROCESS, ENDPROCESS,
COMPLETEPROCESS, REPOSITORYNAME ) VALUES ( 
1, '2', 2, 1, 6, 1, 2, 22, 2, 2, 1144619999, 1142981999, 'N', 'TEST'); 
COMMIT;

Analyze Table TIM_IT. T  partition (PART01) Estimate Statistics Sample 50 Percent;
Analyze Table TIM_IT. T  partition (PART02) Estimate Statistics Sample 50 Percent;
Analyze Table TIM_IT. T  partition (PART03) Estimate Statistics Sample 50 Percent;
Analyze Table TIM_IT. T  partition (PART07) Estimate Statistics Sample 50 Percent;
Analyze Table TIM_IT. T  partition (PART05) Estimate Statistics Sample 50 Percent;
Analyze Table TIM_IT. T  partition (PART06) Estimate Statistics Sample 50 Percent;
Analyze Table TIM_IT. T  partition (PART04) Estimate Statistics Sample 50 Percent;

This way I have 1 row per partition.
I have a function which translate the date in mktime:

create or replace FUNCTION plmktime (DATAIN DATE)
      RETURN INTEGER IS
 BEGIN
 RETURN  TO_NUMBER (TO_CHAR (DATAIN, 'J') - 2440588) * 86400
             + TO_NUMBER (TO_CHAR (DATAIN, 'sssss'))
             - SUBSTR (TO_CHAR (TZ_OFFSET ('Europe/Rome')), 3, 1) * 3600;
   END plmktime;
   
And a function which translates mktime in date:
   
create or replace  FUNCTION TimeMk (MKTIME integer) RETURN DATE
   IS
   BEGIN
      RETURN  
      to_date('01-01-1970 00:00:00','dd-mm-yyyy hh24:mi:ss')+(MKTIME+SUBSTR(TO_CHAR(TZ_OFFSET('Europe/Rome')),3, 1) * 3600) / 86400;
   END TimeMk;

Here comes the problem (GMT offset)

SQL>select plmktime(to_date('27-MAR-2006 00:00:00', 'dd-mon-yyyy hh24:mi:ss' ),NULL) from dual;

        MK
----------
1143410400

I have a program made in C++ called julian

#include <stdio.h>
#include <time.h>
#include <stdlib.h>

void usage();

main(int argc, char **argv)
{
time_t julDate;

    if (argc < 2) {
        usage();
        exit(-1);
    }

julDate = (time_t) atoi(argv[1]);
printf("\n Juian date : %d - %s\n", julDate, ctime(&julDate));
}


void usage()
{
    printf("\nUsage : juldate.exe <JulinanDate>\n\n");
}

oracle@terra> julian 1143410400

 Julian date is: 1143410400 - Mon Mar 27 00:00:00 2006
 
oracle@terra> julian 1143327600

 Julian date is: 1143327600 - Sun Mar 26 00:00:00 2006
 
select plmktime(to_date('26-MAR-2006 00:00:00', 'dd-mon-yyyy hh24:mi:ss' )) mk from dual;

        MK
----------
1143324000

oracle@terra> julian 1143324000

 Julian date is: 1143324000 - Sat Mar 25 23:00:00 2006

Gee... I lost 1 hour! That because on the last sunday of March we had daylight savings.

Any ideas? who's right? C++ or PL/SQL?

Robert Hanrahan
http://rjh.keybit.net
Tom Kyte
April 21, 2006 - 7:34 am UTC

I believe this could have been teeny tiny - couldn't it? as it is, when I page down 5 times on a review/followup - I tend to have to skip it (too big to digest rapidly)

a quick scan seems to say to me that the entire partitioning thing is a red herring, not really part of the problem at all.

can you make it teeny tiny and distill it down to "just the facts" for the problem at hand.

Historical extract of data with UTC

Gopi, April 24, 2006 - 2:02 am UTC

Dear Tom,

I have an existing table date fields in it. Those dates were inserted using SYSDATE function. The new requirement we have is to extract date in the format UTC + Offset bearing daylight saving in mind.

I have tried approaches listed above, but failed to find how to extract UTC + offset for an existing data, e.g. 10-May-2006 17:00 what is it in UTC + Offset? (We are in +8:00 zone)

Another question is to how I can convert my Date fields into TIMESTAMP WITH LOCAL TIME ZONE?

Thank you.

Tom Kyte
April 24, 2006 - 2:16 am UTC

well, the problem is that for some of the dates in there - the answer is "I don't know"

Consider the days we "fall back" - there are 2 "2:30am" points - which 2:30am was it? We don't know.


If you are in +8 time zone all year round (unlike me, sometimes +5, sometimes +4) it is rather easy.

If you are sometimes +8 and sometimes +7 - it gets a bit sticky. I believe you'd need to use the table driven approach as above so you could figure out what timezone was in effect historically for that date (we cannot tell - you did not have the timezone stored with the data - it could be anything).

Historical extract of data with UTC

Gopi, April 24, 2006 - 5:35 am UTC

Thank you very much for your prompt response. I understand the issue now. It looks like the only way to go ahead is for existing data make a function which will return offset (fortunately there were only 2 daylight changes since system started) and alter existing tables to store TIMESTAMPS.

Which of the TIMESTAMP types is best suited for the situation? and what is the best way to do it (i.e just alter table a_tab modify a_date TIMESTAMP WITH LOCAL TIMEZONE) ?

Thank you very much.

Tom Kyte
April 24, 2006 - 5:49 am UTC

That is a decision you have to make based on your needs, this is from my last book Expert Oracle Database Architecture and might be useful in helping you decide:

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

Question

PRS, May 16, 2006 - 7:48 pm UTC

Tom,
I have a little different situation. I have my US data feeds arrive on a london server and they have EST timezone set in data feed.
My database is also on london server phsically. So I have following questions.
1) How do I write a query to find time offset from EST to UTC as I need to populate in one of the column of the table. Keep in mind that my database will be in London timezone of the server. I just need to find this as a constant so I can use in my other calculations.
2) Is there any functions ORACLE have where you supply the source timezone and destination timezone and it tells you what is the time offset?

Thanks,
PRS

Tom Kyte
May 16, 2006 - 8:13 pm UTC

if it is always EST, use new_time() with EST and GMT

Follow-up

PRS, May 16, 2006 - 9:05 pm UTC

It is EST but it should automatically take care of daylight saving time set and reset. Will this take care?
I need an offset. Will new_time give me the offset?

Tom Kyte
May 17, 2006 - 6:47 am UTC

no, it would do EST to GMT unless and until you told it to do EDT to GMT.

Tell me, does the incoming data have the TZ on it? (else what does the 2:30am time mean when you load it on that special day?)

Follow Up

PRS, May 17, 2006 - 8:36 am UTC

The issue is I am getting a time field in EST or EDT format deppending on the day. But I need to manipulate that field and add time offset in number of hours depending on EST or EDT to UTC. After that I need to concat that time offset to that field.
Example:
Original Record in the feed
1~09:10:10~YHOO~1.5
Modified Record in the feed
1~09:10:10 -04:00~YHOO~1.5

As you can see that I am appending time offset to field2 -04:00 (Because of EDT otherwise it will be -05:00).

Is there any way we can do this in ORACLE?

Tom Kyte
May 17, 2006 - 9:31 pm UTC

using the new (in 9i) timestamp type with timezone support - yes.

You still have the 2:30am problem however. Unless your data never has 2:30am (as stock quotes might not).

One more follow-up

PRS, May 17, 2006 - 9:56 pm UTC

select tz_offset('US/Estern') from dual gives me an offset relative to UTC. It gives me -04:00 as EDT is in effect. Will it take care of EST as well as EDT both or only EST?
We do not have 2:30 AM situation for US Quotes.

Tom Kyte
May 18, 2006 - 10:46 am UTC

as long as the database and session time zones as well as the time zones on the machine are set correctly.

Tom - Wouldn't this be of good help also?

Nitin, May 29, 2006 - 2:17 am UTC

select to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP),'dd-mon-yyyy hh24:mi:ss') from dual

Won't this be of great help as well to get the GMT time?

If I try:
SQL>select tz_offset( DBTIMEZONE ) from dual;
TZ_OFFS
-------
-08:00

SQL>  select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'), to_char(sysdate+(substr(tz_offset(dbtimezone),1,1)||'1')*to_dsinterval('0 '||substr(tz_offset( DBTIMEZONE ),2, 5)||':00'),'dd-mon-yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD-MON-YY TO_CHAR(SYSDATE+(SUBSTR(TZ
-------------------------- --------------------------
29-may-2006 00:07:09       28-may-2006 16:07:09

Which is not the right GMT time.

SQL> select SYSTIMESTAMP from dual;
29-MAY-06 12.33.47.409002 AM -05:00

-05:00 is the right difference. I assume my database is wrongly setup. So when I used

SQL> select to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP),'dd-mon-yyyy hh24:mi:ss') from dual

OR 

SQL> select to_char(SYS_EXTRACT_UTC(SYSTIMESTAMP),'dd-mon-yyyy hh24:mi:ss') from dual;

work well for me.

Would you recommend the same as a better option? Does this take care of Daylight saving times???

Thanks
Nitin
 

Store Date in a Format

Surender, June 05, 2006 - 7:19 am UTC

Hi Tom,

How can we store date in database using a Date datatype
in the following format

ex:07/05/2006 12:05:38PM

It can be done using Varchar2 but is it not possible with Date Datatypes?

Suren

Tom Kyte
June 05, 2006 - 7:53 am UTC

it is ALWAYS STORED THAT WAY WITH A DATE datatype.

A date datatype is a 7 byte beast. it has:

century
year
month
day
hour
minute
second

ALWAYS. all you need to do is have it be converted to a character string of your liking:


select to_char(date_col, 'mm/dd/yyyy hh:mi:ssAM') from t;

convert from UTC time back to local TIMESTAMP WITH TIME ZONE

A reader, June 10, 2006 - 1:27 pm UTC

Tom,

I am trying to convert the UTC time back to TIMESTAMP WITH TIEM ZONE value. Here is what I have:

SQL10gR1> !date; date -u
Sat Jun 10 12:14:25 CDT 2006
Sat Jun 10 17:14:25 GMT 2006

(* Server is at (* US/Central with daylight saving. *)

SQL10gR1> select dbtimezone,sessiontimezone from dual;

-06:00
-05:00

SQL10gR1> create table t(
2 ts1 TIMESTAMP WITH TIME ZONE,
3 ts2 TIMESTAMP WITH LOCAL TIME ZONE);

SQL10gR1> insert into t values(systimestamp,systimestamp);

SQL10gR1> select * from t;

10-JUN-06 12.09.41.149164 PM -05:00
10-JUN-06 12.09.41.149164 PM

SQL10gR1> select sys_extract_utc(ts1), sys_extract_utc(ts2) from t;

10-JUN-06 05.09.41.149164 PM
10-JUN-06 05.09.41.149164 PM

Now, suppose I have this string of UTC '10-JUN-06 05.09.41.149164 PM' and would like to convert the UTC value back to TIMESTAMP OF TIME ZONE and TIMESTAMP OF LOCAL TIME ZONE values. What is the reverse operation of sys_extract_utc()?

Thanks!!


to_localtime

A reader, June 11, 2006 - 5:30 pm UTC

Hi, Tom,

I found the answer from your other thread, using the from_tz() call.

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

Thanks.

Dumb question: What is UTC?

Ron, July 24, 2006 - 4:28 pm UTC

I see in the documentation that it is Universal Time Coordinator. It also looks like it is an option when issuing the CREATE DATABASE command (SET TIME_ZONE) clause. I've created lot's of databases without ever messing with this setting so I'm ignorant about how to do it. Is it something set by default?

I'm asking because I have to implement a vendore-supplied app and they told me "MDSI indicated that the operational database must run in UTC - please advise if this is an issue"

Tom Kyte
July 24, 2006 - 4:32 pm UTC

sounds like they might want your database server to use UTC as it's timezone. from this, not clear if just setting the database timezone would be sufficient or if you have to change the host system.


your 3rd party vendor would be the best person to ask about setting up for their application.


it is a timezone, a little different from GMT but you can reasonably think of them as the same:

[tkyte@localhost ~]$ date; date -u
Mon Jul 24 16:28:48 EDT 2006
Mon Jul 24 20:28:48 UTC 2006



Thanks.

Ron, July 25, 2006 - 4:59 pm UTC

That makes sense. After talking with the vendor they indicated that we have to set the UNIX TZ variable to UTC prior to starting the database and listener. I found a couple of references to this same technique in some of your other posts on asktom - thanks!

One way I was thinking about enforcing this (TZ=UTC) to ensure that one of the other DBA's doesn't accidentally start the database without it set correctly whould be to use a "AFTER STARTUP ON DATABASE" trigger to check to see if it's set and - if not - generate some trace files that would be picked up by our monitoring routines *and* put the instance in restricted session until we got it corrected. Something like this psuedo code:

CREATE or replace TRIGGER check_tz
after Startup on database
BEGIN
if TZ is not set to UTC then
execute immediate 'alter system enable restricted session';
sys.dbms_system.ksdwrt(3,'MEC ALERT - Database started with incorrect TZ variable...');
end if;
END;

I'd shudder to think what havoc the app might encounter when the SYSDATE is wrong. How could I check the value of TZ though from a trigger? I was poking around trying to see if any of the NLS parameters might give me a clue but I haven't found anything.

Tom Kyte
July 25, 2006 - 5:22 pm UTC

first thing that pops into my mind would be a host command to peek at the environment via a java stored procedure.

That's a good idea

Ron, July 26, 2006 - 10:15 am UTC

So I setup the java stuff in the database and something like an ls works fine:

09:03:32 SQL> exec :x := RUN_CMD('/usr/bin/ls /opt/app/oracle/admin/RESD/exp');
IGNITE_repository.log
RESD_full_200607250848.dmp.Z
RESD_full_200607260848.dmp.gz
RESD_pipe

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06

But when I try the /usr/bin/echo command it doesn't seem to understand the $ in the $TZ variable:

09:02:27 SQL> exec :x := RUN_CMD('/usr/bin/echo $TZ');
$TZ

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06

So just to make sure I was using the right command I just tried it straight from the host command:

09:02:41 SQL> ! echo $TZ
US/Central

Then I thought maybe I need to 'escape' the $ so I tried:

09:03:08 SQL> exec :x := RUN_CMD('/usr/bin/echo \$TZ');
\$TZ

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07

Still - no joy.  Any idea how I might get past this?  Thanks for your time with this UTC stuff by the way! 

Tom Kyte
July 26, 2006 - 11:36 am UTC

you would have to run a "shell", the shell is the program that sees $TZ and says "ah, environment variable"

but don't run a shell - you'd have to give the permission to run /usr/bin/sh or something and that would open up EVERY COMMAND.

So, write a script and run the script.

Perfect!

Ron, August 03, 2006 - 10:10 am UTC

Thanks Tom - that worked like a charm. I used the techniques from </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:952229840241 <code>
that Jack Wells and Kamal Kishore showed.

Where does oracle store timezone info?

koshal, November 04, 2006 - 7:36 pm UTC

date on which time changed 28-oct-2006 in case of est.
19:20:34 patibank@SONARD.CLT.NASD.COM> SELECT FROM_TZ(CAST(TO_DATE('2006-10-29 05:00:00','YYYY-MM-DD HH:MI:SS') AS TIMESTAMP), 'GMT') AT TIME ZONE 'America/New_York' "NY Time" FROM DUAL;
NY Time ---------------------------------------------------------------------------29-OCT-06 01.00.00.000000 AM AMERICA/NEW_YORK Elapsed: 00:00:00.01
19:20:35 patibank@SONARD.CLT.NASD.COM> SELECT FROM_TZ(CAST(TO_DATE('2006-10-29 06:00:00','YYYY-MM-DD HH:MI:SS') AS TIMESTAMP), 'GMT') AT TIME ZONE 'America/New_York' "NY Time" FROM DUAL;
NY Time ---------------------------------------------------------------------------
29-OCT-06 01.00.00.000000 AM AMERICA/NEW_YORK Elapsed: 00:00:00.01
19:20:56 patibank@SONARD.CLT.NASD.COM> spool off;

Thanks
Koshal

Tom Kyte
November 05, 2006 - 9:08 am UTC

what do you mean by "store" the timezone information?

in a timestamp with timezone type - it is part of the datatype.

the database has a timezone (you can query up the function dbtimezone)
the session has one too (function for that)
the OS has one as well.

How does oracle know time has to be reset on 29-oct-2006.

koshal, November 05, 2006 - 10:10 am UTC

How does oracle know based on the date whether new_york time is -4.00 or -5.00 of GMT?
It should know by some means (os/patch) that from 29th october 2006, it should be -5.00 GMT and not -4.00 gmt.



Tom Kyte
November 05, 2006 - 10:28 am UTC

that information is stored in a timezone configuration file, pretty standard stuff.

how did you PC know to set the timezone right. same sort of effect.

how to read/modify the contents of timezlrg.dat/timezone.dat

koshal, November 05, 2006 - 2:06 pm UTC

how to read/modify the contents of timezlrg.dat/timezone.dat

Thanks
Koshal

Tom Kyte
November 05, 2006 - 5:19 pm UTC

please contact support before you TOUCH A THING.

Daylight Savings Time changes information

Dawar, February 14, 2007 - 4:06 pm UTC

Tom,

Is there any way to perform "Daylight Savings Time changes information" by script.

If yes, how?

Regards,
Dawar

Daylight Savings Time changes and patch information

Dawar, February 14, 2007 - 6:08 pm UTC

It was typo earlier.

I mean,

Is there any way to change Daylight Savings Time by Oracle Script?

Dawar
Tom Kyte
February 15, 2007 - 9:52 am UTC

it takes an act of congress I think?

Not sure what you mean.

DST

Dawar, February 15, 2007 - 11:30 am UTC

yes, its true.

On August 8, 2005, President George W. Bush signed the Energy Policy Act of 2005. This Act changed the time change dates for Daylight Saving Time in the U.S. Beginning in 2007, DST will begin on the second Sunday in March and end the first Sunday in November. The Secretary of Energy will report the impact of this change to Congress.

Dawar
Tom Kyte
February 16, 2007 - 12:36 pm UTC

so, like i said.....

Alexander the ok, February 16, 2007 - 12:59 pm UTC

Dawar, search metalink they have a section dedicated to the DST change. If you have 9.2.0 then you may not have to do anything, just run a script to see if have any data needs to be fixed. In 10g you have to patch the database by either going to 10.3 or they have a special DST patch.

I'm not sure what Tom is talking about hopefully he can confirm or deny what my understanding is.

DST time

Dawar, February 16, 2007 - 3:33 pm UTC

Thanks.

Is upgarde is valid or certify by Oracle from 10.2.0.1.0 to 10.2.0.3.0?
OS is AIX/IBM 5.2


Tom Kyte
February 18, 2007 - 8:49 am UTC

please utilize support for stuff like this

Tom Fox, February 17, 2007 - 12:40 pm UTC

Dawar,

MetaLink has a documentation and certification matrices for their products. Visiting MetaLink first will get you the quickest answer.

Tom

Randall, March 13, 2007 - 10:49 am UTC

Tom,

I'm not sure the time zone patch is working correctly, or maybe I am not doing something right. We need to convert GMT time to local time and I pulled this from the Oracle 10.2 Docs.

Our version is 10.2.0.3 64-bit on 2003. The 10.2.0.3 has the timezone V3 files.


----------------------------------------
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> SELECT
FROM_TZ (CAST (TO_DATE ('2007-03-01 11:00:00',
'YYYY-MM-DD HH:MI:SS') AS TIMESTAMP),
'Etc/Greenwich') AT TIME ZONE 'US/Eastern'
"EAST Coast Time",
FROM_TZ (CAST (TO_DATE ('2007-03-11 11:00:00',
'YYYY-MM-DD HH:MI:SS') AS TIMESTAMP),
'Etc/Greenwich') AT TIME ZONE 'US/Eastern'
"EAST Coast Time2",
FROM_TZ (CAST (TO_DATE ('2007-04-01 11:00:00',
'YYYY-MM-DD HH:MI:SS') AS TIMESTAMP),
'Etc/Greenwich') AT TIME ZONE 'US/Eastern'
"EAST Coast Time3"
FROM DUAL;

EAST Coast Time
------------------------------------------------------------
EAST Coast Time2
------------------------------------------------------------
EAST Coast Time3
------------------------------------------------------------01-MAR-07 06.00.00.000000 AM US/EASTERN
11-MAR-07 06.00.00.000000 AM US/EASTERN
01-APR-07 07.00.00.000000 AM US/EASTERN

-------------------------------------

I would expect to see the second time (Mar 11) to switch to 7AM. But it does not switch until APR-1 which is the old DST change date.

What am I missing?

Thanks for your time...


Tom Kyte
March 13, 2007 - 11:37 am UTC

please utilize support

Different results

BERTHIER LAURENT, March 21, 2007 - 9:00 am UTC

Hi,

What's happen ? I don't understand

select to_char(to_date('09-03-07 23:00:00','DD-MM-RR HH24:MI:SS') +(substr(tz_offset(dbtimezone),1,1)||'1')*to_dsinterval( '0 ' || substr(tz_offset(dbtimezone), 2, 5 ) || ':00' ),'D') from dual;

The query's result from my server is "7"
The query's result from client sql*plus is "6"

Why ?

V, August 09, 2007 - 3:04 pm UTC

TOm,
I am sure you answered to this type of question many times but I could not find any for my requirment.
Can you please hlep me for the below question.

  1*  create table dt(col timestamp with time zone)
SQL> insert into dt values(systimestamp);
1 row created.
SQL> insert into dt values(systimestamp+1);
1 row created.
SQL> select * from dt;
COL
-------------------------------------------------
09-AUG-07 01.50.10.796000 PM -05:00
10-AUG-07 01.50.17.000000 PM US/MOUNTAIN

Our application lets users to choose which way dates to be presented (1. Timestamp with Timezone entered  2.every thing in local time zone of data requester )

I need to select COL in requester's local time zone as below
select convert_to_local_tz(col,sessiontimezone) from dt;

Can you please help me how I can write this function
convert_to_local_tz
Thank you ,
V

A reader, November 17, 2009 - 3:58 am UTC

We have web based application using web logic application server, how can we set session time zone of client/end user. At the start up of application server our connection are built in connection pool. We are using spring 2.5.5 in middle tier. We are working on 11 g with dedicated server setting.
Tom Kyte
November 23, 2009 - 1:46 pm UTC

you would use the alter session command to set the time_zone for that session.


Why not using this:

Asaf, January 07, 2010 - 10:32 am UTC

We are using the at time zone clause for systimestamp and then converting it to date if needed.
for example
SELECT to_date(to_char(systimestamp at time zone 'GMT','DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS') as GMT_SYSDATE
from dual




A reader, May 13, 2010 - 1:56 am UTC

Hi Tom,

Our data centers resides in different zones and we would like to store the date and time information in GMT format and it should aware of daylight savings.

Which data type is preferred? Date or timestamp with timezone or timestamp with local time zone.

Do we need to configure any parameter on the database?

Please share your experience on this.

Convert Sysdate to GMT

Oracle-User, June 03, 2010 - 1:25 pm UTC

Hi Tom

We have an existing application that runs on Oracle 9.2 on a Unix system. The application uses the sysdate function to store the date-time info in many places (in DATE columns) and we have
a lot of historical data with this setup in place.

We now have a requirement to pass the data to another application and they want the date-time columns sent to them in GMT. I do not have the opportunity to make data-type changes etc to use some of the more advanced features of TIMESTAMPS etc.

Based on the posts i think the following approach will work :

1) I checked that the Time Zone on the server is US/Eastern

uxgwdv01:/opt/app/stage/dwgcp> echo $TZ
US/Eastern

2) To return the data I propse to use the SQL:

select
activity_date
+
(substr(tz_offset('US/Eastern'),1,1)||'1')
*
to_dsinterval('0 '||substr(tz_offset( 'US/Eastern' ),2, 5)||':00') activity_date_in_gmt
from my_activity_table

==============
Q's I have are
==============
a) does this solution seem ok, i have checked that it works fine now but testing it for all DST situations is hard, so wanted to see if you think this should work ok ?

b) does this suffer from the '2:30 on the special day' problem ? i think not, as the time is always stored using sysdate so it can never store a 'bad time' - am i right in making this assumption ?

would greatly appreciate your insights ...


Tom Kyte
June 08, 2010 - 12:04 pm UTC

you could use new_time

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/functions092.htm#SQLRF00671

bear in mind, daylight savings things are lost forever. You cannot tell the difference a DST and non-DST time during the switchover period.



everything stored without an explicit timezone will suffer from the special day problem. You'll have 2:30 'twice' for some records that are really an hour apart.

Sql

arnab, October 07, 2010 - 10:51 am UTC

Hi Tom

referring to your code , If this is to be run EST to give GMT date ,shouldnt this be a minus (-) rather than a (+)

ops$tkyte@ORA9I.WORLD> select sysdate,
  2             sysdate+(substr(tz_offset(dbtimezone),1,1)||'1')*to_dsinterval('0 
'||substr(tz_offset( DBTIMEZONE ),2, 5)||':00')
  3    from dual
  4  /

SYSDATE              SYSDATE+(SUBSTR(TZ_O
-------------------- --------------------
22-apr-2002 09:59:26 22-apr-2002 05:59:26


Something like this -


  1  select     sysdate,        sysdate-(substr
  2             (tz_offset
  3             (
  4             dbtimezone
  5             )
  6             ,1,1
  7             )
  8             ||'1'
  9             )
 10             *
 11             to_dsinterval
 12                             ( '0 ' ||substr
 13                             (tz_offset
 14                             (
 15                             dbtimezone
 16                             ), 2, 5
 17                             ) || ':00'
 18                             )
 19* from dual
SQL> /

SYSDATE              SYSDATE-(SUBSTR(TZ_O
-------------------- --------------------
07-OCT-2010 10:51:10 07-OCT-2010 15:51:10

Tom Kyte
October 11, 2010 - 10:38 am UTC

yes, it should have been, thanks

getting ora- 01867 error

A reader, April 12, 2011 - 8:47 am UTC

Dear Tom ,

Can you please clear my following issue

description of table as follows

colm1 NOT NULL VARCHAR2(30)

Colm2 TIMESTAMP(6)

Colm3 INTERVAL DAY(3) TOSECOND(6)

Col4 DATE




SELECT Y.Colm1, LTRIM(TO_CHAR(SYSTIMESTAMP -Y.Colm2),'+0') NEW_INTERVAL FROM a.table_name Y ;

above statement errored out with ORA-01867: the interval is invalid

tried search in google / from other resources .. but no luck ..

Database : 10.2.0.4 .. four node rac
O/s : HP-Ux

can you please help me solve this issue

Thanks
Aliyar




Tom Kyte
April 13, 2011 - 9:18 am UTC

give full example

create table - not a describe
insert into table
select from the table


Getting TimeZone in session

Raj, April 16, 2011 - 1:30 am UTC

Tom,

I have my listener and environment (TZ variable) set for each of my regional databases differently (Like for Eastern it is EST, Pacific PST, same for Europe etc). 
Now after logging I get right UTC time weather I am in PST or PDT:

SQL>select sys_extract_utc(systimestamp) from dual;

SYS_EXTRACT_UTC(SYSTIMESTAMP)
---------------------------------------------------------------------------
16-APR-11 06.15.30.293170 AM

Is there any way, I can find what Time Zone of environment is from within database? 

Here is my problem:
I am storing sysdate in a table (it is local time depending upon TZ environment variable) and want to compare stored time with sysdate. All works well with an exception of 
when DST changes. I stored 1:59AM PST on saturday (day DST took effect in March) in the table. Now I am trying to find a difference between sysdate and stored time 2 minutes 
later at 3:01 (PDT time), it will give me 62 minutes but actually it is 2 minutes. 

I thought I will do something like this:

select sys_extract_utc(systimestamp)-some_function_to_convert_time_to_UTC(Date_stored_in_table) from dual;

I am trying to find "some_function_to_convert_time_to_UTC" this function. I can very well hardcode timezone using new_time but I have hundreds of databases in diffrenet timezone
and will be very hard to maintain all those. I am looking for some way to find timezone from sysdate. I tried:

SQL>SELECT EXTRACT(TIMEZONE_REGION from systimestamp), EXTRACT(TIMEZONE_ABBR from systimestamp) from dual;

EXTRACT(TIMEZONE_REGIONFROMSYSTIMESTAMP)                         EXTRACT(TI
---------------------------------------------------------------- ----------
UNKNOWN                                                          UNK


But this does not give it as there could be multiple TIMEZONE_REGION for same offset from UTC. 


I am sorry if I am not clear and can explain with more specific example if needed. 

Any help is highly appreciated. 

   

Tom Kyte
April 18, 2011 - 10:01 am UTC

unless you store the DATE using a timestamp with timezone - you cannot do this.



Let us say I give to you:

1:59am
3:01am

How much time between the two? You didn't store a time zone - therefore you cannot tell me - and neither can we.

Why aren't you using timestamps which do support timezones?

DST aware DATE timezone converting

Kim Berg Hansen, April 19, 2011 - 2:02 am UTC

Hi

I had similar issues getting date/time data from external source in one timezone and needing to store it in another timezone in a DATE column (alas wasn't possible to change legacy system to use timestamps :-)

I came up with this function:

SQL> create or replace function date_tz_convert(
  2     p_date      date,
  3     p_from_tz   varchar2,
  4     p_to_tz     varchar2
  5  )
  6     return date
  7  is
  8  begin
  9     return cast(from_tz(cast(p_date as timestamp),p_from_tz) at time zone p_to_tz as date);
 10  end date_tz_convert;
 11  /
Function created.


First I cast the p_date as a timestamp in order to be able to use timezone functions.
Then I use the function from_tz to convert from p_from_tz to the database timezone (whatever that may be :-)
The expression "at timezone p_to_tz" then converts from database timezone to p_to_tz.
And finally cast the timestamp back to a date again.

(Took me some time to find the "at timezone" expression. I had expected something like "to_tz" function complementing the "from_tz" function. One sideeffect that there is no "to_tz" function but instead the "at timezone" function is that "at timezone" cannot be used in an 11g virtual column. Therefore I had to create my own function :-)

This function is completely daylight savings time aware.
For example there is usually 6 hours time-difference between New York and Copenhagen - except that the US starts DST 2 weeks earlier than Europe, so for 2 weeks there is only 5 hours difference.

SQL> with ny_dates as (
  2     select to_date('01-03-2011 13:00:00','DD-MM-YYYY HH24:MI:SS')
  3            + numtodsinterval( (level-1)*7, 'day' ) dt
  4     from dual
  5     connect by level <= 6
  6  )
  7  select
  8  to_char(ny_dates.dt,'DD-MM-YYYY HH24:MI:SS') ny_date,
  9  to_char(date_tz_convert(ny_dates.dt,'America/New_York','Europe/Copenhagen'),'DD-MM-YYYY HH24:MI:SS') cph_date
 10  from ny_dates
 11  ;

NY_DATE             CPH_DATE
------------------- -------------------
01-03-2011 13:00:00 01-03-2011 19:00:00
08-03-2011 13:00:00 08-03-2011 19:00:00
15-03-2011 13:00:00 15-03-2011 18:00:00
22-03-2011 13:00:00 22-03-2011 18:00:00
29-03-2011 13:00:00 29-03-2011 19:00:00
05-04-2011 13:00:00 05-04-2011 19:00:00

6 rows selected.


Of course this won't solve the last reviewers problem, as this function do require that you know explicitly from and to timezone in order to simulate working with timezones in DATE datatypes :-)

But it might be useful to someone anyway :-)

Tom Kyte
April 19, 2011 - 6:39 pm UTC

...
Of course this won't solve the last reviewers problem, as this function do require that you know explicitly from and to timezone in order to simulate working with timezones in DATE datatypes :-) ...

exactly :)

if you do not store your data in some "timezone aware" fashion, it won't be timezone aware.

Getting TimeZone in session

Raj, April 23, 2011 - 12:47 am UTC

Thanks Tom.

Problem I have is the table I am talking about is v$database.CONTROLFILE_TIME which is a date column.

When I do systimestamp, how does it take right timezone and display? Is it a direct call to OS? All I am trying to figure out is if we can make use of similar things to resolve my issue.





Tom Kyte
April 25, 2011 - 11:11 am UTC

you cannot do anything like that with a date.

Tell you what, when you can tell me what timezone:

01:15am

is on the day the clocks go back an hour, I can help you out.


systimestamp gets it from the OS


you'd have to look at CHECKPOINT_CHANGE# or something else that is an "absolute" time to order this information.

Multiple values for sysdate

Lal Cyril, March 07, 2012 - 6:15 am UTC

Tom,

One of our applications use database sysdate function extensively. We have a requirement to host two clients in different timezones in same server machine.
From the documentation what i could understand is that the sysdate function uses the timezone of database server machine. We need the sysdate function to
show the time differently for the two clients based on their timezones.

When i referred metalink i got the following thread
How To setup TNS listener to Show More Than one Timezone [ID 399448.1]
It mentions about setting up two listeners with different timezones connecting to the same database. (Works with unix platform only)
For each client i can configure the tnsnames to use the applicable listener.

Can i use this approach?
Do you foresee any issues with this approach?
Suppose i have a pl/sql procedure which uses the sysdate function, will the sysdate function return different values for each client?
Tom Kyte
March 07, 2012 - 7:04 pm UTC

Can i use this approach?


obviously? you have a note showing you how

Do you foresee any issues with this approach?

I foresee much confusion in your future - as someone will undoubtedly connect to the database using a direct connection (no sqlnet) - such as you DBA - and they will inherit THEIR timezone from THEIR session. I'm not a fan of confusing things.


Suppose i have a pl/sql procedure which uses the sysdate function, will the sysdate function return different values for each client?

sysdate will be evaluated by the database asking the OS "what time is it". SQL, PLSQL, whatever - it doesn't matter. the database will respond with the time from the OS. Each client connected to the database via the SAME LISTENER with the same timezone environment variable will see the same time.

Multiple values for sysdate

Lal Cyril, April 26, 2012 - 4:08 am UTC

Hi Tom,
Will the approach work for Oracle jobs.
Suppose i have a oracle job which calls procedure P1 every night 11:00 pm and the procedure P1 calls the sysdate function, what will be value for sysdate?

Tom Kyte
April 26, 2012 - 8:15 am UTC

jobs are no different than anything else.

sysdate will return whatever clock time the operating system reports to us at the point in time sysdate is called.


sysdate in your case would be some time after 11pm - it won't be exactly 11pm, it will be whatever time it was when sysdate was invoked.

Multiple values for sysdate

Lal Cyril, April 27, 2012 - 2:48 am UTC

Tom,

I will refine my question.
Database DB1 exists and LSNR1 listener is confifured for DB1 with TimeZone TZ1.
Database DB2 exists and LSNR2 listener is configured for DB2 with TimeZone TZ2.

Procedure P1 exists in DB1 and DB2 database and calls sysdate function. Oracle job exists in DB1 and DB2 which calls P1 procedure.

My question is will the sysdate function give different values for DB1 and DB2 if its called by oracle job?

Will Oracle job connect through a listener?
Is it possible to make the oracle job connect through a listener?






Tom Kyte
April 27, 2012 - 8:08 am UTC

sysdate could give different values for DB1 and DB2 in all cases - job or not (the 'job' bit is a red herring, it does not affect the outcome).


It depends on WHERE sysdate is evaluated.

If a job running on DB1 uses a datbase link to run a procedure on DB2 and the procedure RUNS on DB2, then sysdate would be evaluated on DB2

UTC conversion with daylight saving

Richa Mahajan, April 14, 2021 - 2:48 pm UTC

We have an Oracle 12c and want to store the data entered by users in GMT ( database is in US- eastern Time zone)
I have created a table T
CREATE TABLE T ( X TIMESTAMP (6),
Y TIMESTAMP (6) WITH TIME ZONE,
Z TIMESTAMP (6) WITH LOCAL TIME ZONE,
W DATE)
I did 3 inserts to see if EDT and EST are giving me same values in database for GMT in column - I see a 1 hr time difference (NEW_TIME function is used)

Insert statements and values are attached below:
insert into t(x,y,z,w,create_user ) values (sysdate,sysdate,sysdate,sysdate);
insert into t(x,y,z,w,create_user) values (( NEW_TIME( TO_DATE( sysdate ), 'EDT', 'GMT' )),( NEW_TIME( TO_DATE( sysdate ), 'EDT', 'GMT' )),( NEW_TIME( TO_DATE( sysdate ), 'EDT', 'GMT' )),( NEW_TIME( TO_DATE( sysdate ), 'EDT','GMT' )));
insert into t(x,y,z,w,create_user) values (( NEW_TIME( TO_DATE( sysdate ), 'EST', 'GMT' )),( NEW_TIME( TO_DATE( sysdate ), 'EST', 'GMT' )),( NEW_TIME( TO_DATE( sysdate ), 'EST', 'GMT' )),( NEW_TIME( TO_DATE( sysdate ), 'EST','GMT' )));
X Y Z W
14-APR-21 10.38.01.000000000 AM 14-APR-21 10.38.01.000000000 AM ASIA/CALCUTTA 14-APR-21 10.38.01.000000000 AM 14-APR-21
14-APR-21 04.00.00.000000000 AM 14-APR-21 04.00.00.000000000 AM ASIA/CALCUTTA 14-APR-21 04.00.00.000000000 AM 14-APR-21
14-APR-21 05.00.00.000000000 AM 14-APR-21 05.00.00.000000000 AM ASIA/CALCUTTA 14-APR-21 05.00.00.000000000 AM 14-APR-21

Doesnt timestamp with timezone take care of daylight saving?

NLS_session_parameters- few of them are as below:
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
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
Kindly help
Chris Saxon
April 15, 2021 - 9:55 am UTC

You're asking to specifically convert the time zone EDT which is UTC -4 and specifically EST which is UTC -5.

To convert times stored in the US Eastern time zone to another adjusting for daylight savings as needed, choose a location in this zone, e.g. New York.

To do this, instead of NEW_TIME:

* Cast the date to a timestamp
* Pass this to FROM_TZ to convert this to a timestamp with time zone in a US Eastern location (America/New_York)
* Return this AT TIME ZONE 'GMT'

alter system set fixed_date = '2021-01-01 00:00:00';

select sysdate eastern_time, 
       from_tz ( 
         cast ( sysdate as timestamp ), 
         'America/New_York' 
       ) at time zone 'UTC' utc_time
from   dual;

EASTERN_TIME           UTC_TIME                   
01-JAN-2021 00:00:00   01-JAN-2021 05.00.00 +00    

alter system set fixed_date = '2021-03-21 00:00:00';

select sysdate eastern_time, 
       from_tz ( 
         cast ( sysdate as timestamp ), 
         'America/New_York' 
       ) at time zone 'UTC' utc_time
from   dual;

EASTERN_TIME           UTC_TIME                   
21-MAR-2021 00:00:00   21-MAR-2021 04.00.00 +00   

alter system set fixed_date = none;

select sysdate eastern_time, 
       from_tz ( 
         cast ( sysdate as timestamp ), 
         'America/New_York' 
       ) at time zone 'UTC' utc_time
from   dual;

EASTERN_TIME           UTC_TIME                   
15-APR-2021 09:50:27   15-APR-2021 13.50.27 +00