Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2007
    Posts
    2

    Unanswered: to_timestamp problem

    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?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    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)

  3. #3
    Join Date
    Dec 2007
    Posts
    2
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •