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

Question

I want to select sql: SELECT "year-month" from table group by "year-month" AND order by date , where year-month is the date format "1978-01", "1923-12". select to_char from couse work , but not the "correct" order:

 to_char(timestamp_column, 'YYYY-MM') 

Answer 1

 date_part(text, timestamp) 

eg

 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

Answer 2

 to_char(timestamp, 'YYYY-MM') 

You say that the order is not “correct”, but I don’t understand why it is wrong (at least until 10,000).

Answer 3

Use the date_trunc method to truncate the day (or what you want, for example, 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; 

Answer 4

You can crop all the information after a month using date_trunc(text, timestamp) :

 select date_trunc('month',created_at)::date as date from orders order by date DESC; 

Answer 5

1st option

 date_trunc('month', timestamp_column)::date 

It will support date format with all months starting from the first day.

Example:

 2016-08-01 2016-09-01 2016-10-01 2016-11-01 2016-12-01 2017-01-01 

Second option

 to_char(timestamp_column, 'YYYY-MM') 

This solution suggested by @yairchu worked fine in my case. I really wanted to give up information about the "day."

Answer 6

You can use the EXTRACT pgSQL function

 EX- date = 1981-05-31 EXTRACT(MONTH FROM date) it will Give 05 

For more information PGSQL Date-Time

Answer 7

It works for more than functions, not less.

For example:

 select date_part('year',txndt) from "table_name" where date_part('year',txndt) > '2000' limit 10; 

works fine.

but for

 select date_part('year',txndt) from "table_name" where date_part('year',txndt) < '2000' limit 10; 

I get an error.