Re: timezone datetime issue - Mailing list pgsql-general

On 04/16/2014 03:56 AM, Glenn Pierce wrote:
> Hi I have an issue with adjusting a timestamp.
>
> I have a table  like
>
> CREATE TABLE sensor_values
> (
>       ts timestamp with time zone NOT NULL,
>       value double precision NOT NULL DEFAULT 'NaN'::real,
> )
>
> It was intended that ts timestamps would be the time we wanted to
> store in UTC.
> Clients would adjust their times to UTC before entering into the
> database.
>
>
> Unfortunately some values have been added with BST times.
> The DB thinks  they are UTC times but are an hour out this time of year.
>
> Is the a way to adjust those times ? Ie offset the summer times back
> an hour ?
>

I see two potential questions here.

If you are asking about the correct way to insert the data then you may
be confusing the issue by adding conversions unnecessarily. I can't tell
from your description but I'll hazard a guess that you may be doing
something similar to the following (using my time zone on the US Pacific
coast as an example):

1. You get a reading and a local time, say 2014-04-16 09:15:00. This
really means 2014-04-16 09:15:00-07 since we're on Pacific Daylight Time.
2. You convert that to UTC which would be 2014-04-16 16:15:00.
3. You insert that value into your data: insert into sensor_values (ts,
value) values ('2014-04-16 16:15:00', 1);
4. You note that the value in the database is not what you expected but
rather is 2014-04-16 16:15:00-07 or 2014-04-16 23:15:00 UTC.

If this is the case then the problem is that you are double converting.
The "time stamp with time zone" does not actually store any time zone
information and is better thought of as a "point in time." If I insert
2014-04-16 09:15:00-07 into a table and someone else, who has their
client set to UTC, views that record it will show 2014-04-16 16:15:00-00.

Further, if you insert data into a timestamptz column and omit the
explicit time-zone offset, PostgreSQL will assume you mean that the
value is your local time (or whatever you have set your client time zone
to).

If you are taking readings in the UK it's quite possible that such an
issue would not be apparent until the spring time change.

If the only issue is fixing incorrect data then you merely need to
identify the incorrect records. If all readings come from sensors in a
common time zone then you need to identify, probably by time range, the
block of bad data and update it by subtracting '1 hour'::interval.
Naturally the data-repair needs to be coordinated with fixing the bug
that caused the incorrect entries in the first place. (It's easy to get
things backward or choose incorrect blocks so I'd make a backup of the
table first.)

If the readings come from sensors across different time zones then you
will be tasked with the issue of somehow identifying which records need
correcting and which don't - possibly a large task.

Cheers,
Steve