[Resolved] date range based on current and previous month?

[Lcousins]

i'm putting together a little timesheet system which outputs statistics at the other end. one of my mysql queries is as follows -

SELECT * FROM timesheet WHERE tsdate > $start_date AND tsdate < $end_date

except that I dont want to have to put in a start date and end date, I want it to pick all records with a tsdate within the current month (and eventually another one which picks all records for the previous month) any ideas?

l.

[mrhappiness]

assuming tsdate is not a unix timestamp:

SELECT * FROM timesheet WHERE EXTRACT(YEAR_MONTH FROM tsdate) = EXTRACT(YEAR_MONTH FROM NOW())

for current month

SELECT * FROM timesheet WHERE EXTRACT(YEAR_MONTH FROM tsdate) = EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 1 MONTH)

for previous month

(not tested, but i like it 😉)

[Lcousins]

its a great idea, it looks like it should work, it comes up with no errors but alas it comes up with no results either 🙁 its definately a good starting point towards finding a solution though, thanks! 😃

l.

[mrhappiness]

So why not posting some examle records?

[Lcousins]

because i've been testing things 😉 and it now works.

the only thing i've changed to your example was i've removed the now() bit and done that outside of the query

$rdate = date("Y-m-j");
$year = substr($rdate,0,4);
$mon = substr($rdate,5,2);
$thismonth = $year.$mon;
[code=php]

making the query -

[code=php]SELECT * FROM timesheet WHERE EXTRACT(YEAR_MONTH FROM tsdate) = '$thismonth'

which produces the results I was after. Many thanks for your help MrHappiness! 🙂

l.

[Lcousins]

and for completeness sake, this is the actual query you just helped me create -

SELECT tsactivity, SUM(tsduration) FROM int_nat_timesheets WHERE EXTRACT(YEAR_MONTH FROM tsdate) = '$thismonth' GROUP BY tsactivity