RE: add dummy rows

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 24 May 2013 10:33:36 -0400
Message-ID: <014001ce588b$ad8de8a0$08a9b9e0$_at_rsiz.com>

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.

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

Received on Fri May 24 2013 - 16:33:36 CEST