Re: DATE field and daylight saving time

Hi John,

    Okay, let me plant both feet firmly in mouth. Why does this matter? The reason I ask is that there is only one hour per year where you may have conflict and since that hour is between 1 and 2 AM you shouldn't have very many transactions to worry about.

    That being the case why not opt for a simplistic solution. set up 2 cron jobs that run on the morning the clock is set back.

    The first job will run a 0059 and will set a system variable.

    The second job will run at 0200 and will create a spool file with all transactions that occurred between 0100 and 0200. This job will also set the name of the spool file using the current contents of the system variable (the one you set at 1259) and then change the contents of the system variable. This will give you two spool files with different names.

    Compare the two files and manually do whatever fix up is required.

regards

Jerry

j.penney_at_servicepower.com wrote in message <704gjb$b55$1_at_nnrp1.dejanews.com>...
>I have a problem (don't we all..?) and looking at the newsgroup via
DejaNews,
>it's been a while since this was discussed (at least in relation to the
>specifics).
>
>We have a table whose primary key is a DATE field. The dates we are
writing
>may potentially be in a time zone which has DST (daylight savings time).
All
>the dates *will* be in the same time zone. BUT: suppose I wrote records
every
>30 minutes and the clock went back (for example, from BST to GMT at
02:00am)
>then I'd get a sequence like this, say: 00:40 01:10 01:40 01:10 01:40 02:10
>Crunch! As far as I know (and Oracle seemed to confirm this when we asked)
as
>far as Oracle is concerned you can't encode a DST flag into the DATE field.
>
>So: has anyone got a flash of inspiration?
>
>Solutions we considered: a) Add an extra DST field: Well, yes, but it would
>mean a lot of rework for us and also it would complicate previously simple
>queries like "select my_date from my_table where my_date between
'somthing'
>and 'something else'" b) Changing the DATE field to a character
>representation of the date, something like "15/10/1998 01:30:00 DST". Same
>problem as above though.
>
>John Penney
>SERVICEPower Business Solutions Ltd
>Stockport
>England
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Thu Oct 15 1998 - 07:48:06 CDT