On 05/31/2011 09:45 AM, hernan gonzalez wrote:
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:
All is well - everything is under your control. It just takes a bit of
time to understand how time calculations work.
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?
Understand that you have basically requested, whether you realized or
not, "at what instant, displayed as a UNIX epoch, will it be midnight
January 1, 1970 in this time zone". You, as the programmer, have
complete control over what time zone is used for such conversions and
PostgreSQL will do the work for you.
If you reverse the calculation, you will see that indeed it is midnight
January 1, 1970 in your location:
select abstime(21600);
abstime
------------------------
1970-01-01 00:00:00-06
Were you in England:
set timezone to 'UTC';
SET
select ts,extract(epoch from ts) from t1;
ts | date_part
---------------------+-----------
1970-01-01 00:00:00 | 0
Note: Most calculations that ask for a timestamp *without* time zone at
a specific time zone return a timestamp *with* time zone - you are
essentially adding timezone information to get a point in time.
Conversely, most calculations that ask for a timestamp *with* time zone
(point in time) at a specific zone return a timestamp *without* time
zone - you told it the time zone so you must only need the time stamp.
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?
See above re: the calculation but note that you are not modifying the
server, you are telling the server the time zone to use for date/time
calculations for this client - no other client is affected.
So choose the data type that is appropriate for your app. If you need to
represent specific points in time (the shuttle launched at..., the
teleconference starts at...) use a timestamp with time zone. For data
that is more "time of day" relevant (lunch is served at 11:30) use a
timestamp without time zone.
Before condemning PostgreSQL's date/time handling, read and reread the
section on date and time data-types and calculations. They are very
powerful, useful and logical once understood.
Cheers,
Steve
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
