0

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?

Community
  • 1
  • 1
Panama Jack
  • 24,158
  • 10
  • 63
  • 95

3 Answers3

2

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.

Mike Brant
  • 70,514
  • 10
  • 99
  • 103
  • I forgot to mention my field is unix timestamp. How can that be done? – Panama Jack Sep 13 '12 at 20:09
  • Thanks for the help. I know should use unix timestamp but that how it was made and it's easier to manipulate with PHP. Don't know if I can convert it but I have til 2038 until I have to worry about it. LOL – Panama Jack Sep 14 '12 at 02:15
1

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.

JConstantine
  • 3,980
  • 1
  • 33
  • 46
0

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;
Teena Thomas
  • 5,139
  • 1
  • 13
  • 17