roundoff problem in time datatype

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: roundoff problem in time datatype
Date: 2005-09-25 17:26:25
Message-ID: 501.1127669185@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

Inserting into a time field with limited precision rounds off, which
is good except for this case:

regression=# select '23:59:59.9'::time(0);
time
----------
24:00:00
(1 row)

This is bad because:

regression=# select '24:00:00'::time(0);
ERROR: date/time field value out of range: "24:00:00"

which means that data originally accepted will fail to dump and reload.

I see this behavior in all versions back to 7.3. 7.2 was even more
broken:

regression=# select '23:59:59.9'::time(0);
time
----------
00:00:00
(1 row)

I think the correct behavior has to be to check for overflow again
after rounding off. Alternatively: why are we forbidding the value
24:00:00 anyway? Is there a reason not to allow the hours field
to exceed 23?

regards, tom lane

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Simon Riggs 2005-09-25 17:45:22 Re: Releasing memory during External sorting?
Previous Message Tom Lane 2005-09-25 15:50:14 Re: Vacuum questions...

Browse pgsql-patches by date

  From Date Subject
Next Message Simon Riggs 2005-09-25 18:31:36 Re: [HACKERS] statement logging / extended query protocol issues
Previous Message Martijn van Oosterhout 2005-09-25 09:18:36 Re: \x output blowing up