Re: to_timestamp() and timestamp without time zone

[hernan gonzalez <hgonzalez@xxxxxxxxx>]

On Thu, Jun 23, 2011 at 4:15 PM, Adrian Klaver <adrian.klaver@xxxxxxxxx> 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