How to extract year and month from date in PostgreSQL without using to_char() function?

to_char(timestamp, 'YYYY-MM')

You say that the order is not "right", but I cannot see why it is wrong (at least until year 10000 comes around).

date_part(text, timestamp)

e.g.

date_part('month', timestamp '2001-02-16 20:38:40'),
date_part('year', timestamp '2001-02-16 20:38:40')

http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html

Use the date_trunc method to truncate off the day (or whatever else you want, e.g., week, year, day, etc..)

Example of grouping sales from orders by month:

select
  SUM(amount) as sales,
  date_trunc('month', created_at) as date
from orders
group by date
order by date DESC;

Related