Thread: Something like 'to_days' in postgresql? Help with a MySQL migration...

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

On Thu, 2003-12-11 at 08:44, Alan T. Miller wrote:
> 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

CURRENT_DATE - 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.

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@lfix.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

On Thu, 2003-12-11 at 10:01, Oliver Elphick wrote:
> On Thu, 2003-12-11 at 08:44, Alan T. Miller wrote:
> > 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
>
> CURRENT_DATE - created <= 90

Sorry, I forgot it was a timestamp column, not a date:

   CURRENT_DATE - CAST(created AS date)

--
Oliver Elphick                                Oliver.Elphick@lfix.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

Dear Alan T. Miller  ,

>I am migrating an application over from mysql to postgresql and am a little
>confused on how to write the following query in PostgreSQL.
>
Migrating to PostgreSQL thats a wise mans act  :-)

>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
>
>
Yes , it can be done this is how you will do it :
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

On Thu, Dec 11, 2003 at 01:44:31 -0700,
  "Alan T. Miller" <amiller@hollywood101.com> wrote:
> 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.

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.