Re: to_timestamp() and timestamp without time zone

On Friday, June 24, 2011 10:37:43 am hernan gonzalez wrote:
> > As I understand it, documentation patches are welcomed:)
>
> I'd indeed wish some radical changes to the documentation.
>
> To start with, the fundamental data type names are rather misleading; SQL
> standard sucks here, true, but Postgresql also has its idiosincracies, and
> the docs do not help much:
>
> http://www.postgresql.org/docs/9.0/interactive/datatype-datetime.html
>
> The most confusing thing is that PG's "TIMESTAMP WITH TIMEZONE" is NOT... a
> timestamp with timezone! (not even in the crippled sense of the SQL
> standard, which thinks of a "timestamp with offset"). It actually has no
> relation with timezones. It's actually a plain "timestamp", as the world is
> commonly used in computing, the "INSTANT" of time in which (typically) an
> event happened. It's a physical concept, not related with civil things
> (even with calendars).

The problem is you are over complicating things. The Postgres timestamp with
time zone is just that. It is a timestamp with timezone of UTC and an offset of
0. That is how it is stored. Given that, it is related to all timezones and is
calendar aware. The confusion arises in how the value is represented to the end
user. That is determined by the needs of the programmer/application/user.

>
> This is the type that should normally be used to record the time at which
> an event happened (typically a record modification - like MYSQL uses the
> world "TIMESTAMP").
>
> On the other hand, "TIMESTAMP WITHOUT TIMEZONE" is a wholy different
> concept (neither 'wider' or narrow' type than the other). It's just the
> local calendar time, it's (conceptually) like a tuple of
> numbers{year,month,day,hour,min,sec, fracsecs}, the "date and time" as it's
> understood in the business-civil world, with the timezone information
> missing.

I am not sure what business-civil world you live in, but the one I live in is
very timezone aware:) Local time is only relevant locally. The problem is
sooner or later you will need to interface with someone who is not local and
then the time comparison issues rear their head. At that point time zones become
essential.

> Hernan J Gonzalez

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com