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 - format for date  1978-01   1923-12   select to char of couse work  but not  right  order   to char timestamp column   YYYY-MM

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   gt   2000  limit 10    is working fine   but for   select date part  year  txndt  from  table name  where date part  year  txndt   lt   2000  limit 10    I am getting error

User · Answer

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

User · Answer

You Can use EXTRACT function pgSQL  EX- date   1981-05-31 EXTRACT MONTH FROM date  it will Give 05   For more details  PGSQL Date-Time

User · Answer

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

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

Examples related to sql

Examples related to postgresql