Thread: invalid input syntax for type timestamp.
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
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.
----- 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
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.
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
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)
"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)
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
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
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
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
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
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
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