Thread: invalid input syntax for type timestamp.

invalid input syntax for type timestamp.

From
"Hiroshi Saito"
Date:
Hi.

I was doing the field test of Slony-I 1.2.2 release now.
However, I encounter the problem of timestamp.:-(
Was anyone recognizing this problem?

Ver 8.1.5
saito=# select 'Fri Dec 15 14:26:05.502000 2006 JST'::timestamp;       timestamp
-------------------------2006-12-15 14:26:05.502

Ver 8.2.0
saito=# select 'Fri Dec 15 14:26:05.502000 2006 JST'::timestamp;
ERROR:  invalid input syntax for type timestamp: "Fri Dec 15 14:26:05.502000 2006 JST"

saito=# select 'Fri Dec 15 14:26:05.502000 2006 GMT'::timestamp;       timestamp
-------------------------2006-12-15 14:26:05.502


Umm, I might refrain from release for 8.2 of Slony-I for this problem. 

Regards,
Hiroshi Saito




Re: invalid input syntax for type timestamp.

From
Martijn van Oosterhout
Date:
On Fri, Dec 15, 2006 at 03:48:50PM +0900, Hiroshi Saito wrote:
> Hi.
>
> I was doing the field test of Slony-I 1.2.2 release now.
> However, I encounter the problem of timestamp.:-(
> Was anyone recognizing this problem?

The list of accepted timezones is now configurable, check through the
docs how to find the list and set it the way you want.

OTOH, postgres never uses timezone identifiers in output, so I'm not sure
why it would need to care.

Have anice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Re: invalid input syntax for type timestamp.

From
"Hiroshi Saito"
Date:
----- Original Message ----- 
From: "Martijn van Oosterhout" 
>> I was doing the field test of Slony-I 1.2.2 release now.
>> However, I encounter the problem of timestamp.:-(
>> Was anyone recognizing this problem?
>
> The list of accepted timezones is now configurable, check through the
> docs how to find the list and set it the way you want.
>
> OTOH, postgres never uses timezone identifiers in output, so I'm not sure
> why it would need to care.

Thanks.
I do not think that this is normal.....
As for 8.2..
SELECT timeofday()::timestamp;
ERROR:  invalid input syntax for type timestamp: "Fri Dec 15 14:26:05.502000 2006 JST"
As for 8.1.5..
SELECT timeofday()::timestamp;
        timestamp------------------------- 2006-12-15 14:26:05.502(1 row)

Regards,
Hiroshi Saito


Re: invalid input syntax for type timestamp.

From
Martijn van Oosterhout
Date:
On Fri, Dec 15, 2006 at 08:20:22PM +0900, Hiroshi Saito wrote:
> Thanks.
> I do not think that this is normal.....
>
> As for 8.2..
> SELECT timeofday()::timestamp;
> ERROR:  invalid input syntax for type timestamp: "Fri Dec 15
> 14:26:05.502000 2006 JST"

Err, sounds like a bug to me. Good ahead and file it. Should probably
change the timezone identifier to full form.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Re: invalid input syntax for type timestamp.

From
Tom Lane
Date:
Martijn van Oosterhout <kleptog@svana.org> writes:
> On Fri, Dec 15, 2006 at 03:48:50PM +0900, Hiroshi Saito wrote:
>> I was doing the field test of Slony-I 1.2.2 release now.
>> However, I encounter the problem of timestamp.:-(
>> Was anyone recognizing this problem?

> The list of accepted timezones is now configurable, check through the
> docs how to find the list and set it the way you want.

Yeah, but why the heck isn't JST in it by default?  This seems to have
slipped through the cracks, because I don't see it anywhere in any of
the timezonesets/* files.  Joachim?
        regards, tom lane


Re: invalid input syntax for type timestamp.

From
Christopher Browne
Date:
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Martijn van Oosterhout <kleptog@svana.org> writes:
>> On Fri, Dec 15, 2006 at 03:48:50PM +0900, Hiroshi Saito wrote:
>>> I was doing the field test of Slony-I 1.2.2 release now.
>>> However, I encounter the problem of timestamp.:-(
>>> Was anyone recognizing this problem?
>
>> The list of accepted timezones is now configurable, check through the
>> docs how to find the list and set it the way you want.
>
> Yeah, but why the heck isn't JST in it by default?  This seems to have
> slipped through the cracks, because I don't see it anywhere in any of
> the timezonesets/* files.  Joachim?

Is it absolutely certain that this is missing by default?

Our QA guys were doing some 8.1 work this week, and were griping about
GMT being missing, which turned out to be the result of only part of a
build having been copied into place.  (Apparently someone tried saving
space or some such thing; that's definitely a dumb idea!)
-- 
let name="cbbrowne" and tld="ca.afilias.info" in name ^ "@" ^ tld;;
<http://dba2.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)


Re: invalid input syntax for type timestamp.

From
Christopher Browne
Date:
"Hiroshi Saito" <z-saito@guitar.ocn.ne.jp> writes:
> Hi.
>
> I was doing the field test of Slony-I 1.2.2 release now.
> However, I encounter the problem of timestamp.:-(
> Was anyone recognizing this problem?
>
> Ver 8.1.5
> saito=# select 'Fri Dec 15 14:26:05.502000 2006 JST'::timestamp;
>        timestamp
> -------------------------
> 2006-12-15 14:26:05.502
>
> Ver 8.2.0
> saito=# select 'Fri Dec 15 14:26:05.502000 2006 JST'::timestamp;
> ERROR:  invalid input syntax for type timestamp: "Fri Dec 15 14:26:05.502000 2006 JST"
>
> saito=# select 'Fri Dec 15 14:26:05.502000 2006 GMT'::timestamp;
>        timestamp
> -------------------------
> 2006-12-15 14:26:05.502
>
>
> Umm, I might refrain from release for 8.2 of Slony-I for this problem.

This seems like the well-known issue that if there is some timezone
{unknownzone} that PostgreSQL is unaware of, you oughtn't operate
either Slony-I or PostgreSQL with TZ={unknownzone} or
PGTZ={unknownzone}.

JST strikes me as being a timezone that I'd be a tad surprised for
PostgreSQL NOT to recognize.  But that should represent a PostgreSQL
regression problem, not a problem with Slony-I...
-- 
let name="cbbrowne" and tld="ca.afilias.info" in String.concat "@" [name;tld];;
<http://dba2.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)


Re: invalid input syntax for type timestamp.

From
Joachim Wieland
Date:
On Fri, Dec 15, 2006 at 10:17:20AM -0500, Tom Lane wrote:
> >> However, I encounter the problem of timestamp.:-(
> >> Was anyone recognizing this problem?

> > The list of accepted timezones is now configurable, check through the
> > docs how to find the list and set it the way you want.

> Yeah, but why the heck isn't JST in it by default?  This seems to have
> slipped through the cracks, because I don't see it anywhere in any of
> the timezonesets/* files.  Joachim?

Yes, it got removed, see

   http://archives.postgresql.org/pgsql-patches/2006-07/msg00077.php

I checked each of the timezones I removed but I don't have my notes anymore
as to why I thought JST could be removed. Obviously, it shouldn't be
removed...

Appended is a patch that adds JST and JDT into Asia.txt and the Default set.


Joachim


Attachment

Re: invalid input syntax for type timestamp.

From
Tom Lane
Date:
Joachim Wieland <joe@mcknight.de> writes:
> Appended is a patch that adds JST and JDT into Asia.txt and the Default set.

Um ... is there a JDT?  Last I heard, Japan does not observe daylight
time.  I don't see a JDT in the old PG set of recognized zone names, and
I'd think we'd have heard complaints long ago if it were needed.
        regards, tom lane


Re: invalid input syntax for type timestamp.

From
Joachim Wieland
Date:
On Fri, Dec 15, 2006 at 11:37:56AM -0500, Tom Lane wrote:
> Joachim Wieland <joe@mcknight.de> writes:
> > Appended is a patch that adds JST and JDT into Asia.txt and the Default set.

> Um ... is there a JDT?  Last I heard, Japan does not observe daylight
> time.  I don't see a JDT in the old PG set of recognized zone names, and
> I'd think we'd have heard complaints long ago if it were needed.

True... Actually I wonder how I have to read the zic files in this case.
It says:

Rule    Japan    1948    only    -    May    Sun>=1    2:00    1:00    D
Rule    Japan    1948    1951    -    Sep    Sat>=8    2:00    0    S
Rule    Japan    1949    only    -    Apr    Sun>=1    2:00    1:00    D
Rule    Japan    1950    1951    -    May    Sun>=1    2:00    1:00    D

Zone    Asia/Tokyo    9:18:59    -    LMT    1887 Dec 31 15:00u        9:00    -    JST    1896        9:00    -    CJT
  1938        9:00    Japan    J%sT
 

The last column of the rules tell us what to put in for %s, but why is there
no

Rule    Japan    19xx    max    ....                                    S

rule? I.e. a rule saying that after some date we should always replace %s
by S? What am I missing here?


Joachim




Re: invalid input syntax for type timestamp.

From
Tom Lane
Date:
Joachim Wieland <joe@mcknight.de> writes:
> True... Actually I wonder how I have to read the zic files in this case.
> It says:

> Rule    Japan    1948    only    -    May    Sun>=1    2:00    1:00    D
> Rule    Japan    1948    1951    -    Sep    Sat>=8    2:00    0    S
> Rule    Japan    1949    only    -    Apr    Sun>=1    2:00    1:00    D
> Rule    Japan    1950    1951    -    May    Sun>=1    2:00    1:00    D

> Zone    Asia/Tokyo    9:18:59    -    LMT    1887 Dec 31 15:00u
>             9:00    -    JST    1896
>             9:00    -    CJT    1938
>             9:00    Japan    J%sT

It looks to me like this says that the only time Japan ever observed DST
was during the American occupation immediately after WWII.  I can well
imagine that they'll never adopt it on their own given that history.

Anyway, given the lack of complaints about our pre-8.2 behavior, it
seems clear that "JDT" is not an abbreviation in use now, so I'm content
to leave it out of the files.  Anybody working with historical info can
always add it for themselves.
        regards, tom lane


Re: invalid input syntax for type timestamp.

From
Joachim Wieland
Date:
On Sat, Dec 16, 2006 at 10:26:52AM -0500, Tom Lane wrote:
> Joachim Wieland <joe@mcknight.de> writes:
> > True... Actually I wonder how I have to read the zic files in this case.
> > It says:

> > Rule    Japan    1948    only    -    May    Sun>=1    2:00    1:00    D
> > Rule    Japan    1948    1951    -    Sep    Sat>=8    2:00    0    S
> > Rule    Japan    1949    only    -    Apr    Sun>=1    2:00    1:00    D
> > Rule    Japan    1950    1951    -    May    Sun>=1    2:00    1:00    D

> > Zone    Asia/Tokyo    9:18:59    -    LMT    1887 Dec 31 15:00u
> >             9:00    -    JST    1896
> >             9:00    -    CJT    1938
> >             9:00    Japan    J%sT

> It looks to me like this says that the only time Japan ever observed DST
> was during the American occupation immediately after WWII.  I can well
> imagine that they'll never adopt it on their own given that history.

Yes, I completely agree that JDT should not be included. I just wanted to
understand how those lines show that JST is still in active use. As far as I
understand it, it says that JST was observed from 1948 to 1951 (the second
rule) and now there is a time zone "J%sT" (because there is no "until"-date
in the last line) but there is no rule that tells us what to replace "%s"
with...


Joachim




Re: invalid input syntax for type timestamp.

From
Brian Hurt
Date:
Joachim Wieland wrote:

>Yes, I completely agree that JDT should not be included. I just wanted to
>understand how those lines show that JST is still in active use. As far as I
>understand it, it says that JST was observed from 1948 to 1951 (the second
>rule) and now there is a time zone "J%sT" (because there is no "until"-date
>in the last line) but there is no rule that tells us what to replace "%s"
>with...
>  
>

We're using JST as a time zone here, so I'd like to politely request 
that JST stay in the list of time zones, wether it is an "official" time 
zone or not.  Thanks.

Brian



Re: invalid input syntax for type timestamp.

From
Joachim Wieland
Date:
On Sat, Dec 16, 2006 at 12:52:12PM -0500, Brian Hurt wrote:
> Joachim Wieland wrote:

> >Yes, I completely agree that JDT should not be included. I just wanted to
> >understand how those lines show that JST is still in active use. As far as 
> >I
> >understand it, it says that JST was observed from 1948 to 1951 (the second
> >rule) and now there is a time zone "J%sT" (because there is no "until"-date
> >in the last line) but there is no rule that tells us what to replace "%s"
> >with...

> We're using JST as a time zone here, so I'd like to politely request 
> that JST stay in the list of time zones, wether it is an "official" time 
> zone or not.  Thanks.

No objection Brian, everybody agrees to put it back into the list of time
zones. I'm just trying to find out why it isn't there already. I think that
the missing rule (better: the rule that i think is missing) might be the
reason for why the conversion script that I used at that time did not output
it as a timezone that is still active.


Joachim