Thread: Timestamp shift when importing data
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 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? Thanks, Peter Jolles
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
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 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