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




---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Thu, 2003-12-11 at 08:44, Alan T. Miller wrote:
CURRENT_DATE - created <= 90

I don't think you do it in SQL without three separate queries. You
can't use GROUP BY because the conditions are not mutually exclusive.

Use a procedural language.

--
Oliver Elphick Oliver.Elphick (AT) lfix (DOT) co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"The spirit of the Lord GOD is upon me; because the
LORD hath anointed me to preach good tidings unto the
meek; he hath sent me to bind up the brokenhearted, to
proclaim liberty to the captives, and the opening of
the prison to them that are bound."
Isaiah 61:1


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Thu, 2003-12-11 at 10:01, Oliver Elphick wrote:
Sorry, I forgot it was a timestamp column, not a date:

CURRENT_DATE - CAST(created AS date)

--
Oliver Elphick Oliver.Elphick (AT) lfix (DOT) co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"The spirit of the Lord GOD is upon me; because the
LORD hath anointed me to preach good tidings unto the
meek; he hath sent me to bind up the brokenhearted, to
proclaim liberty to the captives, and the opening of
the prison to them that are bound."
Isaiah 61:1


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Dear Alan T. Miller ,


select
count(*) as total
from orders
where id = 'id ' <------- For a single id
AND ( age(current_timestamp,created) <= cast('90 days' as interval)) ;
------------------
For Multiple ids do:
-------------------
select
count(*) as total ,
orders.id as Order_No
from orders
where id = 'orders.id ' <------- For a multiple id
AND ( age(current_timestamp,created) <= cast('90 days' as interval))
group by orders.id;

Tell me if this works plz,

Regards,
V Kashyap

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Thu, Dec 11, 2003 at 01:44:31 -0700,
"Alan T. Miller" <amiller (AT) hollywood101 (DOT) com> wrote:
If you want to get several date periods covered in one query, you can
use the CASE statement to get a value of 1 when the record is in the
correct range and 0 when it is not. Doing a SUM of each of three different
CASE statements will get you the numbers you need. You will still want
to use a where clause if the largest period contains only a small fraction
(maybe 10%) of the data so that an index scan can be used instead of
a sequential scan over all of the data.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)