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@gmail.com
> <mailto:glennpierce@gmail.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.klaver@aklaver.com