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