Re: timezone datetime issue

On 04/16/2014 04:19 AM, Glenn Pierce wrote:
> Although I guess something like this would do it ?
>
> UPDATE sensor_values ts = ts - interval (1 hour) WHERE ts BETWEEN
> ('2014-03-30 01:00', '2014-10-26 02:00')
>

I would say:

UPDATE sensor_values ts = ts - interval '1 hour' WHERE ts BETWEEN
('2014-03-30 01:00', '2014-10-26 02:00')

or

UPDATE sensor_values ts = ts - '1 hour'::interval WHERE ts BETWEEN
('2014-03-30 01:00', '2014-10-26 02:00')

I am assuming the BETWEEN clause in this case contains dummy values
because in your post you say only some values are incorrect.

>
> On 16 April 2014 11:56, Glenn Pierce <glennpierce(at)gmail(dot)com
> <mailto:glennpierce(at)gmail(dot)com>> 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 ?
>
> Thanks
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com