Date/time sort not working.

Anon

I have a mysql db, with a timestamp(14) field.

When I do a sort on the db with an ORDER BY date DESC

It sorts it sorta funny..

If there was 4 entries in the DB for today, all with different times, it seems to work fine until the hour changes past noon.

So it would like this:

12:45
12:44
12:30
1:10 <-- I want this one on top.

What am I doing wrong?

tasistro

Remember 1:10 PM is actually 13:10 hrs. You have nothing wrong.

Try this

SELECT if(hour(date)<13,"AM","PM") as noon, if(hour(date)<13,date,subdate(date,interval 12 hour)) as time,ID FROM date_test WHERE 1 order by if(hour(date)<13,date,subdate(date,interval 12 hour)) LIMIT 0, 30

ON a data set like this one
:

phpMyAdmin MySQL-Dump

version 2.2.0

http://phpwizard.net/phpMyAdmin/

http://phpmyadmin.sourceforge.net/ (download page)

Host: localhost

Generation Time: November 21, 2001, 4:18 pm

Server version: 3.23.36

PHP Version: 4.0.4pl1

Database : `phpbuilder`

--------------------------------------------------------

Table structure for table `date_test`

CREATE TABLE date_test (
ID int(11) NOT NULL auto_increment,
DATE datetime NOT NULL default '0000-00-00 00:00:00',
EXTRA char(25) NOT NULL default '',
PRIMARY KEY (ID),
KEY DATE (DATE)
) TYPE=MyISAM;

Dumping data for table `date_test`

INSERT INTO date_test VALUES ( '1', '2001-11-21 12:08:58', '1');
INSERT INTO date_test VALUES ( '2', '2001-11-21 11:09:11', '');
INSERT INTO date_test VALUES ( '3', '2001-11-21 13:09:17', '');
INSERT INTO date_test VALUES ( '4', '2001-11-21 14:09:25', '');
INSERT INTO date_test VALUES ( '5', '2001-11-21 06:09:37', '');

WILL RETURN

PM 2001-11-21 01:09:17 3
PM 2001-11-21 02:09:25 4
AM 2001-11-21 06:09:37 5
AM 2001-11-21 11:09:11 2
AM 2001-11-21 12:08:58 1

Now the PM hours ID 3 a 4 are before the AM hours 5, 2 and 1. AM and PM is added so you don't loose track.

Saludos
Gerardo

[deleted]

Are you sure that this is 1:10 PM, and not 1:10 AM?

and are you ordering by the timestamp column, and not by something you are generating from the timestamp column?

Anon

wow, I'm lost now. =[

Anon

$query = "SELECT author, author_email, news_title, news, unix_timestamp(news_date) as news_date from $newstable ORDER BY news_date DESC";

That is my query if that helps at all..

[deleted]

I think you'll find that midday is at 12:00 :-)

I also think you may want play around with dates and times for a while.
look at this:

SELECT date_format(date, '%p') as noon, date, ID
FROM date_test
ORDER BY date ASC;

MySQL orders perfectly ok with timestamps, it's only the output that gets confusing sometimes.

PS Subtracting data before ordering is evil
You are subtracting 12 from all hours higher than 13, to order by. That means 14-12=2, which is less than 1, so 14:00 comes before 01:00 but 01:00 comes before 14:00.... unless.... 01:00 is in the next day... get the picture? :-)

[deleted]

in that case it is simply not possible that the ordering is wrong.
Are you sure you got the formatting right when you print it?

You didn't accidently get 01:00 when you wanted 13:00?
Try retrieving the full timestamp too, MySQL should format that to a readable string:

$query = "SELECT author, author_email, news_title, news, news_date, unix_timestamp(news_date) as news_unixdate FROM $newstable ORDER BY news_date DESC";
see what news_date contains

Anon

Oh, I think I found the prob..

Your right, they were AM times. I don't understand how that could be though. But the raw times show

20011121013000

2001-11-21 01:30:00

When I manually add a record to the DB, and set the time to say 13:30:00 it sort properly. So my input is someone getting screwed up

Thanks for the help, least I'm on the right track to fixing it! 🙂

tasistro

True should be substracting 12 instead of 13. Sorry bout the copy paste typo. Any way I still don't understand why scott wants to order by hour in a AM/PM style without considering the 24 hour clock.

Scott:

The order by section converts all times over 12 hrs (PM) into AM hours and sorts them after that. Don't know why this would make sense, but it seems to comply with your needs.

At first I didn't think the sql would go. I had never done any math on the order by before. Is there any reason why this is bad besides incredible CPU usage?

Saludos
Gerardo

Anon

Wow, I need to work on my typo's :/

Anyway, it's fixed. You were right on with the am time thing. My form was screwing up the input, so all the times were ending up as AM in the DB. 🙂

Thanks for the help.

[deleted]

You're welcome!

[deleted]

Ordering by the AM-ized times doesn't make sense, and it is not what Scott was trying to do :-)

he just got his date inputs mixed up, he was getting AM times inputted where he needed PM times. So the data in the database was wrong to begin with.