DATE field and daylight saving time

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 - 04:51:40 CDT