Thread: Question about Postgresql time fields(possible bug)
Hi, I just noticed today that Postgresql accepts a value of 24:00:00, this is for sure not correct as there is no such thing as 24:00:00 PG Admin III will display this value just fine which is also incorrect, PG Lightning Admin catches it as a invalid time, but shouldn't there be some validation of times and dates at the server level? There are people who are using PG Admin III and they don't even know they have bogus dates and times in their databases. Thanks, Tony
Tony Caduto wrote: > Hi, > I just noticed today that Postgresql accepts a value of 24:00:00, this > is for sure not correct as there is no such thing as 24:00:00 > > PG Admin III will display this value just fine which is also incorrect, > PG Lightning Admin catches it as a invalid time, but shouldn't there be > some validation of times and dates at the server level? > > There are people who are using PG Admin III and they don't even know > they have bogus dates and times in their databases. A leap second will show as 24:00:00. It is a valid time. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
> -----Original Message----- > From: pgsql-hackers-owner@postgresql.org > [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Tony Caduto > Sent: 10 January 2006 15:38 > To: pgsql-hackers@postgresql.org > Subject: [HACKERS] Question about Postgresql time fields(possible bug) > > Hi, > I just noticed today that Postgresql accepts a value of > 24:00:00, this > is for sure not correct as there is no such thing as 24:00:00 > > PG Admin III will display this value just fine which is also > incorrect, > PG Lightning Admin catches it as a invalid time, but > shouldn't there be > some validation of times and dates at the server level? > > There are people who are using PG Admin III and they don't even know > they have bogus dates and times in their databases. pgAdmin III leaves all data checks in the hands of the database and doesn't try to second guess what may or may not be valid - constraints and regional settings might easily affect what is or isn't valid or how client data is interpreted by the server. Besides, 24:00:00 is an accepted way of indicating a leap second. http://en.wikipedia.org/wiki/24-hour_notation Regards, Dave
In article <200601101551.k0AFpnK17299@candle.pha.pa.us>, Bruce Momjian <pgman@candle.pha.pa.us> writes: > Tony Caduto wrote: >> Hi, >> I just noticed today that Postgresql accepts a value of 24:00:00, this >> is for sure not correct as there is no such thing as 24:00:00 >> >> PG Admin III will display this value just fine which is also incorrect, >> PG Lightning Admin catches it as a invalid time, but shouldn't there be >> some validation of times and dates at the server level? >> >> There are people who are using PG Admin III and they don't even know >> they have bogus dates and times in their databases. > A leap second will show as 24:00:00. It is a valid time. Shouldn't such a leap second be represented as '... 23:59:60'?
Harald Fuchs <hf0923x@protecting.net> writes:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> A leap second will show as 24:00:00. It is a valid time.
> Shouldn't such a leap second be represented as '... 23:59:60'?
People who didn't like 24:00:00 would complain about that, too ;-)
Actually, my recollection is that we decided to allow 24:00:00 for
reasons unrelated to leap seconds. See the archives --- this was
debated and agreed to not all that long ago. PG 8.0 and before
don't allow it.
regards, tom lane
> -----Original Message----- > From: pgsql-hackers-owner@postgresql.org > [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Harald Fuchs > Sent: 10 January 2006 16:53 > To: pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] Question about Postgresql time > fields(possible bug) > > In article <200601101551.k0AFpnK17299@candle.pha.pa.us>, > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > > Tony Caduto wrote: > >> Hi, > >> I just noticed today that Postgresql accepts a value of > 24:00:00, this > >> is for sure not correct as there is no such thing as 24:00:00 > >> > >> PG Admin III will display this value just fine which is > also incorrect, > >> PG Lightning Admin catches it as a invalid time, but > shouldn't there be > >> some validation of times and dates at the server level? > >> > >> There are people who are using PG Admin III and they don't > even know > >> they have bogus dates and times in their databases. > > > A leap second will show as 24:00:00. It is a valid time. > > Shouldn't such a leap second be represented as '... 23:59:60'? On looking further it appears to me that 24:00:00 is not a leap second (which definitely can be 23:50:60), but just another way of expressing midnight. From: http://www.cl.cam.ac.uk/~mgk25/iso-time.html -------------- As every day both starts and ends with midnight, the two notations 00:00 and 24:00 are available to distinguish the two midnights that can be associated with one date. This means that the following two notations refer to exactly the same point in time: 1995-02-04 24:00 = 1995-02-05 00:00 -------------- So: postgres=# select ('1995-02-04 24:00'::timestamp = '1995-02-05 00:00'::timestamp);?column? ----------t (1 row) Regards, Dave.
In article <1292.1136913298@sss.pgh.pa.us>, Tom Lane <tgl@sss.pgh.pa.us> writes: > Harald Fuchs <hf0923x@protecting.net> writes: >> Bruce Momjian <pgman@candle.pha.pa.us> writes: >>> A leap second will show as 24:00:00. It is a valid time. >> Shouldn't such a leap second be represented as '... 23:59:60'? > People who didn't like 24:00:00 would complain about that, too ;-) Well, Richard T. Snodgrass says in "Developing Time-Oriented Database Applications in SQL" (pg. 81) the following: > Most days have 24 hours. The day in April that daylight saving time > kicks in has only 23 hours; the day in October that daylight saving > time ends contains 25 hours. Similarly, minutes can have 62 seconds > (though up to 1999 only one leap second has ever been added to any > particular minute), as mentioned in this standard [44, p. 25]. where ref [44] is > ISO, Database Language SQL. ISO/IEC 9075: 1992. ANSI X3.135-1992 To me this sounds like 23:59:60, doesn't it?
On 10/1/06 18:00, "Tony Caduto" <tony_caduto@amsoftwaredesign.com> wrote: > Dave Page wrote: > >> >> On looking further it appears to me that 24:00:00 is not a leap second >> (which definitely can be 23:50:60), but just another way of expressing >> midnight. >> >> From: http://www.cl.cam.ac.uk/~mgk25/iso-time.html >> >> >> > Hi Dave, > That may be true, but I don't think 24:00:00 is the standard way of > doing it, have you ever seen your PC clock roll over to 24:00:00 > > For a PC/server based bios clock 24:00:00 is not a valid time, a lot of > programming languages datetime routines will not accept a time of 24:00:00. Hi Tony, That's not really the point. The ISO 8601 standard allows midnight to be expressed as 00:00:00 or 24:00:00 to enable you to tell which midnight is being referred to (ie. The beginning or the end of the day). PostgreSQL allows you to make use of that part of the standard, and as admin tool authors I think we should honour what it allows, provided it's not blatantly non-standard. It's up to the user to decide whether or not they actually make use of the facility. Just my tuppence worth :-) Regards, Dave.
Tony, Dave, > That's not really the point. The ISO 8601 standard allows midnight to be > expressed as 00:00:00 or 24:00:00 to enable you to tell which midnight > is being referred to (ie. The beginning or the end of the day). IIRC, the reason for supporting 24:00:00 is that some popular client languages (including PHP, I think) use this number to express "midnight". I personally also find it a useful way to distinguish between "blank time" (00:00) an "specifically intentionally midnight" (24:00). -- --Josh Josh Berkus Aglio Database Solutions San Francisco
Dave Page <dpage@vale-housing.co.uk> writes:
> That's not really the point. The ISO 8601 standard allows midnight to be
> expressed as 00:00:00 or 24:00:00 to enable you to tell which midnight is
> being referred to (ie. The beginning or the end of the day).
There are other reasons for allowing it that have nothing to do with
that, either. IIRC the argument that carried the day involved roundoff
behavior. In 8.0 and before you can do this:
regression=# select '23:59:59.99'::time(0); time
----------24:00:00
(1 row)
If you disallow 24:00:00 then there are legal values of time(n) that
will fail to round off to time(0). What's worse, data that was accepted
and rounded off by prior releases will fail to reload after a dump. It
was a complaint from a user who got burnt by that behavior that got us
thinking about it.
regards, tom lane
> That's not really the point. The ISO 8601 standard allows midnight to be > expressed as 00:00:00 or 24:00:00 to enable you to tell which midnight is > being referred to (ie. The beginning or the end of the day). > > PostgreSQL allows you to make use of that part of the standard, and as admin > tool authors I think we should honour what it allows, provided it's not > blatantly non-standard. It's up to the user to decide whether or not they > actually make use of the facility. For most database applications there is no practical reason to be using a time of 24:00:00(at least none I can think of) and Delphi does not allow a timestamp to contain 24 in the hours position. I have reported it to my database component vendor, maybe they will address it, maybe not. Doing a little research I found that some DBs support it (DB2 for example) and others do not. Since I am targeting mostly windows users with my product, I guess for now I will just allow it to be flagged as invalid. Later, -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com