PostgreSql - extract Month + year from date

Question

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.

user330315 · Accepted Answer · 2021-11-10 07:04:04Z

1

Use to_char() for format the timestamp value:

to_char(to_timestamp(e.startts), 'mm/yy') as "Date"

answered Nov 10, 2021 at 7:04

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

sharkyenergy Over a year ago

would it also be possible to add a variation? week of year-year.. for example W30-2021

user330315 Over a year ago

Please see the manual you probably want 'iw-iyyy'

Collectives™ on Stack Overflow

PostgreSql - extract Month + year from date

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related