Question about Postgresql time fields(possible bug)

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, Pennsylvania 19073

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

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

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

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

Tom Lane said:

Andrew Dunstan <andrew@dunslane.net> writes:

Jim C. Nasby wrote:

Won't this result in a call to pg_sleep with a long sleep time ending
up sleeping noticeably longer than requested?

Looks like it to me.

Something on the order of 1% longer, hm? (1 extra clock tick per
second, probably.) Can't get excited about it --- *all*
implementations of sleep say that the time is minimum not exact.

Well yes, although it's cumulative. I guess I'm not excited for a different
reason - I'm having trouble imagining much of a use case.

cheers

andrew