[Resolved] date() and time(), etc.

Cailean

I seem to have a mental block about how the various date() and time() functions interoperate.

my (untested) code is this:

	$time_limit=time() - 15552000;		// ~six months ago
	$query = "SELECT * FROM news WHERE group_id='".$group_id."' and date>='".$time_limit."' SORT BY date DESC";

while this will do roughly what I want, I would like it to round to the month so if it is currently March it will pull everything from September->March. Is mktime() the thing to use here?

devinemke

i assume that the "date" column in an integer column storing unix timetsamps?

$six_months_ago = strtotime('6 months ago');
$six_months_ago = strtotime(date('Y-m', $six_months_ago) . '-01');
$query = "SELECT * FROM news WHERE group_id = '" . $group_id . "' AND date >= '" . $six_months_ago . "' SORT BY date DESC";

you should really consider doing this using your RDBMS's date/time functions rather than using PHP. your post doesn't mention which RDBMS you are using but if it's mySQl then read this.

zbee

My suggestion would be to use the regular date format ('YYYY-MM-DD'). That way, if you wanted to get a date 6 months earlier, you'd use:

$actual_month = date("n") - 6;
$actual_day = date("d");
$actual_year = date("Y");
$six_m_earlier = date( "Y-n-d", mktime(0,0,0,$actual_month ,$actual_day,$actual_year) );

That way, your query would look like this:

    $query = "SELECT * FROM news WHERE group_id='".$group_id."' and date>='".$six_m_earlier."' SORT BY date DESC";

Cailean

yes, the date column (in mySql) was intended to use unix timestamps - only because they seemed to me to be the most flexible for formatting with php's date() function...

I read the mySql page devinemke mentioned and like the idea of the query function. I assume, then, that I set the date column with now() in the INSERT query. This would store a value with the format 'YYYY-MM-DD HH:MM:SS' which could then be used with strtotime(date()). Do I have that about right?

so my new query looks like this:

$query = "SELECT * FROM news WHERE group_id='".$group_id."' and DATE_SUB(CURDATE(),INTERVAL 6 MONTH)<=date SORT BY date DESC";

Which I like much better. I assume that you could use a variable to set the INTERVAL?

BTW, I realize now that this should've been in the Database section - my bad.

devinemke

Originally posted by Cailean
I assume, then, that I set the date column with now() in the INSERT query. This would store a value with the format 'YYYY-MM-DD HH:MM:SS' which could then be used with strtotime(date()). Do I have that about right?

yes but you must change the field type from INT to DATE or DATETIME. once you do that then then is no reason to use strtotime, you can use mySQL's built in date/time functions.

Cailean

Originally posted by devinemke
yes but you must change the field type from INT to DATE or DATETIME. once you do that then then is no reason to use strtotime, you can use mySQL's built in date/time functions.

php's date() function understands mySql's date format? Why did I think that date() required an epoch timestamp? Hmmm.