In a PostgreSQL table I have two columns (int8) containing a Unix timestamp. [Note: this table is not mine, so the columns can be changed to an actual date, hence the conversion in the query.]
below is a stripped down version of my query. What I need to do is to group the entries by month. But in my query below, the entries november 2020 get grouped with the entries of november 2021.
select
DATE_PART('month',to_timestamp(e.startts)) as "Date", sum(e.checked)
from entries e
where
e.startts >= date_part('epoch', '2020-10-01T15:01:50.859Z'::timestamp)::int8
and e.stopts < date_part('epoch', '2021-11-08T15:01:50.859Z'::timestamp)::int8
group by "Date"
How can I have "Date" as month/year instead of month? so for example 10/20 instead of 10.
>Solution :
Use to_char() for format the timestamp value:
to_char(to_timestamp(e.startts), 'mm/yy') as "Date"