calculate days?

[dadamssg]

i want to calculate how many days are between today and a date sepecified. How would i could that to get a number of days...I want to enter that number in my sql query to pull up events that are going on on that day...i can't figure out how to just enter the date into the sql query so im going about it this way

 

heres the code i got to find out tomorrows events...im just gonna replace the 1 with the number of days and hope it works

 

SELECT * FROM test WHERE DATE(start) <= DATE_ADD( CURDATE(), INTERVAL+1 DAY) AND DATE(end) >= DATE_ADD( CURDATE(), INTERVAL +1 DAY) ORDER BY start DESC	

[dadamssg]

not 'could' but *code...oops

[premiso]

SELECT *, DATE_SUB(`end`, `start`) as `dates_between` FROM ...

 

I am not sure if that would work, but I guess it is worth a shot.

[dadamssg]

thanks but nope...is there any tutorial anyone knows of with querying dates in mysql? everything that i think will work doesn't...at all

[premiso]

What value are the dates stored in MySQL? As a timestamp or as the yyy-mm-dd format?

 

EDIT:

http://dev.mysql.com/doc/refman/5.0/en/date-calculations.html

 

Found that article that may help you.

[dadamssg]

they're stored as datetimes...for example start: 2009-02-01 02:00:00  and  end: 2009-02-01 14:00:00

[premiso]

A work around would be this:

 

<?php
$sql = "SELECT * FROM test WHERE DATE(start) <= DATE_ADD( CURDATE(), INTERVAL+1 DAY) AND DATE(end) >= DATE_ADD( CURDATE(), INTERVAL +1 DAY) ORDER BY start DESC";

$query = mysql_query($sql);

while($row = mysql_fetch_assoc($query)) {
    $start = explode(" ", $row['start']);
    $start = explode("-", $start[1]);
    $end = explode("-", $row['end']);
    $end = explode("-", $end[1]);
    $start = $start[2] . "/" . $start[1] . "/" . $start[0];
    $end = $end[2] . "/" . $end[1] . "/" . $end[0];

    $start = strtotime($start);
    $end = strtotime($end);

    $between = round( abs( $start - $end ) / 86400 );
    echo date("d", $between) . " Days between";
}
?>

 

I am not sure if that will work at all. I cannot remember if that is the syntax. Give it a try and see. If you can figure it out with MySQL, that would be the preferred method as it would be faster than strtotime which is extremely slow.

[dadamssg]

im just trying to find out how many days are between a certain date and the current date, not a start and end date... i may have mislead you with the examples of the dates. is there a date_diff($today, '2009-02-25') function or something like that?

[premiso]

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_datediff

 

That may be useful.

[dadamssg]

thanks, ill try messin around with that