[SOLVED] Making Midnight

[monkeytooth]

Maybe im to tired.. or maybe this concept is eluding me completely.

 

Right now I am thinking mktime() will some how work for what I want to do, but what I want to do I am not sure exactly how to do per say.

 

What I think I want to do is find the range of midnight to 11:59pm of every day. I want to ultimately display a count of posts within that time frame every day. I dunno if theres an easier way to do this or not. But thats the idea, figuring it out is beyond me at the moment and I dont know why. The database storing the times is using UNIX timestamps.. any ideas?

[PFMaBiSmAd]

Using a DATETIME data type would simplify and greatly speed up every query involving dates -

 

SELECT *,count(*) FROM your_table WHERE DATE(your_DATETIME_column) BETWEEN 'some_start_date' AND 'some_end_date' GROUP BY DATE(your_DATETIME_column)

 

Doing the above with a Unix Timestamp would involve a costly conversion just to find and group the data sharing the same date.

[DavidAM]

Actually, since unix timestamps are in seconds, it could be something like:

define ('C_ONE_DAY_SECS', 24 * 60 * 60); // Seconds per day
$forDate = mktime(0, 0, 0, 9, 25, 2009);
$endDate = $forDate + C_ONE_DAY_SECS;
$sql = "SELECT * FROM postTable 
WHERE postDate >= $forDate 
AND postDate < $endDate"; 

I use less than ('<') not '<=' on the upper end since the endDate is actually the start of the next day.  Note: BETWEEN is inclusive (i.e. >= AND <=) so it will not work in this situation (unless you subtract 1 (one) from $endDate).

[monkeytooth]

That sounds Ideal actually. Only problem is alot of the stuff I am working with is already set up UNIX time stamps.. Is there a way to convert timestamps into what your talking about?

[PFMaBiSmAd]

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_from-unixtime

[monkeytooth]

Thank you