> -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of hernan gonzalez > Sent: Tuesday, May 31, 2011 12:45 PM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
