What is the proper way to sort by time?

When each item is e开发者_如何学运维ntered into the db i use the following method to insert date:

 date_default_timezone_set('America/New_York');
 ob_start();
 $createdon = date('l, F j, Y, h:i A');
 ob_get_clean();

This outputs something like:

  Tuesday, May 3, 2011, 04:50 PM

This is the query:

  select test_id, why_test, date_time
  from the_test
  where p_id = '$id'
  UNION
  select pencil_id, pencil_name, date_time
  from pencils
  where p_id = '$id'
  ORDER BY 3 DESC  

Now when i user the order by function it doesn't do it properly. The dates are still being mixed up. I used order by date_time DESC.

I am using php/phpmyadmin.

Any ideas?

Storing a date as a string is wrong. You should store a date as a date.

MySQL has a selection of appropriate types relating to dates and times; I'd recommend either TIMESTAMP or DATETIME (probably the latter; look it up in the manual to see what's best for you).

To avoid losing your existing data when you fix your table structure:

• Add a field with the right type;
• Update each row in your table to copy the data to the new field, possibly with the UNIX_TIMESTAMP function — this is a one-time copy, which might take some time;
• Delete the old field and starting using the new one instead.

ORDER BY will work naturally on this field, as you will now be using MySQL as it is designed to be used.

try

SELECT .... ORDER BY UNIX_TIMESTAMP(`date_time`) DESC

The idea here, is to turn a string into a number that can be sorted. A unix timestamp, if you are not familiar with it, is the number of seconds since 1/1/1970, so if you can convert the time into that, you get a linear, easy way to compare time.

but I believe MySQL's UNIX_TIMESTAMP() requires a string in the format: 1970-01-01 00:00:00

But the concept remains, into turning the date (or even storing it) as a unix timestamp or MySQL's native date types like datetime or timestamp