Re: to_timestamp() and timestamp without time zone

On Thu, Jun 23, 2011 at 4:15 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>wrote:

> 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
>
>
My point is to parse a TIMESTAMP WITHOUT TIME ZONE - and that that should
NOT depend on the server/session TIMEZONE.

Try this:

# set TIMEZONE='XXX8';
# select to_timestamp('2007-12-30 00:30:00','YYYY-MM-DD
HH24:MI:SS')::timestamp;
2007-12-30 00:30:00
# set TIMEZONE='America/Argentina/Buenos_Aires';

select to_timestamp('2007-12-30 00:30:00','YYYY-MM-DD
HH24:MI:SS')::timestamp;
2007-12-30 01:30:00

> 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
>
>
Again: it works "sometimes". It should work always. Run this before and tell
me:

set TIMEZONE='America/Argentina/Buenos_Aires';

>
> 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
>
>
>

No, you can't. The "AT time zone 'UTC'" takes effect AFTER the parsing has
been done (using the session TIMEZONE), and if something was broken there,
to add after the "AT time zone 'UTC'" does NOT help.

I repeat: say I want to parse '30/12/2011 00:30:00' (or in other format)
and get the TIMESTAMP WITHOUT ZONE corresponding to that value (the same
I'd get by typing '2011:12:30 00:30:00'::timestamp ) independently of my
session Timezone.
Currently Postgresql does not give me some function to do reliably that.

Hernán