Deadly
2010-04-29, 01:53 PM
Because this place is so friendly and full of people who know all sorts of strange things completely unrelated to comics and roleplaying (and because I can't seem to find a dedicated Postgres forum anywhere :smallfrown:)...
I'm tearing my hair out over what seems like a bug, but probably isn't, in PostgreSQL (version 8.4 on Ubuntu 9.10 in case it matters). It has to do with time zones, which I have never loved and can never figure out, so it may just be me being a fool. Anyway, I made a little test example, to see if I could figure out where exactly it goes wrong. Didn't help me, but here's the SQL with a few (hopefully helpful) comments:
-- create two test tables
CREATE TABLE test_with_zones (
id SERIAL PRIMARY KEY,
tstamp TIMESTAMP WITH TIME ZONE,
tzone TEXT -- a string such as '+3' or '-5' representing an offset from UTC+0
);
CREATE TABLE test_no_zones (
id SERIAL PRIMARY KEY,
tstamp TIMESTAMP WITHOUT TIME ZONE,
tzone TEXT
);
-- insert a little test data
INSERT INTO test_with_zones (tstamp,tzone) VALUES (now(), '+5');
INSERT INTO test_with_zones (tstamp,tzone) VALUES (now(), '-5');
INSERT INTO test_no_zones (tstamp,tzone) VALUES (now(), '+5');
INSERT INTO test_no_zones (tstamp,tzone) VALUES (now(), '-5');
-- select timestamp,
-- 1st column: using the tzone field as the time zone
-- 2nd column: not specifying any particular time zone,
-- 3rd column: using UTC+0
-- 4th column: the tzone field
SELECT timezone( 'UTC' || tzone, tstamp ),
tstamp,
tstamp AT TIME ZONE 'UTC+0',
tzone
FROM test_with_zones ;
SELECT timezone( 'UTC' || tzone, tstamp ),
tstamp,
tstamp AT TIME ZONE 'UTC+0',
tzone
FROM test_no_zones ;
This is what I get from the first SELECT:
"2010-04-29 13:01:11.412271" | "2010-04-29 20:01:11.412271+02" | "2010-04-29 18:01:11.412271" | "+5"
"2010-04-29 23:01:11.412271" | "2010-04-29 20:01:11.412271+02" | "2010-04-29 18:01:11.412271" | "-5"
And with the second SELECT:
"2010-04-30 03:01:11.412271+02" | "2010-04-29 20:01:11.412271" | "2010-04-29 22:01:11.412271+02" | "+5"
"2010-04-29 17:01:11.412271+02" | "2010-04-29 20:01:11.412271" | "2010-04-29 22:01:11.412271+02" | "-5"
I may not be good at figuring out timezones, but I'm pretty sure both are completely screwed up.
Both have the second column (pulled out of the table with no conversion) right: The time as it was when I executed these things, using the system time zone which for me is UTC+2.
The first one has the third column right too, as far as I can tell: It has properly been converted to UTC+0 by subtracting 2 hours from what was stored in the table. The second one does the opposite (adding 2) however, and I can't figure out why.
Lastly, the first column. In the first SELECT it once again seems to do exactly the opposite of what it is supposed to do: It adds the hours when it should subtract and subtracts when it should add. In the second SELECT it seems to do it right, except that it uses the same incorrect value for UTC+0 which it lists in column 3.
***
Ideally, I would like to store all timestamps in UTC+0 regardless of whatever the system time zone is, and convert them to whatever is needed upon retrieval only (based on the value of a field, such as 'tzone' in this example). I can't seem to get it to do that, however. So I tried the above, and got even more frustrated. Something just ain't right about this whole thing :smallsigh:
I'm tearing my hair out over what seems like a bug, but probably isn't, in PostgreSQL (version 8.4 on Ubuntu 9.10 in case it matters). It has to do with time zones, which I have never loved and can never figure out, so it may just be me being a fool. Anyway, I made a little test example, to see if I could figure out where exactly it goes wrong. Didn't help me, but here's the SQL with a few (hopefully helpful) comments:
-- create two test tables
CREATE TABLE test_with_zones (
id SERIAL PRIMARY KEY,
tstamp TIMESTAMP WITH TIME ZONE,
tzone TEXT -- a string such as '+3' or '-5' representing an offset from UTC+0
);
CREATE TABLE test_no_zones (
id SERIAL PRIMARY KEY,
tstamp TIMESTAMP WITHOUT TIME ZONE,
tzone TEXT
);
-- insert a little test data
INSERT INTO test_with_zones (tstamp,tzone) VALUES (now(), '+5');
INSERT INTO test_with_zones (tstamp,tzone) VALUES (now(), '-5');
INSERT INTO test_no_zones (tstamp,tzone) VALUES (now(), '+5');
INSERT INTO test_no_zones (tstamp,tzone) VALUES (now(), '-5');
-- select timestamp,
-- 1st column: using the tzone field as the time zone
-- 2nd column: not specifying any particular time zone,
-- 3rd column: using UTC+0
-- 4th column: the tzone field
SELECT timezone( 'UTC' || tzone, tstamp ),
tstamp,
tstamp AT TIME ZONE 'UTC+0',
tzone
FROM test_with_zones ;
SELECT timezone( 'UTC' || tzone, tstamp ),
tstamp,
tstamp AT TIME ZONE 'UTC+0',
tzone
FROM test_no_zones ;
This is what I get from the first SELECT:
"2010-04-29 13:01:11.412271" | "2010-04-29 20:01:11.412271+02" | "2010-04-29 18:01:11.412271" | "+5"
"2010-04-29 23:01:11.412271" | "2010-04-29 20:01:11.412271+02" | "2010-04-29 18:01:11.412271" | "-5"
And with the second SELECT:
"2010-04-30 03:01:11.412271+02" | "2010-04-29 20:01:11.412271" | "2010-04-29 22:01:11.412271+02" | "+5"
"2010-04-29 17:01:11.412271+02" | "2010-04-29 20:01:11.412271" | "2010-04-29 22:01:11.412271+02" | "-5"
I may not be good at figuring out timezones, but I'm pretty sure both are completely screwed up.
Both have the second column (pulled out of the table with no conversion) right: The time as it was when I executed these things, using the system time zone which for me is UTC+2.
The first one has the third column right too, as far as I can tell: It has properly been converted to UTC+0 by subtracting 2 hours from what was stored in the table. The second one does the opposite (adding 2) however, and I can't figure out why.
Lastly, the first column. In the first SELECT it once again seems to do exactly the opposite of what it is supposed to do: It adds the hours when it should subtract and subtracts when it should add. In the second SELECT it seems to do it right, except that it uses the same incorrect value for UTC+0 which it lists in column 3.
***
Ideally, I would like to store all timestamps in UTC+0 regardless of whatever the system time zone is, and convert them to whatever is needed upon retrieval only (based on the value of a field, such as 'tzone' in this example). I can't seem to get it to do that, however. So I tried the above, and got even more frustrated. Something just ain't right about this whole thing :smallsigh: