What about jobs?
Bob, October 23, 2002 - 8:24 am UTC
For example, if you need to run a job every 5 minutes and the next time is 1:03 AM, but the time change moves the time back an hour, how would you go about getting the job to run at the new time of 12:03?
October 23, 2002 - 8:44 am UTC
... only in applications that use the database ....
So, lets look at this, is it time to "fall back". At 2am -- it will become 1am all of a sudden.
In 9i with timezone support -- you can easily deal with this. In 8i -- it is somewhat more complex, you'll need to "code" timezone support -- that is, your job (which can compute the next time as an out parameter instead of using the INTERVAL parameter to submit) would have to be aware of it.
Handling Day light savings in 8i
Kulguru, October 23, 2002 - 10:15 am UTC
Tom
Can you please illustrate as to how we can handling day light savings in 8i , with an example.
Thaks
October 23, 2002 - 11:17 am UTC
You'll have to code the logic for it -- i don't have it.
something like a lookup table of when the time shift happens for you -- something your job would inspect to come up with the "right" time for you.
A reader, October 23, 2002 - 3:14 pm UTC
Using examples:
-- Add 1 hr. and 59 min.
SQL> select to_char(to_date('27/10/2002','DD/MM/YYYY')
+ 119/(24*60), 'DAY HH:MI:SS AM') from dual;
you would get
TO_CHAR(TO_DATE('27/1
---------------------
SUNDAY 01:59:00 AM
and
-- ADD 2 hr. and 1 min.
SQL> select to_char(to_date('27/10/2002','DD/MM/YYYY')
+ 121/(24*60), 'DAY HH:MI:SS AM') from dual;
TO_CHAR(TO_DATE('27/1
---------------------
SUNDAY 02:01:00 AM
We are using 8.1.7.3 so the second query returns
Sunday 2:01 AM.
But what your saying that in 9i it would return
Sunday 1:01 AM.
is that correct? This would be very nice.
Thanks
dst aware
Phil, September 03, 2003 - 6:00 am UTC
Hi Tom,
I am still a little confused, sorry, about how Oracle 9i is day light savings aware.
I have tested and seen the Oracle was aware that dst changed in Sydney/Aus to cater for the Olympics. However what if the powers that be decide that they should change in Melbourne for the Commonwealth Games (British thing) next year. ?
Is there something that needs to be done to make it aware of changes on a regular basis, or does it only get updated with released versions of Oracle.
I know there are companies out there that supply this information, with the expansion of timezones in 9i i may be lucky ??
thanks
September 03, 2003 - 7:06 am UTC
you'd have to contact support for that one.
A reader, March 11, 2004 - 9:12 am UTC
I contacted support and finally got an answer and an update of the oracle files that were required to be updated. Unfortunatly the response time was not what I was looking for. Some countries change there DST quite often.
We now have decided to subscribe to recieving a Java class that is updated regularly and thus the java programmers will there worst.
what i would like to do is keep the java in Oracle as there will be alot of database activity in the process. How do I use an external class source with a oracle java source (9i) ?
thanks
March 11, 2004 - 1:28 pm UTC
you do not, you'd have to load that in.
zoneinfo
phil, February 09, 2005 - 12:10 am UTC
Hi Tom,
9ir2
this I think could possibly be a big ask !
we currently use the tz / zoneinfo database that seems to be globaly used ( </code>
http://www.twinsun.com/tz/tz-link.htm ) within our application to convert incoming dates to utc. As DST is very important we need to use the last information to do this - hence zoneinfo. As oracles timezone files are not updated frequently enough we were unable to use oracle features.
However - I found the following through google
http://www.weitz.de/timezone.html <code>
My question is how can I do this in a java stored procedure rather than the external C program, using the same zoneinfo files.
many thanks
phil
February 09, 2005 - 2:48 am UTC
don't know, don't do enough java to "know". not sure where the java stuff gets it's tz info from.
would be easy for you to test. get an updated ( from the database ) tz file and see if java in a stored procedure gives you the right thing.
Primary key and daylight saving times
rkinfo, November 02, 2005 - 5:07 pm UTC
Suppose I have an table collecting timestamp and other value and I have primary key on timestamp. When daylight saving time ends
A)Will table reject the records as timestamps got repeated for an hour?
B)How can I get records back without daylight saving adjustment (as I need to get all the data for standard time not daylight saving time) ?
November 03, 2005 - 6:50 am UTC
what kind of timestamp? does it have timezone?
(using a timestamp as a primary key, hmm - you don't want to know what I think about that....)
A reader, November 03, 2005 - 7:33 am UTC
Primary key is on tag_id and timestamp
timestamp is "dd-mon-yyyy hh24:mi:ss"
November 04, 2005 - 2:19 am UTC
using a time stamp as part of the key - you still do not want to know what I think about that.
and a timestamp is not to be confused with a format - 'dd-mon-yyyy hh24:mi:ss' is a FORMAT, a timestamp is a 7 to 11 byte fixed width field that contains binary data that may be formatted into some string upon retrieval.
If you are NOT storing the timezone, then you will get the opportunity for duplicate timestamps - so I guess it depends on what tag_id is to see if this will be a problem.
having your timestamp as a functionally dependent attribute of a primary key that used a sequence would remove any chance of duplication.
A reader, November 03, 2005 - 7:34 am UTC
I should say timestamp is declare like date datatype
Explanation on your comment
Puja, November 04, 2005 - 2:17 am UTC
Hi Tom,
In one of the above postings, you gave the following comment:
"(using a timestamp as a primary key, hmm - you don't want to know what I think about that....) "
Would you please say why using a timestamp as a primary key is not a very good idea?
Thanks and regards,
Puja
November 04, 2005 - 3:38 am UTC
because two things can happen at the same time.
Thanks
Puja, November 04, 2005 - 7:01 am UTC
That was indeed a very minor detail (with major impact!!) that I missed!!
daylight saving time
A reader, December 01, 2005 - 10:03 am UTC
I used est, edt and it added 1 hr to all the times regardless of date
eg:
select to_char(oldtime,'dd-Mon-yyyy hh24:mi:ss') Standard,
to_char(NEW_TIME(oldtime,'EST','EDT'),'dd-Mon-yyyy hh24:mi:ss') daylightsaving from times
It returned
STANDARD DAYLIGHTSAVING
-------------------- --------------------
01-Dec-2005 09:38:26 01-Dec-2005 10:38:26
29-Oct-2005 23:50:00 30-Oct-2005 00:50:00
29-Oct-2005 23:59:00 30-Oct-2005 00:59:00
30-Oct-2005 00:00:01 30-Oct-2005 01:00:01
30-Oct-2005 00:02:01 30-Oct-2005 01:02:01
30-Oct-2005 01:02:01 30-Oct-2005 02:02:01
30-Oct-2005 05:02:01 30-Oct-2005 06:02:01
26-Oct-2005 10:44:28 26-Oct-2005 11:44:28
Instead of
STANDARD DAYLIGHTSAVING
-------------------- --------------------
01-Dec-2005 09:38:26 01-Dec-2005 9:38:26
29-Oct-2005 23:50:00 30-Oct-2005 00:50:00
29-Oct-2005 23:59:00 30-Oct-2005 00:59:00
30-Oct-2005 00:00:01 30-Oct-2005 00:00:01
30-Oct-2005 00:02:01 30-Oct-2005 00:02:01
30-Oct-2005 01:02:01 30-Oct-2005 01:02:01
30-Oct-2005 05:02:01 30-Oct-2005 05:02:01
26-Oct-2005 10:44:28 26-Oct-2005 11:44:28
Cont...
A reader, December 01, 2005 - 10:15 am UTC
Oracle version is 10g
December 01, 2005 - 12:49 pm UTC
don't use new_time, use the timestamp with timezone that is there in explicit and full support of timezones.
convert eastern standard time to daylight saving time
A reader, December 01, 2005 - 2:28 pm UTC
will you please give me example for that?
December 02, 2005 - 10:34 am UTC
give me an example of when you would need to do that - 2:30 AM EST on the day the clocks change is 2:30 AM EST - there might be a 2:30 AM EDT that same day but the two times are sort of mutually exclusive?
daylight saving time
A reader, December 02, 2005 - 11:27 am UTC
I am getting 5 min data in EST.
During compilation for a year:
From first Sunday of April to last Sunday of October I need to use daylight saving time instead of EST while for rest of the year I will use EST. Now I need to convert EST for daylight saving period to EDT.
New_Time changes all the time regardless of period although I used following table
TS TIMESTAMP(6)
TSZ TIMESTAMP(6) WITH TIME ZONE
TLZ TIMESTAMP(6) WITH LOCAL TIME ZONE
OLDTIME DATE
Is ther any function
Thanks in advance
December 02, 2005 - 11:57 am UTC
does your data have a timezone associated with it or not.
If not, you'll have to use something like
case when dt between A and B then dt-1/24 else dt end
that is, just adjust it yourself.
Server Date change
Yogesh, January 17, 2007 - 2:03 pm UTC
I've a similar requirement where, I've to change the server system date. Whill that have any impact on the running databases? To add to this complication, I've a RAC cluster with data guard.
Timestamp with TimeZone Issues
ritesh, January 22, 2008 - 6:54 am UTC
We are facing issues with using timestamp with timezone type in Oracle and are stuck.
Following is a brief description of the timestamp with time zone issues we are facing
We are experiencing inconsistencies in interpreting dates in the interval for 7.5 hours before the DST switch times. We use TIMESTAMP WITH TIME ZONE Data type. Example:
a. 2007-11-03 17:00:00 PDT ¿ 2007-11-04 00:30:00 PDT
b. 2008-03-08 19:00:00 PST ¿ 2008-03-09 01:30:00 PST
c. 2008-11-01 17:00:00 PDT ¿ 2008-11-01 01:30:00 PDT
Oracle DB Default Time zone:
Time zone is set to UTC.
Oracle Data type used:
TIMESTAMP WITH TIME ZONE
Oracle Driver Used:
OJDBC14 ( downloaded from oracle-java brazil package).
Status of Data in DB:
CONSISTENT
Status of Data from SQLPLUS:
CONSISTENT
Status of Data from oracle Driver:
Offset by +1:00 hr during this duration.
How we convert to Java type
We call oracle.sql.TimestampTZ.timestampValue(connection);
Can you please help.
Thanks
January 22, 2008 - 7:33 am UTC
without an example of what you are doing and how you are doing it - nope, no help to be found anywhere probably.
small test case, tiny test case - as LITTLE code as humanly possible.
daylight saving switch data
A reader, November 04, 2011 - 12:44 pm UTC
Hi Tom,
Is there a data dictionary table that stores daylight saving switch info for each time zone, like which day each year at what time?
The purpose that I am asking is to detect the switch.
Thanks