Timestamp shift when importing data

Jolles, Peter M (GE Infra, Energy) wrote:

I am trying to migrate several years of historical data with timestamps
from an MS Access database to Postgres. I am running into an issue where
specific dates/times get pushed one hour ahead, which creates duplicate
date/time stamps or failes the import if I have that defined as my
primary key. The time that gets shifted is always 2:00 AM to 2:55 AM
(data is in 5 minute blocks). What I don't understand is that it only
seems to happen on the following dates (m/d/yy format):

4/7/02
4/6/03
4/4/04
4/3/05
4/2/06

Daylight savings time shift?
- Jeremy

On Sat, Jan 3, 2009 at 5:59 PM, Jolles, Peter M (GE Infra, Energy)
<peter.jolles@ge.com> wrote:

For example, on these days, 4/7/02 2:00 AM imports to 4/7/02 3:00 AM.
4/6/03 2:15 AM imports as 4/6/03 3:15 AM, etc. All other dates and times
do not give any errors. I have tried to extract the date and create a
text field in MS Access, I get the same error when imported to Postgres.
Is there some significance to these dates?

Those are the dates of daylight savings time kicking in- which
happens, not coincidentally, at 2am.

What's the type of the field you're trying to import into, and how are
you doing the import?

(Sorry for the duplicate- missed sending to the list the first time.)
--
- David T. Wilson
david.t.wilson@gmail.com

On Saturday, January 03, 2009 6:27 PM, David T Wilson wrote:

Those are the dates of daylight savings time kicking in-
which happens, not coincidentally, at 2am.

What's the type of the field you're trying to import into,
and how are you doing the import?

That makes a lot more sense now, although I'm not sure why it is only
happening in the spring and not in the fall. The original data field is
a MS Access "General Date". In Postgres it is stored as a timestamp with
timezone.

To do the import, I tried using an Access append query. I've also tried
to use the Access export function.

Reading up on Windows XP handling of DST, it appears that it is
unreliable for pre-2007 time shifts, which would explain why it isn't
happening with more recent data. Is there any way to ignore DST in an
import/export transaction?

Thanks,
Peter

On Monday 05 January 2009 5:29:19 am Jolles, Peter M (GE Infra, Energy) wrote:

On Saturday, January 03, 2009 6:27 PM, David T Wilson wrote:

Those are the dates of daylight savings time kicking in-
which happens, not coincidentally, at 2am.

What's the type of the field you're trying to import into,
and how are you doing the import?

That makes a lot more sense now, although I'm not sure why it is only
happening in the spring and not in the fall. The original data field is
a MS Access "General Date". In Postgres it is stored as a timestamp with
timezone.

To do the import, I tried using an Access append query. I've also tried
to use the Access export function.

Reading up on Windows XP handling of DST, it appears that it is
unreliable for pre-2007 time shifts, which would explain why it isn't
happening with more recent data. Is there any way to ignore DST in an
import/export transaction?

One way would be to create a field with type "timestamp without timezone" and
import your timestamp data into that field.

Thanks,
Peter

--
Adrian Klaver
aklaver@comcast.net