ORDER BY on a datetime field

dmeade

I have a "Log" database which has a datetime field. I'm trying to display the logs in chronological order using ORDER BY thedate

But it isn't really working. The values seem to be stored in YYYY-MM-DD hh:mm:ss format and when the query ORER BY on this it isnt done correctly.

Is there a way via either the SQL query itself or in PHP to accurately sory on a datetime field?

Thanks in advance,
Dave

chriskl

Sure it's actually a datetime field and not a varchar or something?

Chris

dmeade

It's datetime alright.

it's storing datetime values in the format:
0000-00-00 00:00:00

This is the code I'm using:

$logquery = "SELECT message, UNIX_TIMESTAMP(entry_date) as thedatetime, lognumber, link_from_root FROM Logs ORDER BY `thedatetime` DESC limit 7";

$result = mysql_query($logquery) or die("<BR><B>Could not get logs</B>");

while ($a_log = mysql_fetch_row($result)) {
  print  date("l m/d", $a_log[1])  . " - $a_log[0]";
}

However is is not correctly sorting. What I want to happen is for the newest entries to be listed on top and the older ones listed below in reverse chronological order.

However with the following 6 datetimes from the 6 records in the database:

2002-09-17 01:09:51
2002-09-17 01:10:18
2002-09-17 01:09:28
2002-09-17 11:09:59
2002-09-17 01:09:15
2002-09-17 01:09:37

The resulted sort appears in this order:

2002-09-17 11:09:59
2002-09-17 01:10:18
2002-09-17 01:09:51
2002-09-17 01:09:37
2002-09-17 01:09:28
2002-09-17 01:09:15

when I'm trying, of course, to get them in this order:

2002-09-17 01:10:18
2002-09-17 01:09:51
2002-09-17 01:09:37
2002-09-17 01:09:28
2002-09-17 01:09:15
2002-09-17 11:09:59

Dolemite50

I have the same problem, anybody run into this?

Sxooter

Is that military time (i.e. 00 to 23 hours) or is it AM/PM? If am/pm is that part of the time causing the problem?

wazo00

The resulted sort appears in this order:

2002-09-17 11:09:59
2002-09-17 01:10:18
2002-09-17 01:09:51
2002-09-17 01:09:37
2002-09-17 01:09:28
2002-09-17 01:09:15

This is the order you want. The newest is on top. 11AM>1AM

dmeade

Originally posted by wazo00
This is the order you want. The newest is on top. 11AM>1AM

well no ... take a look at the whole date time ...

entry 1
entry 2 - is later than entry 1
entry 3 - is earlier than entry 2

.... clearly this is not being sorted on the date time in any order which is logical to a date time.

nemonoman

Why are you overkilling this sort? Just sort by the raw data:

try this:

$logquery = "SELECT message, UNIX_TIMESTAMP(entry_date) as thedatetime, lognumber, link_from_root FROM Logs ORDER BY entry_date DESC limit 7";

sort by entry_date, not the unix timestamp.