[HACKERS] Bad timestamp external representation
Hi everyone: I am using Postgresql 7.3.4 and found a problem inserting "Timestamp" objects throughJDBC Prepared Statements when the time zone is set to Canada/Newfoundland (3 hours and30 minutes from MGT). I am trying to insert "new Timestamp(0L)" into one of the fields.The database replies with an error message: Bad timestamp external representation '1969-12-31 20:30:00.00-030-30' Most likely, the database doesn't understand the last part of the timestamp, which is'-30' (30 minutes). It works properly only with time zones that don't have that additional half hour difference. I could not find any useful information regarding that issue. Any help would be appreciated. Thanx
Re: [HACKERS] Bad timestamp external representation
On Fri, 19 Mar 2004, Denis Khabas wrote: Hi everyone: I am using Postgresql 7.3.4 and found a problem inserting Timestamp objects through JDBC Prepared Statements when the time zone is set to Canada/Newfoundland (3 hours and 30 minutes from MGT). I am trying to insert new Timestamp(0L) into one of the fields. The database replies with an error message: This has been fixed in the 7.4 driver which is compatible with 7.3 servers. Try downloading it from http://jdbc.postgresql.org/download.html Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Bad timestamp external representation
Denis, This is more appropriate for the jdbc mail list. --Barry Denis Khabas wrote: Hi everyone: I am using Postgresql 7.3.4 and found a problem inserting Timestamp objects through JDBC Prepared Statements when the time zone is set to Canada/Newfoundland (3 hours and 30 minutes from MGT). I am trying to insert new Timestamp(0L) into one of the fields. The database replies with an error message: Bad timestamp external representation '1969-12-31 20:30:00.00-030-30' Most likely, the database doesn't understand the last part of the timestamp, which is '-30' (30 minutes). It works properly only with time zones that don't have that additional half hour difference. I could not find any useful information regarding that issue. Any help would be appreciated. Thanx ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Bad timestamp external representation 'Sun 05 May 11:53:44.731416 2002 EEST'
Hi I found a strange error (at least at first glance I had thought it seems so): mail=# select * from accounts_log where login='trading'; id | login | debet | credit | when --+-+---+---+-- 6289 | trading | 1170 | 1294.9071 | Wed 21 Mar 18:07:19 2001 EET (1 row) mail=# select * from accounts_log where login='trading' and when = '2001-03-21 18:07:19'; id | login | debet | credit | when --+-+---+---+-- 6289 | trading | 1170 | 1294.9071 | Wed 21 Mar 18:07:19 2001 EET (1 row) mail=# select * from accounts_log where login='trading' and when = '2001-03-21 18:07:19'; ERROR: Bad timestamp external representation 'Wed 04 Apr 20:00:56 2001 EEST' mail=# Could you add some comments to this ? Also I'd like to question if you don't mind: While now() outputs Sun 05 May 11:53:44.731416 2002 EEST It seems I can't use EEST (Eastern Europe Summer Time) in input: proba=# select * from temp; n | date ---+-- (0 rows) proba=# \d temp Table temp Column | Type | Modifiers +--+--- n | integer | date | timestamp with time zone | proba=# select * from temp where date = 'Sun 05 May 11:53:44.731416 2002 EEST'; ERROR: Bad timestamp external representation 'Sun 05 May 11:53:44.731416 2002 EEST' proba=# The EETDST time zone abbreviation works but it is inconvenient because all files produced with pg_dump utility or copy command contains EEST and I can't use then without some modifications e.g $ psql -e -f copy-command.sql proba Using pager is off. COPY temp FROM stdin; psql:copy-command.sql:1: ERROR: copy: line 2952, Bad timestamp external representation 'Mon 26 Mar 18:45:36 2001 EEST' psql:copy-command.sql:1: lost synchronization with server, resetting connection $ To be precise, DST time was started at 25 Mar 2001 at 01:00 UTC for our time zone (UTC+2) if it does matter. Could you suggest something ? Using PostgreSQL 7.2 on Slackware 8.0 Best regards -- Vladimir Zolotykh ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Bad timestamp external representation
On Wed, Jul 25, 2001 at 06:53:21PM -0400, Bruce Momjian wrote: I can confirm that current CVS sources have the same bug. It's a bug in timestamp output. # select '2001-07-24 15:55:59.999'::timestamp; ?column? --- 2001-07-24 15:55:60.00-04 (1 row) Richard Huxton wrote: From: tamsin [EMAIL PROTECTED] Hi, Just created a db from a pg_dump file and got this error: ERROR: copy: line 602, Bad timestamp external representation '2000-10-03 09:01:60.00+00' I guess its a bad representation because 09:01:60.00+00 is actually 09:02, but how could it have got into my database/can I do anything about it? The value must have been inserted by my app via JDBC, I can't insert that value directly via psql. Seem to remember a bug in either pg_dump or timestamp rendering causing rounding-up problems like this. If no-one else comes up with a definitive answer, check the list archives. If you're not running the latest release, check the change-log. It is not a bug, in general, to generate or accept times like 09:01:60. Leap seconds are inserted as the 60th second of a minute. ANSI C defines the range of struct member tm.tm_sec as seconds after the minute [0-61], inclusive, and strftime format %S as the second as a decimal number (00-61). A footnote mentions the range [0-61] for tm_sec allows for as many as two leap seconds. This is not to say that pg_dump should misrepresent stored times, but rather that PG should not reject those misrepresented times as being ill-formed. We were lucky that PG has the bug which causes it to reject these times, as it led to the other bug in pg_dump being noticed. We should access :60 seconds but we should round 59.99 to 1:00, right? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Bad timestamp external representation
On Thu, Jul 26, 2001 at 05:38:23PM -0400, Bruce Momjian wrote: Nathan Myers wrote: Bruce wrote: I can confirm that current CVS sources have the same bug. It's a bug in timestamp output. # select '2001-07-24 15:55:59.999'::timestamp; ?column? --- 2001-07-24 15:55:60.00-04 (1 row) Richard Huxton wrote: From: tamsin [EMAIL PROTECTED] Hi, Just created a db from a pg_dump file and got this error: ERROR: copy: line 602, Bad timestamp external representation '2000-10-03 09:01:60.00+00' I guess its a bad representation because 09:01:60.00+00 is actually 09:02, but how could it have got into my database/can I do anything about it? The value must have been inserted by my app via JDBC, I can't insert that value directly via psql. Seem to remember a bug in either pg_dump or timestamp rendering causing rounding-up problems like this. If no-one else comes up with a definitive answer, check the list archives. If you're not running the latest release, check the change-log. It is not a bug, in general, to generate or accept times like 09:01:60. Leap seconds are inserted as the 60th second of a minute. ANSI C defines the range of struct member tm.tm_sec as seconds after the minute [0-61], inclusive, and strftime format %S as the second as a decimal number (00-61). A footnote mentions the range [0-61] for tm_sec allows for as many as two leap seconds. This is not to say that pg_dump should misrepresent stored times, but rather that PG should not reject those misrepresented times as being ill-formed. We were lucky that PG has the bug which causes it to reject these times, as it led to the other bug in pg_dump being noticed. We should access :60 seconds but we should round 59.99 to 1:00, right? If the xx:59.999 occurred immediately before a leap second, rounding it up to (xx+1):00.00 would introduce an error of 1.001 seconds. As I understand it, the problem is in trying to round 59.999 to two digits. My question is, why is pg_dump representing times with less precision than PostgreSQL's internal format? Should pg_dump be lossy? Nathan Myers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Bad timestamp external representation
At 15:13 26/07/01 -0700, Nathan Myers wrote: Should pg_dump be lossy? No it shouldn't, but it already is because it uses decimal text reps of everything; we lose data when dumping floats as well. In the latter case we should dump the hex text reps to get the full bit width. Something similar is probably true for times etc. It's just a lot less readable. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Bad timestamp external representation
It is not a bug, in general, to generate or accept times like 09:01:60. Leap seconds are inserted as the 60th second of a minute. ANSI C defines the range of struct member tm.tm_sec as seconds after the minute [0-61], inclusive, and strftime format %S as the second as a decimal number (00-61). A footnote mentions the range [0-61] for tm_sec allows for as many as two leap seconds. This is not to say that pg_dump should misrepresent stored times, but rather that PG should not reject those misrepresented times as being ill-formed. We were lucky that PG has the bug which causes it to reject these times, as it led to the other bug in pg_dump being noticed. We should access :60 seconds but we should round 59.99 to 1:00, right? If the xx:59.999 occurred immediately before a leap second, rounding it up to (xx+1):00.00 would introduce an error of 1.001 seconds. Oh, so there is a good reason for showing :60. As I understand it, the problem is in trying to round 59.999 to two digits. My question is, why is pg_dump representing times with less precision than PostgreSQL's internal format? Should pg_dump be lossy? No idea. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
