to_timestamp problem

leokom · #1 (**permalink**) 12-21-07, 05:19

I need to convert string with timezone into timestamp with timezone. Example from docs without timezone works OK:

SELECT to_timestamp(
'05 JAN 2000+00',
'DD MON YYYY'
)

According to the specification I've tries to convert the string like:

select to_timestamp( '05 Dec 2000 UTC', 'DD Mon YYYY TZ' )

or

select to_timestamp( 12 Jan 2000 -05', 'DD Mon YYYY TZ' )

but received an error:
ERROR: "TZ"/"tz" not supported

Postgre version 8.0.1, and documentation for this version says that TZ is supported.
Does anybody know the solution?

shammat · #2 (**permalink**) 12-21-07, 05:41

A timezone without a time information does not really make sense to me, I think you should at least specifiy 00:00 for the time part.

The description for the "TZ" string in the manual says: "time-zone name (uppercase)". My understanding of that is, that this is an output format and would show the name of the timezone, not the offset. I don't think this is supported as an input format.

You could try something like:

Code:

select timestamp with time zone '2000-01-05 00:00+02'

(I tried this with 8.2, so I don't know if it will work with 8.0)

leokom · #3 (**permalink**) 12-21-07, 08:55

Quote:

Originally Posted by shammat

A timezone without a time information does not really make sense to me, I think you should at least specifiy 00:00 for the time part.

The description for the "TZ" string in the manual says: "time-zone name (uppercase)". My understanding of that is, that this is an output format and would show the name of the timezone, not the offset. I don't think this is supported as an input format.

You could try something like:

Code:

select timestamp with time zone '2000-01-05 00:00+02'

(I tried this with 8.2, so I don't know if it will work with 8.0)

Your code works OK, but it's not what I need.

Probably I've shown not totally correct example,
but changing it to smth like this

Code:

SELECT to_timestamp('00:00:05.601 UTC Tue Jun 28 2005',
'HH24:MI:SS.MS TZ Dy Mon DD YYYY');

doesn't work too.

What I need in fact - I have a varchar field, where there is stored timestamp with timezone, and I need it to be converted to correct timestamp. It should be done in stored procedure so I can't do smth like

Code:

select timestamp with timezone 'qqq'

as the string will be dynamically fetched from db.