Thread: Question about Postgresql time fields(possible bug)

Question about Postgresql time fields(possible bug)

From
Tony Caduto
Date:
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


Re: Question about Postgresql time fields(possible bug)

From
Bruce Momjian
Date:
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
 


Re: Question about Postgresql time fields(possible bug)

From
"Dave Page"
Date:

> -----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


Re: Question about Postgresql time fields(possible bug)

From
Harald Fuchs
Date:
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'?



Re: Question about Postgresql time fields(possible bug)

From
Tom Lane
Date:
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


Re: Question about Postgresql time fields(possible bug)

From
"Dave Page"
Date:

> -----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.


Re: Question about Postgresql time fields(possible bug)

From
Harald Fuchs
Date:
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?



Re: Question about Postgresql time fields(possible bug)

From
Dave Page
Date:


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. 



Re: Question about Postgresql time fields(possible bug)

From
Josh Berkus
Date:
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


Re: Question about Postgresql time fields(possible bug)

From
Tom Lane
Date:
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


Re: Question about Postgresql time fields(possible bug)

From
Tony Caduto
Date:
> 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