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

2 Answers 2

Reset to default
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
4
  • Is it possible to return the full date as well? This returns only a numeric value for the month.
    – user2676140
    May 9, 2016 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, 2016 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, 2016 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, 2016 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
2
  • Is it possible, to still return the full date as well?
    – user2676140
    May 9, 2016 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, 2016 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.