Re: Insert statement changes timestamp value from MS Access

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:
>
>> "David Dabney" <David(dot)Dabney(at)noaa(dot)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
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: Don't 'kill -9' the postmaster
>>
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>