RE: add dummy rows

If you have to restore Oracle to a point in time during the 1am hour the second time the 1am hour has rolled by, time is not normally the reference, but something like the SCN number. If you restore to SCN corresponding to 1:30am, Oracle knows what time it is.... So do you mean the underlying monochromatically increasing clock, is like the julian type clock oracle uses.... (but I think those times roll back and repeat). It seems that you need an extra field... like an SCN to keep the time part straight? No?

On a side note concerning time: pulling time/date during leap years. (I made an algorithm around a decade ago based on a Wilsons Quarterly article that should be good for something like one day in 29000 years or so. (the 400 years, or even 2000, year thing is still not good enough as you go farther into the future). You can't believe the math already in Leap years for those of you that work for companies making futuristic video games :) I can see a neat trick in there somewhere -- like adding a twist to Hitchhikers Guide to the Galaxy.

Now I won't try making schedule like in Steven Feuerstein's recent example using dbms_scheduler.evaluate_calendar_string in his plsql challenge -- since oracle won't even live that long -- I don't think.

Joel Patterson
Database Administrator
904 928-2790

--
Joel Patterson
Sr. Database Administrator | Enterprise Integration
Phone: 904-928-2790 | Fax: 904-733-4916
http://www.entint.com/

http://www.entint.com/

http://www.facebook.com/pages/Enterprise-Integration/212351215444231 http://twitter.com/#!/entint http://www.linkedin.com/company/18276?trk=tyah http://www.youtube.com/user/ValueofIT

This message (and any associated files) is intended only for the use
of the addressee and may contain information that is confidential,
subject to copyright or constitutes a trade secret. If you are not the
intended recipient, you are hereby notified that any dissemination,
copying or distribution of this message, or files associated with this
message, is strictly prohibited. If you have received this message in
error, please notify us immediately by replying to the message and
deleting it from your computer. Messages sent to and from us may be
monitored. Any views or opinions presented are solely those of the
author and do not necessarily represent those of the company. [v.1.1]

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham
Sent: Friday, May 24, 2013 10:34 AM
To: jonathan_at_jlcomp.demon.co.uk; 'Oracle Mailinglist'
Subject: RE: add dummy rows

JL's questions highlight why an underlying monochromatically increasing clock is a better way to store data.

Stored that way, you can do whatever gymnastics are required to label it a given hour and day in whatever local calendar and hour of day you want.

Once improperly intertwined in the same hour by replaying an hour during "fall back" for daylight savings, there is no general solution to sorting things out.

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Jonathan Lewis
Sent: Friday, May 24, 2013 9:10 AM
To: Oracle Mailinglist
Subject: Re: add dummy rows


The simple partition outer join doesn't solve the generic version of your problem.

a) On the day you switch back from DST the output should "lose" an hour - partition outer join will re-insert it (though with null data)

b) On the day with the switch if you have input values for BOTH occurrences of 2 am you're okay, but if it appears only once then partition outer won't generate the second row you need, and if 2:00 am doesn't appear at all partition outer will only generate one row of the two you need.

In passing - how are you going to get the two versions of 2:00 am in the right order on the switch ?

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

----- Original Message -----
From: "Ls Cheng" <exriscer_at_gmail.com>
To: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>
Cc: "Oracle Mailinglist" <oracle-l_at_freelists.org>
Sent: Friday, May 24, 2013 10:47 AM
Subject: Re: add dummy rows




| Hi

|

| The example I posted was with test data. The real thing is that we

| have data for each hour in a day (24 rows) but when Daylight Savings

| Time

kicks


| in we will have 25 rows because the hour 2 is accounted twice in that

| specific day.

|

| So basically


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l