MySQL order by date (M D, Y, H:M:S am\pm)

I've been messing around with this for hours trying different ways of ordering by date. I wish there was an strtotime(field) that would do what I need.

I've tried messing around with UNIX_TIMESTAMP, STR_TO_DATE and I just can't figure this out.

I'm trying to put these in order given below:

June 13, 2012, 4:45:28 pm
May  31, 2012, 10:41   am
June 12, 2012, 6:40:34 pm
June 06, 2012, 3:05:22 am
June 10, 2012, 7:59:31 pm

I've tried a bunch of different approaches, this is the latest one I've been working on...

SELECT name,lastonline FROM `users` ORDER BY STR_TO_DATE(UPPER(lastonline),
       '%M %d,%Y, %H:%i:%s') DESC

It seems as though this is as close as I can get and it doesn't take into account "AM" or "PM". I've tried adding the %p on the end, but for some reason it's as though the UPPER isn't doing anything at all. I've also tried using %r.

Any ideas?

Answers


The MySQL docs say, that a construct of 12-Hour time and AM/PM should be parsed as %r in one piece, not as %H:%i:%s %p.

While I consider this to be a misfeature, it definitly works.


you may use HH24 instead to get a full range of hours.


Need Your Help

Is there any other strongly-integrated presentation layer tool other than JSF/JSP for Java EE?

java java-ee presentation-layer

I'm teaching Java EE at the university, and this was a question a student asked. I said "no", but I wasn't really sure, so I thought I might ask you mighty developers. :)