Count rows added today, yesterday...and other times

Question

I was given good direction to solve a problem today from here but I got stuck in trying to follow this direction.

I would like to retrieve a count for all rows from a table for the past 30 days using my date field. I populate these fields using "now()" and they are in the format "2009-03-04 14:58:01".

• I need a count for the rows added today, yesterday, 3 days ago...etc
• I guess I can then use the above to get a count for each months?
• Finally a count for the year, year before etc using the months as total?

I was hoping not to specify dates in my query and for the query to just be generic and work out counts relative to today. I was hoping also I could do it accurately too by taking into consideration different months have different number of days.

How can I get this count using just SQL? I then can use the result set as an array and parse this with PHP.

Answer 1

Look into MySQLs DATE_ADD and DATE_SUB function it will give you what your looking for.

Answer 2

Look into DATEDIFF... I'm not sure of the exact syntax with mysql, but it should end up being someing similar to:

select * from Table WHERE datediff(StartDate, Today) < DaySpan

Answer 3

I'm not entirely sure what your database structure looks like, but you can probably achieve what you want using GROUP BY:

To aggregate by day:

SELECT date(my_date_field) d, count(*) c FROM tbl
GROUP BY d
ORDER BY d

To aggregate by month:

SELECT year(my_date_field) y, month(my_date_field) m, count(*) c FROM tbl
GROUP BY y, m
ORDER BY y, m

To aggregate by year, you can simply remove the month parameter from the sample above.

If you want to limit the result to specifics you can use WHERE directly on my_date_field or HAVING on d, y or m. Use DATE_SUB to calculate the date relative to the current.

Answer 4

Number of records inserted yesterday:

select count(*) from mytable where date(myfield)=date(date_sub(now(),interval 1 day));

For the year:

select count(*) from mytable where year(myfield)=year(now());

And so on...

Answer 5

Hi, try this solution based on grouping by date, month and year:

-- Testing data
DECLARE @TestTable TABLE(ID INT, AddDate DATETIME)
INSERT INTO @TestTable
SELECT 1, '2009-03-04 14:58:01' UNION
SELECT 1, '2009-05-14 14:58:01' UNION
SELECT 1, '2009-05-14 14:58:02' UNION
SELECT 1, '2009-05-24 14:58:01' UNION
SELECT 1, '2009-05-24 14:58:02' UNION
SELECT 1, '2009-05-24 14:58:03' UNION
SELECT 1, '2009-07-04 14:58:01' UNION
SELECT 1, '2009-09-04 14:58:01' UNION
SELECT 1, '2010-01-04 14:58:01' UNION
SELECT 1, '2010-03-04 14:58:01' UNION
SELECT 1, '2010-03-04 14:58:02'

-- MySQL Answer
-- for each day
SELECT EXTRACT(YEAR_MONTH_DAY, AddDate), COUNT(ID) FROM @TestTable
GROUP BY EXTRACT(YEAR_MONTH_DAY, AddDate)
-- for each month
SELECT EXTRACT(YEAR_MONTH, AddDate), COUNT(ID) FROM @TestTable
GROUP BY EXTRACT(YEAR_MONTH, AddDate)
-- for each year
SELECT EXTRACT(YEAR, AddDate), COUNT(ID) FROM @TestTable
GROUP BY EXTRACT(YEAR, AddDate)

-- SQL Answer
-- for each day
SELECT dateadd(day, datediff(day, 0, AddDate)+0, 0), COUNT(ID) 
FROM @TestTable
GROUP BY dateadd(day, datediff(day, 0, AddDate)+0, 0)
-- for each month
SELECT dateadd(month, datediff(month, 0, AddDate)+0, 0), COUNT(ID) 
FROM @TestTable
GROUP BY dateadd(month, datediff(month, 0, AddDate)+0, 0)
-- for each year
SELECT dateadd(year, datediff(year, 0, AddDate)+0, 0), COUNT(ID) 
FROM @TestTable
GROUP BY dateadd(year, datediff(year, 0, AddDate)+0, 0)