[SOLVED] date from current week

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

[SOLVED] date from current week

Post 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?
Last edited by irealms on Tue Oct 19, 2004 6:09 am, edited 3 times in total.
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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'
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Code: Select all

SELECT * FROM foo WHERE WEEK(datefield) = '$week';
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

Post 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.
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

Post 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());
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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());
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

Post by irealms »

Thanks, i think thats working now :)

To do last week would i just add a -1 after now?
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

Post 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. :)
Post Reply