Thread: Formatting a month in query output
Hi! I have a query which needs to provide a monthly report. Thanks to the date_trunc() function someone mentioned in a thread earlier today, I got the query working properly. However, each month is '2004-10-01 00:00:00' and I would like to have "October 2004". Which function, if any, would help me do this formatting trick? And where can I read more about it? Or must I use a PHP case statement in the web page to do this reformatting manually? Thanks, -- Rodolfo J. Paiz <rpaiz@simpaticus.com>
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; might do the trick, if date_col is your column with a timestamp. Sean ----- Original Message ----- From: "Rodolfo J. Paiz" <rpaiz@simpaticus.com> To: <pgsql-novice@postgresql.org> Sent: Monday, January 24, 2005 8:42 PM Subject: [NOVICE] Formatting a month in query output > Hi! > > I have a query which needs to provide a monthly report. Thanks to the > date_trunc() function someone mentioned in a thread earlier today, I got > the query working properly. However, each month is '2004-10-01 00:00:00' > and I would like to have "October 2004". > > Which function, if any, would help me do this formatting trick? And > where can I read more about it? Or must I use a PHP case statement in > the web page to do this reformatting manually? > > Thanks, > > -- > Rodolfo J. Paiz <rpaiz@simpaticus.com> > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings >
Hi Rodolfo, > I have a query which needs to provide a monthly report. Thanks to the > date_trunc() function someone mentioned in a thread earlier today, I got > the query working properly. However, each month is '2004-10-01 00:00:00' > and I would like to have "October 2004". > > Which function, if any, would help me do this formatting trick? And > where can I read more about it? Try to_char(the_date_you_want_to_display, 'MonthYYYY') for that. Note that there actually is no need to use date_trunc here as to_char will only represent only what you ask for anyway. You can find more in the PostgreSQL Online Manual at http://www.postgresql.org/docs/manuals/ (select the one for the version you are using). The chapter is 'Functions and Operators', section 'Data Type Formatting Functions'. HTH, Alex
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
On Tue, 2005-01-25 at 11:39 -0500, Sean Davis wrote: > > 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? > Yup: flightlog=> select to_char(date,'Mon YYYY') as month, count(num) as num, sum(hrs_total) as hours from flights group by month order by date asc; ERROR: column "flights.date" must appear in the GROUP BY clause or be used in an aggregate function If I just run the query, the output is ordered by the way the rows are entered into the database. Using "order by month" orders the *text* labels alphabetically (e.g. Apr Aug Dec Feb...). Using "order by date" gets me an error since the date column is not used in the query. I'm sure PostgreSQL is trying to tell me something, and this is probably an easy one, but it's still got me stumped. Cheers, -- Rodolfo J. Paiz <rpaiz@simpaticus.com>
> flightlog=> select to_char(date,'Mon YYYY') as month, count(num) as num, > sum(hrs_total) as hours from flights group by month order by date asc; > > ERROR: column "flights.date" must appear in the GROUP BY clause or be > used in an aggregate function Try this: select to_char(date,'Mon YYYY') as month, count(num) as num, sum(hrs_total) as hours from flights group by date, month order by date asc; > Using "order by date" > gets me an error since the date column is not used in the query. It is (for sorting). "date" is just not grouped and you can't do this, which is what the above error message is all about: you have group it or use an aggregate function (e.g. count, sum, ...) on it as you do for all other columns in that query. Cheers, Alex
On Thu, 2005-01-27 at 10:36 +1100, Alexander Borkowski wrote: > Try this: > > select to_char(date,'Mon YYYY') as month, count(num) as num, > sum(hrs_total) as hours from flights group by date, month order by date asc; > Sometimes two flights are made on the same day (there and back), so 124 flights were made on a total of 87 unique dates. This query, which I assume does a primary group by date and a secondary group by month, provides 87 rows which have the month column formatted correctly but correspond to the 87 unique dates available. There are, of course, multiple rows for each month. I also attempted this: flightlog=> select to_char(date, 'Mon YYYY') as month, count(date) as nnn, sum(hrs_total) as hours from flights group by month order by date asc; ERROR: column "flights.date" must appear in the GROUP BY clause or be used in an aggregate function Notice "count(date) as nnn", so that now I *am* using it in an aggregate function. I tried "order by date" and also "order by "nnn". No joy. But I don't understand why... --- By now I've figured out that I could just do to_char(date, 'YYYY-MM') and then use a case statement in my PHP code to reformat the date, but that's no fun. Now I want to figure out just what the hell it wants me to type so I get the results I want. Call me stubborn... -- Rodolfo J. Paiz <rpaiz@simpaticus.com>
Rodolfo J. Paiz wrote:
> Sometimes two flights are made on the same day (there and back), so 124
> flights were made on a total of 87 unique dates. This query, which I
> assume does a primary group by date and a secondary group by month,
> provides 87 rows which have the month column formatted correctly but
> correspond to the 87 unique dates available. There are, of course,
> multiple rows for each month.
Sorry, I totally missed the crucial point there. How about
select date_trunc('month', date) as sort_month, to_char(date,'Mon YYYY')
as month, count(num) as num, sum(hrs_total) as hours from flights group
by sort_month, month order by sort_month asc;
> I also attempted this:
>
> flightlog=> select to_char(date, 'Mon YYYY') as month, count(date) as
> nnn, sum(hrs_total) as hours from flights group by month order by date
> asc;
> ERROR: column "flights.date" must appear in the GROUP BY clause or be
> used in an aggregate function
>
> Notice "count(date) as nnn", so that now I *am* using it in an aggregate
> function. I tried "order by date" and also "order by "nnn". No joy. But
> I don't understand why...
Yes, you are using it in an aggregate function but not referring to the
result in the "order by"-clause when using "order by date". I don't know
why "order by nnn" would fail though (apart from not doing what you
want). I realise now that my comment about the aggregate function
probably does not make too much sense in this context, I just tried to
explain what the error message was all about.
Cheers,
Alex
On Thu, 2005-01-27 at 12:31 +1100, Alexander Borkowski wrote:
> Sorry, I totally missed the crucial point there. How about
>
> select date_trunc('month', date) as sort_month, to_char(date,'Mon YYYY')
> as month, count(num) as num, sum(hrs_total) as hours from flights group
> by sort_month, month order by sort_month asc;
>
flightlog=> select date_trunc('month', date) as sort_month, to_char
(date, 'Mon YYYY') as month, count(num) as num, sum(hrs_total) as hours
from flights group by sort_month, month order by sort_month asc;
sort_month | month | num | hours
---------------------+----------+-----+-------
2003-08-01 00:00:00 | Aug 2003 | 4 | 5.6
2003-09-01 00:00:00 | Sep 2003 | 2 | 3.5
2003-10-01 00:00:00 | Oct 2003 | 2 | 4.2
2003-11-01 00:00:00 | Nov 2003 | 10 | 17.7
2003-12-01 00:00:00 | Dec 2003 | 10 | 16.4
2004-01-01 00:00:00 | Jan 2004 | 9 | 13.3
2004-02-01 00:00:00 | Feb 2004 | 6 | 9.3
2004-03-01 00:00:00 | Mar 2004 | 10 | 12.2
2004-04-01 00:00:00 | Apr 2004 | 11 | 17.4
2004-05-01 00:00:00 | May 2004 | 5 | 7.3
2004-06-01 00:00:00 | Jun 2004 | 10 | 16.5
2004-07-01 00:00:00 | Jul 2004 | 8 | 10.4
2004-08-01 00:00:00 | Aug 2004 | 8 | 13.0
2004-09-01 00:00:00 | Sep 2004 | 8 | 10.3
2004-10-01 00:00:00 | Oct 2004 | 2 | 2.5
2004-11-01 00:00:00 | Nov 2004 | 10 | 17.4
2004-12-01 00:00:00 | Dec 2004 | 5 | 8.9
2005-01-01 00:00:00 | Jan 2005 | 4 | 6.0
(18 rows)
This would be perfect if we could get rid of that first column and only
display the rest. Is that possible?
Thanks for all the help.
--
Rodolfo J. Paiz <rpaiz@simpaticus.com>
Rodolfo J. Paiz wrote:
> On Thu, 2005-01-27 at 12:31 +1100, Alexander Borkowski wrote:
>
>>Sorry, I totally missed the crucial point there. How about
>>
>>select date_trunc('month', date) as sort_month, to_char(date,'Mon YYYY')
>>as month, count(num) as num, sum(hrs_total) as hours from flights group
>>by sort_month, month order by sort_month asc;
[...]
> This would be perfect if we could get rid of that first column and only
> display the rest. Is that possible?
Apart from just not displaying it on the client side you can do a
subselect. For example:
select to_char(monthly_flights.sort_month, 'Mon YYYY') as month,
monthly_flights.num, monthly_flights.hours
from (
select date_trunc('month', date) as sort_month, count(num) as num,
sum(hrs_total) as hours
from flights group by sort_month
) as monthly_flights
order by monthly_flights.sort_month asc;
Cheers,
Alex