Re: Formatting a month in query output

On Jan 25, 2005, at 11:35 AM, Rodolfo J. Paiz wrote:

> On Mon, 2005-01-24 at 21:08 -0500, Sean Davis wrote:
>> Check out the documentation here:
>>
>> http://www.postgresql.org/docs/8.0/interactive/functions-
>> formatting.html#FUNCTIONS-FORMATTING-TABLE
>>
>> Something like
>> select to_char(date_col,'Month YYYY') from tablename;
>>
>
> Hey, neat... the online manual has a search function. Pity the HTML
> files on local disk don't have that... it would probably save them a
> lot
> of traffic.
>
> I've gotten my query to work, but the output table comes out like this:
>
> flightlog=> select to_char(date, 'Mon YYYY') as month,count(num) as
> flights,sum(hrs_total) as hours from flights group by month;
>   month   | flights | hours
> ----------+---------+-------
>  Dec 2003 |      10 |  16.4
>  Mar 2004 |      10 |  12.2
>  Nov 2004 |      10 |  17.4
>  May 2004 |       5 |   7.3
>  Jan 2005 |       4 |   6.0
>  Jul 2004 |       8 |  10.4
>  Aug 2004 |       8 |  13.0
>  Oct 2003 |       2 |   4.2
>  Oct 2004 |       2 |   2.5
>  Nov 2003 |      10 |  17.7
>  Aug 2003 |       4 |   5.6
>  Sep 2004 |       8 |  10.3
>  Jan 2004 |       9 |  13.3
>  Sep 2003 |       2 |   3.5
>  Jun 2004 |      10 |  16.5
>  Apr 2004 |      11 |  17.4
>  Dec 2004 |       5 |   8.9
>  Feb 2004 |       6 |   9.3
> (18 rows)
>
> I've tried "order by month asc" as well as a few other ideas, but since
> these are now strings they are no longer ordered chronologically but
> alphabetically. Is there a simple way I can order these by date, or am
> I
> going to be stuck with formatting the date as 'YYYY-MM' to get the
> right
> order?

Rodolfo, glad it is working for you.  Did you try order by date,
assuming your column with dates is named date?

Sean