summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-08-06 17:44:11 +0200
committerYorhel <git@yorhel.nl>2009-08-06 17:44:11 +0200
commit1efef2763236ab42d71a34ed90a1a31579443b9d (patch)
tree28973d186cc76d6ca2e8beb487d578cffc2354b8
parent753a2d8c6b69ae86e5968b9672992b7b60341067 (diff)
Use timestamptz data type instead of timestamp
It took me a while to figure out that the "timestamp with timezone" PostgreSQL type doesn't actually store the time zone, it simply stores and calculates on UTC, and only converts it to the connection time zone in the formatting stage before sending it to the SQL client. When working with date/times in PgSQL, the NOW() (and related) functions return a timestamp with time zone. Upon converting that to a timestamp without time zone type, the current timezone setting is used. Meaning that comparing a column of type timestamp without timezone to NOW() will only work if all data in the columns is stored in the same timezone as that of the current connection, which in turn means that changing this setting at any point will cause all data in timestamp without timezone columns to be incorrectly interpreted. I often praise PostgreSQL for their comprehensive documentation, but they could've pointed this out more clearly...
-rw-r--r--util/dump.sql4
-rw-r--r--util/updates/update_2.6.sql4
2 files changed, 4 insertions, 4 deletions
diff --git a/util/dump.sql b/util/dump.sql
index bdff5c6b..3a470102 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -26,7 +26,7 @@ CREATE TABLE anime (
type smallint,
title_romaji,
title_kanji,
- lastfetch timestamp
+ lastfetch timestamptz
);
-- changes
@@ -165,7 +165,7 @@ CREATE TABLE screenshots (
CREATE TABLE sessions (
uid integer NOT NULL,
token bytea NOT NULL,
- expiration timestamp NOT NULL DEFAULT (now() + '1 year'::interval),
+ expiration timestamptz NOT NULL DEFAULT (now() + '1 year'::interval),
PRIMARY KEY (uid, token)
);
diff --git a/util/updates/update_2.6.sql b/util/updates/update_2.6.sql
index a5ae03f6..06722cc2 100644
--- a/util/updates/update_2.6.sql
+++ b/util/updates/update_2.6.sql
@@ -4,7 +4,7 @@
CREATE TABLE sessions (
uid integer NOT NULL REFERENCES users(id),
token bytea NOT NULL,
- expiration timestamp NOT NULL DEFAULT (NOW() + '1 year'::interval),
+ expiration timestamptz NOT NULL DEFAULT (NOW() + '1 year'::interval),
PRIMARY KEY (uid, token)
);
@@ -19,7 +19,7 @@ ALTER TABLE users ADD COLUMN salt character(9) NOT NULL DEFAULT ''::bpchar;
ALTER TABLE anime ALTER COLUMN lastfetch DROP NOT NULL;
ALTER TABLE anime ALTER COLUMN lastfetch DROP DEFAULT;
UPDATE anime SET lastfetch = NULL WHERE lastfetch <= 0;
-ALTER TABLE anime ALTER COLUMN lastfetch TYPE timestamp USING timestamp 'epoch' + lastfetch * interval '1 second';
+ALTER TABLE anime ALTER COLUMN lastfetch TYPE timestamptz USING 'epoch'::timestamptz + lastfetch * interval '1 second';
ALTER TABLE anime ALTER COLUMN ann_id DROP NOT NULL;
ALTER TABLE anime ALTER COLUMN ann_id DROP DEFAULT;