TalkPHP - mysql order by date

TalkPHP (http://www.talkphp.com/forums.php)

- General (http://www.talkphp.com/general/)

- - mysql order by date (http://www.talkphp.com/general/4578-mysql-order-date.html)

nefus

06-19-2009 06:49 PM

mysql order by date

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

losing my mind

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!

All times are GMT. The time now is 02:31 PM.