Query a dataset applying filter on month and year in PostgreSQL

user_12800405 0
Viewed 2283

I have the following dataset

CREATE TABLE my_table 
(
    the_debt_id varchar(6) NOT NULL, 
    the_debt_paid date NOT NULL, 
    the_debt_due date NOT NULL
)

INSERT INTO my_table
VALUES ('LMUS01', '2019-05-03', '2019-05-02'), 
       ('LMUS01', '2019-06-03', '2019-06-02'), 
       ('LMUS01', '2019-07-01', '2019-07-02'), 
       ('LMUS02', '2019-05-03', '2019-05-07'), 
       ('LMUS02', '2019-06-07', '2019-06-07')

And I want to query this dataset by filtering only the rows where the_debt_paid is in June 2019.

The expected result is:

the_debt_id    the_debt_paid     the_debt_due
LMUS01         2019-06-03        2019-06-02
LMUS02         2019-06-07        2019-06-07

I tried the following:

SELECT * FROM my_table 
WHERE EXTRACT(month, the_debt_paid) = 6

But I don't know how apply with the year which is 2019. Any help will be greatly appreciated.

postgresql sql
2 Answers

Try this:

SELECT * FROM my_table 
WHERE EXTRACT('month' from  the_debt_paid) = 6 
and EXTRACT('year' from the_debt_paid)=2019

You can write your query like this also:

SELECT * FROM my_table 
WHERE (EXTRACT(month from  the_debt_paid), EXTRACT('year' from the_debt_paid))=(6,2019)

you can compare month and year by using extract DEMO

user_77724830

Use direct date comparisons!

SELECT *
FROM my_table 
WHERE the_debt_paid >= '2019-06-01' AND
      the_debt_paid < '2019-07-01';

Using functions on dates is bad from multiple perspectives.

  • First, it is less readable. I find that explicit date constants are more easily understood and maintained over time.
  • Second, it prevents the use of indexes on the columns, in almost all cases.
  • Third, it makes the query much harder to optimize, because statistics on the expression are not as accurate as statistics on the column itself.
user_11440350
Related