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

Re: Top five challenges

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: silly sad <sad(at)bestmx(dot)ru>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, pgsql-advocacy <pgsql-advocacy(at)postgresql(dot)org>
Subject: Re: Top five challenges
Date: 2011-03-10 16:45:02
Message-ID: 201103101645.p2AGj2319472@momjian.us (view raw or flat)
Thread:
Lists: pgsql-advocacypgsql-www
silly sad wrote:
> On 03/10/11 19:01, Bruce Momjian wrote:
> > Alvaro Herrera wrote:
> >> Excerpts from sad's message of mi? mar 02 10:23:53 -0300 2011:
> >>> On Mar 2 2011, Josh Berkus wrote:
> >>
> >>>>> 3. To make TIMESTAMPTZ useful by introducing proper type cast
> >>>>> TIMESTAMPTZ to TIMESTAMP
> >>>>
> >>>> Why are you using Timestamp-no-tz at all?  I was thinking we should
> >>>> change the default ...
> >>>
> >>> Why did u introduced it?
> >>
> >> The standard requires the current behavior.  It's not going to change.
> >> It changed in 7.1 or so.
> > 
> > And we document why the default is so odd:
> > 
> > 	http://www.postgresql.org/docs/9.0/static/datatype-datetime.html#DATATYPE-TIMEZONES
> > 	
> > 	Note: The SQL standard requires that writing just timestamp be
> > 	equivalent to timestamp without time zone, and PostgreSQL honors that
> > 	behavior. (Releases prior to 7.3 treated it as timestamp with time
> > 	zone.) 
> 
> do you document why 'epoch'::timestamp is not the true 'epoch' unless
> timezone is not GMT?
> 
> if you mention any timestamp except 'epoch' it will be interpreted
> correctly taking in account timezone setting.

Well, 'epoch' clearly is a point in time with the hour being midnight at
GMT, so I don't see a problem with epoch making such an adjustment:

	test=> select 'epoch'::timestamp ;
	      timestamp
	---------------------
	 1970-01-01 00:00:00
	(1 row)
	
	test=> select 'epoch'::timestamptz;
	      timestamptz
	------------------------
	 1969-12-31 19:00:00-05
	(1 row)

However, a text string behaves the same:

	test=> select '1970-01-01 00:00:00'::timestamp;
	      timestamp
	---------------------
	 1970-01-01 00:00:00
	(1 row)

	test=> select '1970-01-01 00:00:00'::timestamptz;
	      timestamptz
	------------------------
	 1970-01-01 00:00:00-05
	(1 row)
	
Notice the "-05".  I would argue that 'epoch' has a predefined timezone.
We don't document this because it has never been mentioned before and no
one has mentioned they were surprised by the behavior.

-- 
  Bruce Momjian  <bruce(at)momjian(dot)us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

In response to

Responses

pgsql-www by date

Next:From: silly sadDate: 2011-03-10 16:45:44
Subject: Re: Top five challenges
Previous:From: Bruce MomjianDate: 2011-03-10 16:01:57
Subject: Re: Top five challenges

pgsql-advocacy by date

Next:From: silly sadDate: 2011-03-10 16:45:44
Subject: Re: Top five challenges
Previous:From: Bruce MomjianDate: 2011-03-10 16:01:57
Subject: Re: Top five challenges

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