PostgreSQL Bugs - Re: Insert statement changes timestamp value from MS Access

Author

Re: Insert statement changes timestamp value from MS Access

David Dabney

2005-07-27, 1:29 pm

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

Tom Lane

2005-07-28, 3:24 am

"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/...-reporting.html

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

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

David Dabney

2005-07-28, 9:25 am

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:

>"David Dabney" <David.Dabney@noaa.gov> writes:
>
>
>
>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/...-reporting.html
>
>The short and sweet version of it is "give us a SQL script that
>delivers a wrong result" ...
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster
>
>

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

David Dabney

2005-07-28, 9:25 am

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:

> 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:
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Richard Huxton

2005-07-28, 11:36 am

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Sponsored Links