Re: Some clarification about TIMESTAMP

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of hernan gonzalez
> Sent: Tuesday, May 31, 2011 12:45 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] Some clarification about TIMESTAMP
>
> 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).
>
> 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?
>

From the documentation for "extract":
"epoch

For date and timestamp values, the number of seconds since 1970-01-01
00:00:00 UTC (can be negative); for interval values, the total number of
seconds in the interval"

Since "epoch" is a timestamptz value (UTC) in order to subtract determine
the how many seconds has elapsed since the epoch the value being substracted
must be converted into a timestamptz. Since you stored a "local time"
without a timestamp when the conversion occurs it is done at local time (+6
or +11 in your examples).

David J.