query date range select

[corillo181]

how do i make a query that select between today and a week from today.

[GingerRobot]

Rather depends on your database setup. If you have a timestamp field, you could do something like:

 

<?php
$today = time();
$next_week = $today+60*60*24*7;
$sql = mysql_query("SELECT * FROM `yourtable` WHERE `datefield` BETWEEN $today and $next_week") or die(mysql_error());
?>

 

If you have a date/datetime field, you could apply the UNIX_TIMESTAMP() mysql function to the field before the comparison.

[corillo181]

and if i wanted to do from last week i would only have to do

 

oneweek = 60*60*24*7;

 

lastweek = time()-oneweek;

 

 

[corillo181]

was that a yes?

because i saw in some website saying that it should be turn into a unix time stamp

[jitesh]

(1) SELECT *

FROM DATA WHERE `timefeield` < CURRENT_TIMESTAMP( )

AND `Name` > ( CURRENT_TIMESTAMP( ) + INTERVAL 168 HOUR )

 

(2) SELECT *

FROM DATA WHERE (DATEDIFF(`timefeild`,CURDATE()) <= 7)

[corillo181]

alright any explanation of what is going on there?

[jitesh]

(1) SELECT *

  FROM DATA WHERE `timefeield` > CURRENT_TIMESTAMP( )

  AND `timefeield` < ( CURRENT_TIMESTAMP( ) + INTERVAL 168 HOUR )

 

Will fecth records between current time and next 168 hours (1 week)

 

(2) SELECT *

  FROM DATA WHERE (DATEDIFF(`timefeild`,CURDATE()) <= 7)

 

Will fetch records from current date to next week.

[corillo181]

i tried it with a date time field and it does not work.

what kind of field doe sit need to be?

[corillo181]

any one can add to this?