CODEWITHSUNDEEP
sqlpostgresql
Harry Wardana
Harry Wardana

Reputation: 277

Postgres - how to displays data for the previous year?

I have sql

select
    name,
    tanggal,
    status
from
    tbl_person
where
    status = 'PROSES'
    and date_part('year', tanggal) = 2021 - INTERVAL '1 YEAR'

tanggal is date 2021-01-01

I want to display previous year's data for example in 2020, how to write the correct query?

Upvotes: 1

Views: 100

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Using your method:

where status = 'PROSES' and
      date_trunc('year', tanggal) = date_trunc('year', current_date) - interval '1 year'

However, I prefer to avoid functions on the column -- so the query is easier to optimize. So I would recommend:

where status = 'PROSES' and
      tanggal < date_trunc('year', now()) and
      tanggal >= date_trunc('year', now()) - interval '1 year'

This just uses now() rather than current_date because it is easier to type.

Upvotes: 1

Related Questions