Обсуждение: timezone datetime issue
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
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
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')
On 16 April 2014 11:56, Glenn Pierce <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
Glenn Pierce wrote: > 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 ? The value stored in the database is always the UTC. So in your case you just have the wrong dates in the database. What about using UPDATE to change them? Yours, Laurenz Albe
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
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