Skip site navigation (1) Skip section navigation (2)

Re: Some clarification about TIMESTAMP

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: hernan gonzalez <hgonzalez(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Some clarification about TIMESTAMP
Date: 2011-05-31 18:40:35
Message-ID: 27623.1306867235@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

Responses

pgsql-general by date

Next:From: hernan gonzalezDate: 2011-05-31 19:00:21
Subject: Re: Some clarification about TIMESTAMP
Previous:From: David JohnstonDate: 2011-05-31 18:16:56
Subject: Re: "postgresql-9.0-801.jdbc4.jar" always cause "org.postgresql.util.PSQLException: Cannot commit when autoCommit is enabled" Exception

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group