Query a Dataset Applying Filter on Month and Year in Postgresql
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...
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.
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
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.
Marcus Vance is a cybersecurity auditor and technology writer dedicated to educating the public about online safety, data privacy regulations, enterprise security, and emerging cyber threats.
