Thread: Timestamp trouble

Hello all,

I am getting hit by errors in timestamp formatting on seemingly valid timestamps:
19990108040506.

 From the PG manual:

<Valid input for the time stamp types consists of a concatenation of a date and a time, followed by an optional time
zone,followed by an optional AD or BC. (Alternatively, AD/BC can appear before the time zone, but this is not the
preferredordering.)> 

The string above is the concatenation of two ISO 8601 date+time fields but still, the error is thrown each time.

Is this a bug or is it a workaround for this?

The system I use:
WinXP, SP2
PG 8.0.1.

Thank you and best regards,
Razvan

On Wed, Jun 08, 2005 at 19:27:25 +0300,
  Razvan Costea-Barlutiu <cbrazvan@laitek.com> wrote:
> Hello all,
>
> I am getting hit by errors in timestamp formatting on seemingly valid timestamps:
> 19990108040506.
>
>  From the PG manual:
>
> <Valid input for the time stamp types consists of a concatenation of a date and a time, followed by an optional time
zone,followed by an optional AD or BC. (Alternatively, AD/BC can appear before the time zone, but this is not the
preferredordering.)> 
>
> The string above is the concatenation of two ISO 8601 date+time fields but still, the error is thrown each time.
>
> Is this a bug or is it a workaround for this?

This seems to work:

area=> select '19990108 040506'::timestamp;
      timestamp
---------------------
 1999-01-08 04:05:06
(1 row)

Razvan Costea-Barlutiu said:
> Hello all,
>
> I am getting hit by errors in timestamp formatting on seemingly valid
> timestamps:
> 19990108040506.
>
>  From the PG manual:
>
> <Valid input for the time stamp types consists of a concatenation of a
> date and a time, followed by an optional time zone, followed by an
> optional AD or BC. (Alternatively, AD/BC can appear before the time zone,
> but this is not the preferred ordering.)>
>
> The string above is the concatenation of two ISO 8601 date+time fields but
> still, the error is thrown each time.
>
> Is this a bug or is it a workaround for this?

Here is the actual parse order for date and time fields:
http://www.postgresql.org/docs/8.0/interactive/datetime-appendix.html

From that, I wouldn't see how it would figure out to parse a 12 digit
field.  I'd suggest breaking it down a little: putting a space in between
the date and time would probably be enough of a hint.

Daniel T. Staal

------------------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you are
expressly allowed to retransmit, quote, or otherwise use the contents
for non-commercial purposes.  This copyright will expire 5 years after
the author's death, or in 30 years, whichever is longer, unless such a
period is in excess of local copyright law.
------------------------------------------------------------------------

Thank you Bruno. Indeed, adding the space there did the trick, thanks a bunch for the lead.

However, is there any way to do this without interfering with the original timestamp string?

Thank you,
Razvan

>> Hello all,
>>
>> I am getting hit by errors in timestamp formatting on seemingly valid timestamps:
>> 19990108040506.
>>
>>  From the PG manual:
>>
>> <Valid input for the time stamp types consists of a concatenation of a date and a time, followed by an optional time
zone,followed by an optional AD or BC. (Alternatively, AD/BC can appear before the time zone, but this is not the
preferredordering.)> 
>>
>> The string above is the concatenation of two ISO 8601 date+time fields but still, the error is thrown each time.
>>
>> Is this a bug or is it a workaround for this?
>
>This seems to work:
>
>area=> select '19990108 040506'::timestamp;
>      timestamp
>---------------------
> 1999-01-08 04:05:06
>(1 row)