Closed agstephens closed 3 years ago
@jhaigh0 looks promising!
Possible test/solution, try writing a script of SQL commands that you can run with psql ... -f test-script.sql
:
@jhaigh0: thanks for looking into this.
I have just run a "SELECT" command as mentioned in #73. The funny thing is, now we have (re-)set the timezone, the result is actually different. It actually looks like setting UTC fixes the queries anyway:
cdm=> SHOW timezone;
TimeZone
----------
UTC
(1 row)
cdm=> select station_name, date_time from lite_2_0.observations_1755_land_0 WHERE date_time BETWEEN '1755-01-01 06:00:00'::timestamptz AND '1755-01-03 07:00:00+00:00'::timestamptz LIMIT 1;
station_name | date_time
--------------+------------------------
BASEL | 1755-01-01 06:29:00+00
(1 row)
Please see the top-panel in #73 to see how this is different to query I ran then (with TimeZone: 'Europe/London'
So, the situation might be as simple as altering the column type. Please can you test that this will work if we alter the column type to remove the time zone. Hopefully it simplifies your test even more. Please document your testing here. Thanks
This is what I've found that seems work;
First set up a test table like the real data
abcunit_jh=> set timezone to 'Europe/London';
SET
abcunit_jh=> CREATE TABLE time_data(id serial PRIMARY KEY, dates TIMESTAMPTZ NOT NULL);
CREATE TABLE
abcunit_jh=> INSERT INTO time_data (dates) VALUES('1800-06-22 19:00:00');
INSERT 0 1
abcunit_jh=> SELECT * FROM time_data;
id | dates
----+------------------------------
1 | 1800-06-22 19:00:00-00:01:15
(1 row)
Then remove timezone info
abcunit_jh=> ALTER TABLE time_data ALTER COLUMN dates TYPE timestamp;
ALTER TABLE
abcunit_jh=> SELECT * FROM time_data;
id | dates
----+---------------------
1 | 1800-06-22 19:00:00
(1 row)
so we should just be able to run this alter table command on the columns that have the problem, if our end goal is to remove timezone info and just assume all is in utc. @agstephens
Thanks @jhaigh0, that looks great. It looks like it is actually easier to solve than I thought. I'll have a think about how/whether we need to make a change for r2.0. For r3.0, we'll build it with UTC only.
At present, the global timezone change to UTC seems to have fixed the issue that was raised by the CDS Team. I have asked them if we should completely remove the timezone from the returned data. I would propose that that would be the best option, for simplicity and future usage.
No further action needed on this.
In the current r2.0 contents of the GLAMOD DB, we have some strange date/times that include a time zone. Where they have been read in by Pandas they were somehow modified. This relates to:
https://github.com/glamod/glamod-ingest/issues/73
What we need to do with the existing Postgres content is:
timestamp with timezone
totimestamp without timezone
(https://www.postgresql.org/docs/11/datatype-datetime.html)@jhaigh0 Please can you create you own test Postgres DB and create a simple table with a single column
date_time
- populate it with some values and then do some googling and testing to find out the best way for us to do the datetime conversion so that we end up removing the timezone using a simple column type:timestamp without timezone
Thanks