ChrisHagg
asked on
Selecting all bills from x months ago in Postgres?
Hi, i've got a small problem with an SQL query. I could do it all in PHP, but I guess it's nicer when done in SQL. Say I got the following table:
Table BILLS
* bill_id (INTEGER, autonummering)
* location_id (INTEGER)
* billdate (POSTGRES TIMESTAMP WITHOUT TIMEZONE)
Now I want to know, if it is a year or more ago when I sent the last bill (because after a year I have to send a new one). So I guessed I got to have the latest bill of a specifice location (bills go for each location each year):
SELECT locationid, max(billdate) AS billdate FROM bills WHERE GROUP BY locationid;
This works ok, I get the latest billdate from each location. But now I have to compair this billdate with the current date, and only select the tuple when the difference between these two dates is 12 months or bigger. Now I found something like this:
SELECT ((EXTRACT(DOY FROM TIMESTAMP billdate))::integer - (EXTRACT (DOY FROM TIMESTAMP '2005-02-01 00:00:00'))::interget as doy_difference FROM bills;
To calculate the days between the different dates (not exactly what I want (is 365 a year in 2004?)), but I guess it's a way to get a bit closer to the sollution. Can anybody help me with the question "How do I select all locationids where the latest billdate is a year or more ago compairt to the current date?".
If more information is needed, please ask.
Thnx!
Table BILLS
* bill_id (INTEGER, autonummering)
* location_id (INTEGER)
* billdate (POSTGRES TIMESTAMP WITHOUT TIMEZONE)
Now I want to know, if it is a year or more ago when I sent the last bill (because after a year I have to send a new one). So I guessed I got to have the latest bill of a specifice location (bills go for each location each year):
SELECT locationid, max(billdate) AS billdate FROM bills WHERE GROUP BY locationid;
This works ok, I get the latest billdate from each location. But now I have to compair this billdate with the current date, and only select the tuple when the difference between these two dates is 12 months or bigger. Now I found something like this:
SELECT ((EXTRACT(DOY FROM TIMESTAMP billdate))::integer - (EXTRACT (DOY FROM TIMESTAMP '2005-02-01 00:00:00'))::interget as doy_difference FROM bills;
To calculate the days between the different dates (not exactly what I want (is 365 a year in 2004?)), but I guess it's a way to get a bit closer to the sollution. Can anybody help me with the question "How do I select all locationids where the latest billdate is a year or more ago compairt to the current date?".
If more information is needed, please ask.
Thnx!
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
( SELECT location_id, max(billdate) AS billdate FROM bills GROUP BY location_id ) as most_recent_bills
where
billdate < now() - interval '1 year';