Insert statement changes timestamp value from MS Access ODBC

I'm sorry, but I attached the incorrect file the first time!

"David Dabney" <David.Dabney@noaa.gov> writes:

I'm assuming this is a bug.

Perhaps, but there is absolutely no chance of anyone reproducing the
problem from the information you've offered. You say "it fails when
I try to insert data from table DD5", but you give us no clue about
what data is in DD5.

There is some generic advice about how to create a useful bug report
here:
http://www.postgresql.org/docs/8.0/static/bug-reporting.html

The short and sweet version of it is "give us a SQL script that
delivers a wrong result" ...

regards, tom lane

Tom,

Thanks for the quick reply and I'm sorry I wasn't explicit enough in my
description. At the top of the attachment there is this section which
is the piece of data from DD5 that is causing the problem:

****************
MS Access Details:

DATA TO IMPORT FROM LOCAL MSACCESS Table into linked ODBC pg table:
ID original_deployment_code date_time water_temp
depth_m salinity sp_cond do_per do_mg_l ph
1 MLM20010327 2001-04-01 02:00:00 17.860001
1.49352 30.1 46.299999 80.400002 6.42 7.64
2 MLM20010327 2001-04-01 02:30:00 17.700001
1.61544 30.200001 46.400002 78.699997 6.3 7.64
3 MLM20010327 2001-04-01 03:00:00 17.67
1.64592 30.200001 46.400002 72.800003 5.84 7.62
4 MLM20010327 2001-04-01 03:30:00 17.639999
1.524 30.1 46.299999 79.300003 6.36 7.61
********************

I needed to get this data in, so I tried to manually correct the invalid
date_time field after inserting and then put the uniqueness constraint
back on. From EMS I tried to change the times from 3:30 and 3:00 to
2:30 and 2:00. When I refresh the data it now shows 1:30 and 1:00 for
these records! I then tried to change 1:30 and 1:00 to 2:30 and 2:00
and it went back to 3:30 and 3:00.

So I backed up and tried to import directly from psql. The same results
happen as when I did it from MS Access.

I was driving home and realized 4/1/2001 was probably when daylight
savings time changed.....and it was. So I'm not sure if this is a bug
or not.

Regards,

Dave

Tom Lane wrote:

The more I thought about this I realized it's not a bug and I'm sorry to
have perstered the list. The fact that the data logger was deployed
before and after a time change and it was not setup to update itself
created this problem. I just have to push forward all the times after
the change and then import the data. We've been trying to get the
scientists to use UTC.....maybe this will help them understand why they
should be doing that.

Thanks,

Dave

PS Postgres rocks. Please keep up the good work!

David Dabney wrote:

David Dabney wrote:

The more I thought about this I realized it's not a bug and I'm sorry to
have perstered the list. The fact that the data logger was deployed
before and after a time change and it was not setup to update itself
created this problem. I just have to push forward all the times after
the change and then import the data. We've been trying to get the
scientists to use UTC.....maybe this will help them understand why they
should be doing that.

The other thing you should do is use "timestamp with time zone" to store
your timestamps. Otherwise, you aren't storing an absolute time at all.

--
Richard Huxton
Archonet Ltd