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

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

to_char(timestamp_column, 'YYYY-MM')
sqlpostgresql


Solution 1:[1]

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).

Solution 2:[2]

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;

Solution 3:[3]

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

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

Example:

Input: 
created_at = '2019-12-16 18:28:13'

Output 1:

date_trunc('day',created_at)
// 2019-12-16 00:00:00

Output 2:

date_trunc('day',created_at)::date 
// 2019-12-16

Output 3:

date_trunc('month',created_at)::date 
// 2019-12-01

Output 4:

date_trunc('year',created_at)::date 
// 2019-01-01

Solution 4:[4]

1st Option

date_trunc('month', timestamp_column)::date

It will maintain the date format with all months starting at day one.

Example:

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

2nd Option

to_char(timestamp_column, 'YYYY-MM')

This solution proposed by @yairchu worked fine in my case. I really wanted to discard 'day' info.

Solution 5:[5]

You Can use EXTRACT function pgSQL

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

For more details PGSQL Date-Time

Solution 6:[6]

It is working for "greater than" functions not for less than.

For example:

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

is working fine.

but for 

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

I am getting error.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 yairchu
Solution 2 Gerry Shaw
Solution 3 Eddy
Solution 4 Luis Martins
Solution 5 kometen
Solution 6

Related Questions