Date time grouping and count problem.

[raulgh]

Hi guys:

I have a problem on date time and count function.

I have a table which contains the following field:
IndexID: autoincrement;
users: varchar;
ordertime: 0000-00-00 00:00:00
messages: varchar;

What I want to do is to count the number of orders per day from now to 10 or 15 days back and display them in a table. The real difficulty is that the ordertime is precise up to seconds instead of days. The nearest approach I made so far is:

select count(*)
from tablename
where to_days(now()) - to_days(datetime) <= 15
and datetime like '2003-09-01%'

However, it has two drawbacks, firstly, one execution can only get number of orders for one day, so there should be a loop to execute them for 10 to 15 times.

Secondly, overhere, I manually set the parameter as &quot;2003-09-01%&quot;, in real program such as PHP script, it could be diffult to pass in the parameter like this.

Can someone help me to find a better solution for this simple problem? Thanks a lot.

 

[KamranFeroz]

If you've nothing todo with the time saved in the database then you might change the datatype of your field to &quot;Date&quot;.

By this you can easily calculate dates using date functions.

Best of luck.

 

[r937]

select date_format(ordertime,'%Y-%m-%d'), count(*)
from tablename
where to_days(now()) - to_days(ordertime) <= 15
group by date_format(ordertime,'%Y-%m-%d')


rudy

http://r937.com/

 

[raulgh]

Hi KamranFeroz, unfortunately I have to keep time in the database as it is important to us.

Rudy, you are definately expert in SQL. It works.

Thanks your guys for the reply