SQL arrange by date

[t0mmy9]

sorry, i know this is more of an sql problem and not php, but ive made my own forum and i want the forum to display the newest posts first, but the dates are stored like this:

 

Thu, 28 Feb 2008 09:48:32 -0500

 

is there a way to do this or will i have to change the date/time format in php?

thanks for any help.

[fnairb]

Your best bet for the type of sorting you want is to use a date/time field.  However, I'm guessing you have reasons for the field being how it is.  If changing the fields type will cause more trouble than it is worth create another field in your table that has the same value but is in a storable format.

 

Note that creating a second field with the same value in the same table is in essence a bad design.  If you can afford the time and effort to change the table, do it.  If you can't you can just chalk it up to experience and next time just use a date/time field to start off.

 

[t0mmy9]

im not sure what you mean, i set the type in the sql db as date/time and the values changed to 00:00:00 00-00  or something like that so i had to make it a varchar. There seems to be no way to fix this

[fnairb]

Where are you performing your sort.  I made the (false) assumption that you were trying to sort the entries while issuing the query. 

 

If you are performing your sort in the query where the field is a date/time and ORDER BY is clause using the unformatted date/time value then it should sort as expected. 

 

If you are performing the sort using PHP they you are out of luck using that date format.

 

I took a quick look at the MySQL manual Date Time functions page and there is a snippet near the bottom that might help you out.  Just search for:

Posted by Shamun toha on December 18 2004 10:45am

 

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

 

 

[t0mmy9]

sorry, i guess i didnt explain myself properly

 

yes, i am trying to sort them in order using the sql query but the sql doesnt seem to recognise them as date/time format.

 

i used the php

date('r');

 

to add the dates to the database so i dont understand why sql wont recognise them as date/time

[fnairb]

Maybe we are going about this the wrong way.  Instead of talking about the problem maybe we should hit it head on.  Could you post the query you are executing?

[PFMaBiSmAd]

Here is a slightly different slant on the problem.

 

In order for a date/time to be sortable, it must be in a format that can be sorted. This means that the information in the date is arranged from most significant digit to least significant digit, ie. yyyy-mm-dd.

 

The date('r') format is not sortable, because none of the fields in it are in a most significant to least significant order, the alpha field for the day is meaningless in a sort, and sorting the month name does not produce chronologically ordered months.

 

The mysql DATE and DATETIME formats are sortable, which is actually the reason why they are defined the way they are (comparisons and sorts work correctly.)

 

To do what you want you need a DATE or DATETIME column in your database. You cannot just change an existing column definition. You must add a new column, covert and copy the values from your existing column, change your queries to use the new column and its format (you can use the mysql DATE_FORMAT() function in a query to give you any output format you want), then once all the changes are made and tested remove your existing column.

[t0mmy9]

luckily my forum is still fairly small at the moment,

 

for the datetime format, i tried date("o-m-d h:i:s")

 

which for example, produced: 2008-03-09 05:35:57

 

in the database, the format when you change it to datetime is 0000-00-00 00:00:00 yet when i tried inserting that date, it didnt work, have i done something wrong?

[PFMaBiSmAd]

If you are inserting the current date and time, just use the mysql now() function in the query. No slow php code is necessary to produce the date and time.

 

You would need to show your php code with your query string and your mysql_query statement for anyone in a forum to be able to help you with why it is not working.