Page 1 of 1

[SOLVED] date from current week

Posted: Mon Oct 18, 2004 5:20 am
by irealms
Hi,

I need to draw data from a table limited to dates from the current week. I've tried lots of variations using date and time functions from the mysql manual but can't get it to work. Is it possible to just draw out all date from a week if i have the week number?

Posted: Mon Oct 18, 2004 8:23 am
by kettle_drum
Use mktime() with the start of the week and then with the end of the week and you'll have the start and end of the week in unix epoch which you can then either use like this or transform into a date value and use in an sql query:

Code: Select all

SELECT * FROM blah WHERE time_added BETWEEN '$start_time' AND '$end_time'

Posted: Mon Oct 18, 2004 11:33 am
by timvw

Code: Select all

SELECT * FROM foo WHERE WEEK(datefield) = '$week';

Posted: Mon Oct 18, 2004 12:05 pm
by Weirdan
timvw wrote:

Code: Select all

SELECT * FROM foo WHERE WEEK(datefield) = '$week';
that would fetch data for $week range from any year, not only current one.

Posted: Mon Oct 18, 2004 2:14 pm
by irealms
the start and end of week need to be calculated from a date field. So it will draw all orders places since say, the last sunday morning.

Posted: Tue Oct 19, 2004 5:17 am
by irealms
timvw wrote:

Code: Select all

SELECT * FROM foo WHERE WEEK(datefield) = '$week';
I could use this with AND YEAR to make it use currrent year too i think. But how do i get the current week? Would this work?

$week = date('w');

*edit*

Tried this but it's returning "2" as the current week for some reason. Any ideas?

Current code:

Code: Select all

//find current week, even just using date('w'); gave week as "2" as well
//the method of setting the variable below ensures the right year is used
$week = date('w', mktime(0, 0, 0, date("m"),  date("d"),  date("Y")));
//query
$weekorders = "SELECT * FROM orders WHERE WEEK(dateadded)='$week' AND status!='Quote' AND sub='0'";
$weekordersq = mysql_query($weekorders, $db_conn) or die("Query $weekordersq Failed".mysql_error());

Posted: Tue Oct 19, 2004 5:32 am
by timvw
in the case you don't have the weeknumber, but need to generate it

Code: Select all

SELECT * FROM bar WHERE YEARWEEK(datefield)=YEARWEEK(NOW());

Posted: Tue Oct 19, 2004 5:56 am
by irealms
Thanks, i think thats working now :)

To do last week would i just add a -1 after now?

Posted: Tue Oct 19, 2004 6:09 am
by irealms
timvw wrote:in the case you don't have the weeknumber, but need to generate it

Code: Select all

SELECT * FROM bar WHERE YEARWEEK(datefield)=YEARWEEK(NOW());
For last week i changed the above code to:

Code: Select all

SELECT * FROM bar WHERE YEARWEEK(datefield)=YEARWEEK(NOW());
seems to be working, thanks again. :)