Re: Some clarification about TIMESTAMP

hernan gonzalez <hgonzalez(at)gmail(dot)com> writes:
> 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).

Frankly, that position seems pretty silly. There are any number of
server-side settings that can affect the interpretation (and display)
of your data. Datestyle for example already renders this position
untenable.

> 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?

It did, but extract(epoch) assumes you want a distance from the real
Unix epoch, so it takes the timestamp as being in local zone. AFAIR
there isn't a function that does exactly what you seem to be thinking
of.

regards, tom lane