Getting a total count from specific times

[monkeytooth]

Ok, I am attempting to gather totals. I want to get and display total counts from a specific table of posts. These posts originally had unix timestamps (well still do, i just added a new row to the table to convert them from unix to mysql format). Anyway. I want to get the totals in a number of ways. Total Posts Today, Week, Month and All Time. All time I can do, thats no biggie. Its the specific time frames that are mucking me up..

 

I am assuming my not so optimized concept isnt working well for what I want to do, as it displays nothing. This works for totals rows (but Im sure theres a better quicker way to even do that)

 

$today_count_query   = "SELECT COUNT(*) AS today_count_rows FROM mtp_list_mafia WHERE DATE(sqtime) BETWEEN '2009-07-12 00:00:01' AND '2009-07-12 23:59:59' GROUP BY DATE(sqtime)";
$today_count_result  = mysql_query($today_count_query) or die('View Count SQL Error, query failed<br>' . mysql_error());
$today_count_row     = mysql_fetch_array($today_count_result, MYSQL_ASSOC);
$today_count = $today_count_row['today_count_rows'];
echo $today_count;

 

the sqtime is a DATETIME Column in YYYY-MM-DD HH:MM:SS (hours in 24 hour format example: 2009-05-31 23:00:36) Any ideas? Anyone?

[PFMaBiSmAd]

The previous example that someone gave you that contained - WHERE DATE(sqtime) BETWEEN 'some_start_date' AND 'some_end_date' GROUP BY DATE(sqtime) was a generic example to get the values over a range of dates, group the posts by each day, and get a count of the posts made on each day in the range. Which is what you basically asked.

 

What you are doing - WHERE DATE(sqtime) BETWEEN '2009-07-12 00:00:01' AND '2009-07-12 23:59:59' makes no logically sense because '2009-07-12 00:00:01' is not a date and won't compare with DATE(sqtime), which is a date.