0

I want my query to show all months grouped together, regardless of the year. For example, I would want this to be the output:

01/13/2015
01/01/2016
02/07/2015
02/01/2016

This is sample DDL - what must I alter so months are all displayed together regardless of year?

CREATE TABLE randomdate(dateofwork timestamp);

INSERT INTO randomdate VALUES('2016-01-01 00:00:00-05');
INSERT INTO randomdate VALUES('2015-01-13 00:00:00-05');
INSERT INTO randomdate VALUES('2016-02-01 00:00:00-05');
INSERT INTO randomdate VALUES('2015-02-07 00:00:00-05');


Select 
date_trunc('month',randomdate)
from randomdate
GROUP BY date_trunc('month', randomdate)
ORDER BY date_trunc('month', randomdate) ASC
| improve this question | |
1

date_trunc truncates the date to the specified part. You need extract

select extract('month' from dateofwork) from randomdate group by 1 order by 1;

+-------------+
|   date_part |
|-------------|
|           1 |
|           2 |
+-------------+
SELECT 2
Time: 0.005s
| improve this answer | |
  • Is it possible to return the full date as well? This returns only a numeric value for the month. – user2676140 May 9 '16 at 15:16
  • @user2676140 - the short answer is no, you can't do this - by performing the aggregation of GROUP BY, you "lost" information. As a comparison, consider an average - that provides summary information but the "price" you pay is losing your individual data points. – Vérace May 9 '16 at 15:31
  • 1
    His example desired output indicates he doesn't actually want the group by anyway. All he has to do is remove it (and keep just the order by). Then he could just add dateofwork into the select list. – jjanes May 9 '16 at 18:26
  • 1
    The goal of group by extract is to 'combine' different values by decreasing granularity and doing some aggregation. So having the 'full date' in the group by column is breaking the functionality as others pointed out before me. If you still need the original date you can use something like array_agg to return the list of (full) dates together with the other columns you're interested in: select extract('month' from dateofwork), array_agg(dateofwork) from randomdate group by 1 order by 1; – Károly Nagy May 10 '16 at 7:54
1

this query will also have valid result:

Select date_part('month',dateofwork)
from randomdate
GROUP BY date_part('month', dateofwork)
ORDER BY date_part('month', dateofwork) ASC
| improve this answer | |
  • Is it possible, to still return the full date as well? – user2676140 May 9 '16 at 15:16
  • you cannot do it with group by expression. maybe with windowing functions but i need to check it first. – Mladen Uzelac May 9 '16 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.