Re: More Info-Daylight Savings Problem

Two thoughts on your solution.

1. If you add a second then you don't need the column marking it as a special case. Since there is only one special case, it shows up in order without any extra work. And to interpret it's meaning you need to know that it is special, which you do without the special case column. (BTW, I'd add a minute to avoid the case where someone truncates the field because they "know" seconds don't matter)
2. This seems like a good place for a sequence AND the time. That way you have one thing that denotes uniqueness and order (the sequence) and a separate thing that relates to time-of-day which, in some cases, will require special handling. You also end up with a very small primary key (the sequence).

> Date: Mon, 29 Jan 1996 11:57:36 -0800
> Reply-to: "ORACLE database mailing list." <ORACLE-L_at_ccvm.sunysb.edu>
> From: "TSAWMILL.US.ORACLE.COM" <TSAWMILL_at_US.ORACLE.COM>
> Subject: More Info-Daylight Savings Problem
> X-To: oracle-l_at_ccvm.sunysb.edu
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_ccvm.sunysb.edu>

> I'm not directly involved, just a curious bystander, but I got more
> information. I am working at a large electric utility and incoming readings
> from industrial meters record energy usage by the hour. The time stamp is on
> the hour (no 1:15 or 1:30 times, just 1:00). The original design had a table
> with all dates/hours until the sun goes nova, and foreign key relationships
> from this thing all over the place. The old 'virtual entity' argument won out
> and a date function was used instead to validate the date/time and a unique
> key constraint added to prevent duplicates. The problem only comes up once a
> year in October when 2:am becomes 1:am again. Suggested solutions were to
> have a smart function add a second to the time on that day only, using
> Grenwich(sp?) Mean Time (someone suggested that on this list), and adding a
> column to the key denoting this one special case. This last one is the most
> popular so far, as the column will always be null except for 1:am Standard
> Time on the last Sunday in October.
>
>
>
>
> Tim Sawmiller
> tsawmiller_at_us.oracle.com
> "The opinions expressed here are my own and not necessarily those of the
> Oracle Corporation".
>

Mark Saltzman, Assistant Director
Information Systems, University of Wisconsin-Extension 432 North Lake Street, Madison, Wisconsin 53706-1498 TEL: 608.263.3084 / FAX: 608.262.2343 Received on Mon Jan 29 1996 - 17:07:46 CST