Thread: BUG #3252: Select Order by time

The following bug has been logged online:

Bug reference:      3252
Logged by:          Lee Chua
Email address:      leehchua@bensecurity.com.au
PostgreSQL version: 8.2.3
Operating system:   FC4
Description:        Select Order by time
Details:

When we select and order by time we get 00:00:00 as the latest time of the
day.

The latest time of the day should be 23:59:59 not 00:00:00. 00:00:00 should
be the earliest time of the day. This follows naval/military convention
somewhat although the time 00:00:00 actually does not exist in the
military/naval definition and in fact is called Midnight. The only
convention then that we should follow is the computer where we start
counting from 0; so 00:00:00 should be the epoch of the day. It also makes
numerical order sense.

"Lee Chua" <leehchua@bensecurity.com.au> writes:
> When we select and order by time we get 00:00:00 as the latest time of the
> day.

Really?  It works as expected for me:

regression=# create table foo(f1 time);
CREATE TABLE
regression=# insert into foo values ('1:00:00'),('2:00:00'),('0:00:00'),
regression-#  ('23:00:00'), ('23:59:59');
INSERT 0 5
regression=# select * from foo order by f1;
    f1
----------
 00:00:00
 01:00:00
 02:00:00
 23:00:00
 23:59:59
(5 rows)

regression=

Please provide a reproducible test case.

            regards, tom lane

>>> On Wed, Apr 25, 2007 at 12:42 AM, in message <14184.1177479755@sss.pgh.=
pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> wrote:=20
> "Lee Chua" <leehchua@bensecurity.com.au> writes:
>> When we select and order by time we get 00:00:00 as the latest time of t=
he
>> day.
>=20
> Really?  It works as expected for me:
>=20
> regression=3D# create table foo(f1 time);
> CREATE TABLE
> regression=3D# insert into foo values ('1:00:00'),('2:00:00'),('0:00:00'),
> regression- #  ('23:00:00'), ('23:59:59');
> INSERT 0 5
> regression=3D# select * from foo order by f1;
>     f1=20=20=20=20
> ----------
>  00:00:00
>  01:00:00
>  02:00:00
>  23:00:00
>  23:59:59
> (5 rows)
=20
I just wanted to point out that midnight is supported at both ends -- the s=
tart of the day as 00:00:00, and the end of the day as 24:00:00.  Perhaps t=
he application software is not distinguishing these?
=20
Modifying Tom's example to insert one more row, you will see:
=20
    f1
----------
 00:00:00
 01:00:00
 02:00:00
 23:00:00
 23:25:59
 24:00:00
(6 rows)
=20
I know there are some who require this behavior.  (I had to add it to a dat=
abase product years ago when it was used to develop an application for fire=
 departments.)
=20
-Kevin
=20

Hi Kevin,



Thanks for the response. I am a little embarrassed and I was in fact hoping
that my stupid Report would just dissolve away in the abyss. In your
responding I am of course quite bowled over that - Hey there are people
genuinely out there - unlike reports one may send to mickeysoft who are
probably paid handsomely.



You are of course right. I do not know what I was seeing - might have been
something to do with it being about 3:00am my time.



Interesting that 24:00 is accepted by Postgres. Didn't know that.



Again, Thank you for the effort you have expended to look into my (really
stupid) bug report.



Regards





Lee



Lee Chua





-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: Thursday, 26 April 2007 6:15 AM
To: Lee Chua; Tom Lane
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #3252: Select Order by time



>>> On Wed, Apr 25, 2007 at 12:42 AM, in message
<14184.1177479755@sss.pgh.pa.us>,

Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "Lee Chua" <leehchua@bensecurity.com.au> writes:

>> When we select and order by time we get 00:00:00 as the latest time of
the

>> day.

>

> Really?  It works as expected for me:

>

> regression=# create table foo(f1 time);

> CREATE TABLE

> regression=# insert into foo values ('1:00:00'),('2:00:00'),('0:00:00'),

> regression- #  ('23:00:00'), ('23:59:59');

> INSERT 0 5

> regression=# select * from foo order by f1;

>     f1

> ----------

>  00:00:00

>  01:00:00

>  02:00:00

>  23:00:00

>  23:59:59

> (5 rows)



I just wanted to point out that midnight is supported at both ends -- the
start of the day as 00:00:00, and the end of the day as 24:00:00.  Perhaps
the application software is not distinguishing these?



Modifying Tom's example to insert one more row, you will see:



    f1

----------

 00:00:00

 01:00:00

 02:00:00

 23:00:00

 23:25:59

 24:00:00

(6 rows)



I know there are some who require this behavior.  (I had to add it to a
database product years ago when it was used to develop an application for
fire departments.)



-Kevin