How to extract year and month from date in PostgreSQL without using to_char() function?
SqlPostgresqlSql Problem Overview
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')
Sql Solutions
Solution 1 - Sql
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 - Sql
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
Solution 3 - Sql
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 4 - Sql
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 5 - Sql
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 6 - Sql
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 7 - Sql
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.