First Day & Last Day Of The Last 6 Months

[refiking]

I need to retrieve records from my db that are timestamped.  I need 6 different queries for the last 6 months.  I need all the records in a given month.  So, how do I code that exactly?  Here's what I have so far

 

$time = time();
$lastmonth = strtotime('-1 month');
$sql = mysql_query("SELECT * FROM reports WHERE time < '$time' AND time > '$lastmonth'");

 

That string won't give me exactly what I'm looking for so, what can I change or do i need to scrap the whole thing and start from scratch?

[F1Fan]

I'm guessing you want the first second of the month to the last second of the month. Of so, try this:

 

<?php
$month = '2008-06-01'; //here's the month you want, as an example
$start = strtotime("$month 00:00:00");
$end = strtotime("$month 00:00:00 +1 month");
$sql = mysql_query("SELECT * FROM reports WHERE time BETWEEN '$start' AND '$end'");
?>

[refiking]

OK.  So, here's the code I have so far:

 

$sepsql = mysql_query("SELECT * FROM records WHERE time BETWEEN '$jstart' AND '$jend'")or die(mysql_error());
$sepnum = mysql_num_rows($sepsql);

 

When I echoed results, here's what I got:

 

$jstart = 09-01-08 00:00:00

$jsend = 10-01-08 00:00:00

$sepnum = 0

 

$sepnum should have been 6

[F1Fan]

That looks OK to me. Is it working?

[refiking]

The problem is the value for $sepnum should be 6, since there are 6 records that match the query string.  But, it returns a value of 0

[F1Fan]

Ah, sorry. Well, what format is your "time" column? Also, "time" is a keyword in SQL, so I suggest adding double quotes:

 

$sepsql = mysql_query("SELECT * FROM records WHERE \"time\" BETWEEN '$jstart' AND '$jend'")or die(mysql_error());
$sepnum = mysql_num_rows($sepsql);

[refiking]

The format is timestamp.  I can just change the time field to vtime if that helps.

 

[F1Fan]

Did you try the quotes?

[refiking]

Yeah.  I also changed the `time` filed to `vtime` and still nothing

[F1Fan]

Try echoing your query and running it manually:

 

$query = "SELECT * FROM records WHERE \"vtime\" BETWEEN '$jstart' AND '$jend'";
echo $query;
$sepsql = mysql_query($query)or die(mysql_error());
$sepnum = mysql_num_rows($sepsql);

[discomatt]

SELECT `column` FROM `table` WHERE MONTH(`vtime`) = '09' AND YEAR(`vtime`) = '2008'

[F1Fan]

Man, I hate that. You spend WAY too much time on a problem, and someone comes up with some brilliantly simple solution like that. Nice, discomatt!

[refiking]

Man, I hate that. You spend WAY too much time on a problem, and someone comes up with some brilliantly simple solution like that. Nice, discomatt!

 

Ditto. Thanks for the assistance guys!

[discomatt]

To get previous months ( in this example, 2 months ago )

 

SELECT
 `your`, `columns`, `here`,
 @then := DATE_SUB( NOW(), INTERVAL 2 MONTH ) as `prev_date`
FROM
 `your_table`
WHERE
 MONTH( `vtime` ) = MONTH( @then )
   AND
 YEAR( `vtime` ) = YEAR( @then );

 

Man, I hate that. You spend WAY too much time on a problem, and someone comes up with some brilliantly simple solution like that. Nice, discomatt!

 

Thank you

[refiking]

How can I search for records on a given week?  Say 01/01/08 - 01/08/08