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