Re: Some clarification about TIMESTAMP - Mailing list pgsql-general

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