CODEWITHSUNDEEP
sqlpostgresql
Bdfy
Bdfy

Reputation: 24621

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

Upvotes: 226

Views: 525329

Answers (6)

Singhak
Singhak

Reputation: 8896

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

Upvotes: 24

Aya
Aya

Reputation: 571

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

Upvotes: 57

MK.
MK.

Reputation: 34537

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

Upvotes: 133

Luis Martins
Luis Martins

Reputation: 1632

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.

Upvotes: 29

Gerry Shaw
Gerry Shaw

Reputation: 9378

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;

Upvotes: 63

yairchu
yairchu

Reputation: 24764

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

Upvotes: 366

Related Questions