Re: Sorting distinct dates by year and month respectively - Mailing list pgsql-novice

> gsin=> SELECT DISTINCT date_part('year', article_pubdate),
> date_part('month', article_pubdate) FROM articles GROUP BY
> date_part('year', article_pubdate), date_part('month',
> article_pubdate) ORDER BY date_part('year', article_pubdate),
> date_part('month', article_pubdate) DESC;
>  date_part | date_part
> -----------+-----------
>       2002 |         5
> Can anyone help me figure out why this is so?


I believe that date_trunc will give you what you want.

mydb=> select date_trunc('month', tstamp) as month from process group by month order by month
limit 4;
        month
---------------------
 2005-10-01 00:00:00
 2006-01-01 00:00:00
 2006-02-01 00:00:00
 2006-04-01 00:00:00
(4 rows)

Regards,

Richard Broersma Jr.