Re: to_timestamp() and timestamp without time zone

On 06/23/2011 11:40 AM, hernan gonzalez wrote:
> Rather than being not viable, I'd argue that is is not correct.
> Rather, a simple direct cast will suffice:
> '2011-12-30 00:30:00'::timestamp without time zone
>
>
> That works only for that particular format. The point is that, for
> example, if I have some local date time
> stored as a string in other format ('30/12/2011 00:30:00') I cannot
> reliably parse it as a TIMESTAMP. Which I should.

Works here. I am in US PDT:

select to_timestamp('30/12/2011 00:30:00', 'DD/MM/YYYY HH24:MI:SS
')::timestamp with time zone;

to_timestamp
------------------------
2011-12-30 00:30:00-08

>
> Every feature and function in PostgreSQL is "potentially dangerous"
> - understanding them and using them correctly is the responsibility
> of the programmer. Time handling has lots of subtleties that take
> time to digest
>
>
> Thanks for the advice. But it's precisely in the role of a programmer
> who has digested a good deal about date-time data and its subtleties,
> and who is trying to use in a consistent an robust way date-time data
> that I'm asking this question. Or rather, reporting this issue.
>
> . It appears that you would like a timestamp of 2011-12-30 00:30:00
> which you can get. But even so, there are places in the world where
> that time exists and other places in the world that it does not.
>
> If you try to force that timestamp into a zone where it doesn't
> exist, PostgreSQL makes a reasonable interpretation of the intended
> point in time.
>
>
> I strongly disagree. I'm not trying "to force that timestamp into a
> zone" at all. I'm just telling postgresl to parse the string '30/12/2011
> 00:30:00' as a TIMESTAMP (without time zone), that is, to
> parse/understand/store it as the abstract/civil (wall calendar+clock)
> local datetime "30 dec 2011, 00 30 00 am" with NO association with a
> timezone.

Again works here:

test=> select to_timestamp('30/12/2011 00:30:00', 'DD/MM/YYYY HH24:MI:SS
')::timestamp without time zone;
to_timestamp
---------------------
2011-12-30 00:30:00

Postgreql does not need to interpret anything here, and indeed
> it works pefectly with this datetime if I store it in a TIMESTAMP
> WITHOUT TIMEZONE (it stores/manipulates it internally as UTC, but the
> programmer doesn't care about it, that is internal).

Actually that is how timestamp with timezone are stored:) If you don't
want to deal with time zones keep tz out of the loop. Store the values
in timestamp without time zone. If you at any point store it in a
timestamp with timezone or cast it to same you will change the value
based on whatever offset is in effect at that time. That is what is
supposed to happen.

> IT's only this particular function TO_TIMESTAMP() that have this
> problem, because it insists in "interpret" the local date time as a
> datetime with timezone (and can't even tell it to use UTC). This is just
> wrong.

Yes you can:

test=> select to_timestamp('30/12/2011 00:30:00', 'DD/MM/YYYY HH24:MI:SS
') at time zone 'UTC';
timezone
---------------------
2011-12-30 08:30:00

> Hernán

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