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

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

 

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

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.

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.

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,

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

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

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.

 

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

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

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
 

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.

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 !


 

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 .

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

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

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

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

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 

"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

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?

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.

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

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

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.

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,

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

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


 

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.

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.

 

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
 

Thanks for the suggestion.

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

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.

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.

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> 

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

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?

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?

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.

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
 

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,

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

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.

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"

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.

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! 

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.

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

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.

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

Thanks
Koshal

Tom,

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

If yes, how?

Regards,
Dawar

It was typo earlier.

I mean,

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

Dawar

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

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.

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

Dawar,

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

Tom

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

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 ?

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

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.

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

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.

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

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

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,

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. 

   

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

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,

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?

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,

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?

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

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