MySQL week interval tweaking

monday to monday, not sunday to sunday

[CodilX]

Hello,

I'm new to this forum, hope to get some help :)

I'm fairly new to MySQL scripting, and I'm in need of help.

Basicly I have a db, where the visitors from my site are stored. Their IP, referrer, os, browser, date visited, etc.

Now after reading some posts in this forum I made a little script to show how many visitors came LAST week

$lweek_command='SELECT COUNT(*) AS total FROM visits WHERE YEARweek( DATE ) = YEARweek(CURRENT_DATE - interval 7 DAY)';
$lweek_query=mysql_query($lweek_command, $connect);
$fetch_lweek=mysql_fetch_array($lweek_query);
$lastweek=$fetch_lweek['total'];

THIS week

$week_command='SELECT COUNT( * ) AS total FROM visits WHERE YEARweek( DATE ) = YEARweek( CURRENT_DATE )';
$week_query=mysql_query($week_command, $connect);
$fetch_week=mysql_fetch_array($week_query);
$thisweek=$fetch_week['total'];

It works perfectly, but the thing is, that MySQL shows from Sunday to Sunday. I need it to show Monday to Monday. I tried adding -1 DAY, but I get a MySQL error.

Any ideas?

[edited by: CodilX at 11:04 am (utc) on June 10, 2007]

[phranque]

do it like this for monday:

YEARWEEK( DATE, 1 )

[CodilX]

Thanks, but it didn't work.

I tried it, but instead of showing this weeks visitors from monday (today) it shows me the last weeks visitors

[phranque]

what was the exact query you tried?

[CodilX]

SELECT COUNT( * ) AS total FROM visits WHERE YEARweek( DATE, 1 ) = YEARweek( CURRENT_DATE )

DATE is the name of a field in my db, yyyy-mm-dd format

[phranque]

try this:

SELECT COUNT( * ) AS total FROM visits WHERE YEARWEEK( DATE, 1 ) = YEARWEEK( CURRENT_DATE, 1 )

[CodilX]

thanks so much!

it works perfectly with This Weeks visitors, but when I add this to the Last Weeks visitors, it doesn't change anything

SELECT COUNT(*) AS total FROM visitors WHERE YEARweek( DATE, 1 ) = YEARweek(CURRENT_DATE, 1 - interval 7 DAY)

[phranque]

i think this is what you are trying to do:

SELECT COUNT(*) AS total FROM visitors WHERE YEARWEEK( datetime_column, 1 ) = YEARWEEK(DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY), 1)

[physics]

Hi CodilX and welcome to WebmasterWorld.com!

There may be a more elegant way to do it but if I had your problem I'd use PHP's strtotime() to get "last monday"

$lastmonday = strtotime("last monday");
$twomondaysago = $lastmonday - 60*60*24*7;

$query = "... yourdate >= DATE_SUB(".date($lastmonday).", INTERVAL 7 days) and yourdate <= ".date($lastmonday)." ... ";

Repeat as necessary for other weeks ...

p.s. Sorry if the above doesn't work, I haven't tested it ... just wanted to get you thinking about going about it in a more general way by using php to get specific dates ...

[edited by: physics at 12:08 am (utc) on June 14, 2007]

[CodilX]

thank you both sooo much! awesome help! couldn't have done this without you. thank you

[phranque]

physics sez:

Hi CodilX and welcome to WebmasterWorld.com!

as i didn't notice or forgot to offer:
welcome WebmasterWorld, codilx!

glad we could help...