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 Y-m-d h:i:s.

• 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

Since none of the answers explicitly mention how to get the date for today:

select count(*) from transactions where date(created_at)=date(now());

For completeness there's also the following that seems to be functionally equivalent:

SELECT count(*) AS today 
FROM transactions 
WHERE created_at >= CURDATE()

CURDATE stands for current date (i.e. today starting from 00:00 midnight rather than the last 24 hours), but now() also seems to use the same definition so at least in my tests there was no real difference between the commands.

Answer 2

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 3

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)

Answer 4

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

Answer 5

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 6

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