To: andrew@supernews.com
Cc: pgsql-sql@postgresql.org, Bret Hughes <bhughes@elevating.com>
Subject: Re: definative way to place secs from epoc into timestamp 
In-reply-to: <slrnd2g2nm.29om.andrew+nonews@trinity.supernews.net> 
References: <1109780719.28180.86.camel@bretsony>
	<13089.1109793126@sss.pgh.pa.us>
	<1109794066.27857.100.camel@bretsony>
	<13466.1109795186@sss.pgh.pa.us>
	<1109828560.27905.137.camel@bretsony>
	<2bf00b6e6300e2c76bc3c09fa89e7913@myrealbox.com>
	<1109834845.27905.147.camel@bretsony>
	<slrnd2euj5.29om.andrew+nonews@trinity.supernews.net>
	<1109915231.28180.182.camel@bretsony>
	<slrnd2g2nm.29om.andrew+nonews@trinity.supernews.net>
Comments: In-reply-to Andrew - Supernews <andrew+nonews@supernews.com>
	message dated "Fri, 04 Mar 2005 07:15:02 +0000"
Date: Fri, 04 Mar 2005 10:14:14 -0500
Message-ID: <27400.1109949254@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Andrew - Supernews <andrew+nonews@supernews.com> writes:
> On 2005-03-04, Bret Hughes <bhughes@elevating.com> wrote:
>>> (Why are you using timestamp without time zone anyway? For recording the
>> 
>> Valid question.  Because there is no reason to keep up with time zones

> It's a common mistake to think that just because you don't need to keep
> track of time zones that somehow using timestamp without time zone is
> correct. It is _not_. "timestamp with time zone" and "timestamp without
> time zone" have _very_ different semantics.

> [ excellent example snipped ]

It's curious that people who say they want Unix timestamps find it so
hard to wrap their heads around this, because in fact "timestamp with
time zone" operates EXACTLY the way that Unix timekeeping is done.
Consider this:

	$ export TZ=GMT
	$ date
	Fri Mar  4 15:11:31 GMT 2005
	$ export TZ=EST5EDT
	$ date
	Fri Mar  4 10:11:35 EST 2005

The system's internal idea of the time didn't change (modulo the few
seconds it took to type the commands), but the way it is displayed
changed.

			regards, tom lane