From: | hernan gonzalez <hgonzalez(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Some clarification about TIMESTAMP |
Date: | 2011-05-31 16:45:10 |
Message-ID: | BANLkTimEqQxM0urXOZVWpWTgu_KQVfpp=A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I'm doing some tests with date-time related fields to design my web
application.
I was already dissatisfied with Postgresql handling of timezones
concepts (issue
already discussed here - not entirely PG's fault, rather a SQL thing)
and I vehemently
reject the idea of a global server-side timezone configuration having
any infuence on
my DB layer, so I am planning to use always plain TIMESTAMP data tipe
(with no TIMEZONE).
What I want is that a TIMESTAMP field to be conceptually equivalent to
a plain {YEAR,MONTH,DAY HH,MM,SS},
data-tuple, i.e. a "local time". To clarifiy, for me "local time" =
"timezone unkown". Which is is NOT the same
as assuming some default (OS or database) timezone. It might very well
happen that I store in a -say- ALARM_TIME two datetimes
that correspond to users that have different ("local") timezones. So,
I want '2011-05-31 10:00:00' in this field
to mean 10.00 AM in some UNKNOWN timezone (that of a particular user).
In this scenario, I assumed the natural convention is: store just a
UTC time, using a TIMESTAMP. I believe that's the idea
of a plain TIMESTAMP.
However, I'm not sure if I can get a totally timezone-indepent behaviour:
CREATE TABLE t1 ( ts timestamp without time zone);
db=# insert into t1 values('1970-01-01 00:00:00');
INSERT 0 1
db=# select ts,extract(epoch from ts) from t1;
ts | date_part
---------------------+-----------
1970-01-01 00:00:00 | 21600
I was dismayed to see this, I assumed that my insert has stored a unix
timestamp = 0.
It seems not?
But on the other side, if I modify the server timezone what gets
changed is the epoch calculation!
asdas=# SET TIMEZONE TO 'XXX11';
SET
asdas=# select ts,extract(epoch from ts) from t1;
ts | date_part
---------------------+-----------
1970-01-01 00:00:00 | 39600
Why? What is happening here?
--
Hernán J. González
http://hjg.com.ar/
From | Date | Subject | |
---|---|---|---|
Next Message | Emi Lu | 2011-05-31 16:51:35 | "postgresql-9.0-801.jdbc4.jar" always cause "org.postgresql.util.PSQLException: Cannot commit when autoCommit is enabled" Exception |
Previous Message | Raymond O'Donnell | 2011-05-31 16:19:54 | Re: troubles with initdb |