group by to_char in postgres

by Ivan Alex   Last Updated May 15, 2018 19:06 PM

I have table with three columns (id int, intdate int, txt text). And have to group by txt content by day.

I can only query like

select 
  date(to_timestamp(intdate)) as data, 
  count(txt) 
from tbl 
group by data 
order by data desc 
limit 30;

So i get result for example:

23-04-2018, 40

But i want get for example:

23.04.2018, 04.2018, 156789012, 40

For this i tried query, but did not get it working

select 
  to_char(to_timestamp(intdate), 'DD.MM.YYYY') as date, 
  to_char(to_timestamp(intdate), 'MM.YYYY') as month, 
  intdate, count(txt) 
from tbl 
group by date desc 
order by date desc
limit 30

I only can query

select 
  to_char(to_timestamp(intdate), 'DD.MM.YYYY') , 
  count(txt) 
from tbl 
group by date desc 
order by date desc 
limit 30

But it orders by char and not working as i want

Any help please?



Related Questions


WHERE clause converting INT to DATETIME

Updated December 15, 2017 15:06 PM

PostgreSQL datestyle / OS locale issue

Updated April 20, 2018 15:06 PM

How do I drop the TIME portion from DATEADD?

Updated August 10, 2015 19:02 PM

Understanding datetime formats in SQL Server

Updated March 20, 2016 08:02 AM