Date formatting/sorting

[Jim Babcock]

Hello,

I'm querying a bunch of timestamped rows with various counts. I use date_format on the tstamp to put it in '%M %Y', ex: Apr 2005, group by that date and sum all the columns.

Here is the selct statement:

String sqlGSM = "select date_format(tstamp,'%M %Y') num,sum(received_messages), sum(transmitted_messages), sum(received_messages+transmitted_messages) from ytsmsc_message_counts where node_name like 'GIW%' group by num";

and it puts out:

April 2005 43995937 25221318 44308986 26170410 88304923 51391728
February 2005 225831430 48281013 226892608 58628583 452724038 106909596
March 2005 261285035 111214654 262415276 115385181 523700311 226599835

I'm happy with the output, I use this in a JSP page, format it in a table and add commas... its a beautiful thing. Except, if you look at the output, it is doing an alphabetic sort on the date, even though I'm not specifying one.

I thought I could something sneaky like using str_to_date to convert 'April 2005' to '2005-04' and sort on that, but apparently that is not available on my version of mysql (mysql Ver 12.22 Distrib 4.0.20, for sun-solaris2.8 (sparc)). When I try 'SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');' I get:

ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '('04/31/2004', '%m/%d/%Y')' at line 1

Any suggestions? I'd really like to keep the date presented in the '%M %Y' format. This is an exec level report and the prettier the better. While I was writing this I thought to try a numeric formatted date as the second field in the select and the grouping on both fields, then ordering on the second... I'll let you know :).

Thanks,

Jimbus