I am migrating an application over from mysql to postgresql and am a little
confused on how to write the following query in PostgreSQL. The date
functions and syntax is a world apart from MySQL and what I am used to. To
sum it up, I want to select a count of all records in a table that have been
added in the last 90 days. My current table has a field called 'created'
which is a timestamp. In MySQL the query goes as follows...
SELECT COUNT(*) AS total
FROM orders
WHERE id = 'id'
AND TO_DAYS(NOW()) - TO_DAYS(created) <= 90
If someone thinks this is easy enough, it would be even more helpful if
someone could suggest the most efficient was to do the same query but
perhaps return the total for the last 7 days, the last 30 days, and the last
90 days in the same query. I know I can run the query three times but I was
hoping for a suggestion that might be more efficient.
Thanks in advance.
Alan
