mysql order by date

nefus · 06-19-2009, 06:49 PM

So I'm wondering about order by date but I guess this probably covers most order by questions. If you do something like

Code:

 Select * from events ORDER BY date_field

Is it best to use yyyy/mm/dd or can you use dd/mm/yyyy. I assume it'll go left to right in the field and the year/month/day is the best way to sort?

Village Idiot · 06-19-2009, 07:51 PM

If the datatype is a date type, it is not stored as a string but as a machine readable format. How it is output is decided after the sorting is done.

nefus · 06-23-2009, 04:40 PM

Ok, I have no idea what you mean.

Wildhoney · 06-23-2009, 04:51 PM

You can't change the format in which MySQL stores the date. However, you can always modify the format when you retrieve it from your table. MySQL will sort it in chronological order.

nefus · 06-23-2009, 05:20 PM

Ok but my original question still seems to be about left to right or right to left in the field being ORDER BY.

Wildhoney · 06-23-2009, 05:31 PM

But I don't think you can change it from YYYY/MM/DD, can you?

nefus · 06-23-2009, 05:33 PM

I guess if you have inserted the data from a php statement, that was my intention. I'm sort of new to this date sorting thing and am not sure the best way to store dates really.

nefus · 06-23-2009, 05:40 PM

I better explain this better. I am using a javascript date picker that puts dates into a form. It uses these formats 06/23/2009 or 04.06.2009. Suggestions?

Wildhoney · 06-23-2009, 08:31 PM

I'd personally store the dates as a timestamp.

nefus · 06-23-2009, 08:46 PM

Yes, that does make more sense. Thanks for the suggestion, I'll change my feeble code

nefus · 06-23-2009, 09:06 PM

I can't see where I am screwing this up...

Code:

echo "06/27/2009 converts to: ". strtotime("06/27/2009"), "\n";

$send_date	=	date('m/d/Y');

$send_date2	=	strtotime($send_date);

$send_date3	=	date("m/d/Y", strtotime($send_date2));

Returns this:

Code:

06/27/2009 converts to: 1246078800
send1: 06/23/2009
send2: 1245733200
send3: 12/31/1969

Where am I messing this up?? Why is strtotime not converting correctly?

Wildhoney · 06-23-2009, 09:55 PM

It is because you're using strtotime again on an existing timestamp.

php Code:

$send_date  = date('m/d/Y');
printf('Date now: %s<br />', $send_date);

$send_date2 =    strtotime($send_date);
printf('As a timestamp: %s<br />', $send_date2);

$send_date3 =    date("m/d/Y", ($send_date2));
printf('And back again: %s', $send_date3);

nefus · 06-23-2009, 10:02 PM

I feel sheepish. Thanks Wildhoney!