Re: Question about Postgresql time fields(possible bug)

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Harald Fuchs
> Sent: 10 January 2006 16:53
> To: pgsql-hackers(at)postgresql(dot)org
> Subject: Re: [HACKERS] Question about Postgresql time
> fields(possible bug)
>
> In article <200601101551(dot)k0AFpnK17299(at)candle(dot)pha(dot)pa(dot)us>,
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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.