CODEWITHSUNDEEP
phpmysqlunix-timestamp
Panama Jack
Panama Jack

Reputation: 24458

How can I calculate the number of days between two dates?

There are plenty of questions and answers to this like this question but I can't find any that actually use it in a MYSQL query. I need to query the database based on certain amount of days between now and a date in the table. I now there are ways to do math in mysql queries but don't quite know how to begin.

Any suggestions how to do this with PHP and mysql?

Sorry for not mentioning it, my date field is unix timestamp. So how do I accomplish that with that format?

Upvotes: 0

Views: 731

Answers (3)

Teena Thomas
Teena Thomas

Reputation: 5239

In PHP, you may do this:

$date1 = '1348117200000'; //From table for date 09/20/2012    

$date2 = time(); //Current time 

$diffDate = $date1 - $date2;

then use $diffDate in your query.

 $sql = 'SELECT * FROM DATE_TABLE WHERE DATE_FIELD = ' . $diffDate;

Upvotes: 0

Mike Brant
Mike Brant

Reputation: 71384

I assume you are looking for something like this for datetime field:

SELECT *
FROM table
WHERE datetime_field BETWEEN (NOW() - INTERVAL 2 DAY) AND NOW()

Slight modification for date field

SELECT *
FROM table
WHERE date_field BETWEEN (DATE(NOW()) - INTERVAL 2 DAY) AND DATE(NOW())

You shouldn't use unix timestamp fields to store datetimes for a number of reasons. But if you really insist on doing it that way.

SELECT *
FROM table
WHERE date_field BETWEEN UNIX_TIMESTAMP(NOW() - INTERVAL 2 DAY) AND UNIX_TIMESTAMP(NOW())

Make sure the field in WHERE clause has an index on it.

Upvotes: 2

JConstantine
JConstantine

Reputation: 3931

You mean like the DateDiff function?

SELECT DATEDIFF('2008-11-30','2008-11-29') AS DiffDate

http://www.w3schools.com/sql/func_datediff_mysql.asp

$sql = "SELECT DATEDIFF('".$startDate."', '".$endDate."') AS DiffDate";

Then run that through a PDO or mysqli_ function.

Upvotes: 1

Related Questions