Search code examples
phpmysqlunix-timestampepoch

Trouble with MYSQL query BETWEEN two epoch timestamps?

I have epoch timestamps in my mysql database, I am using a Jquery datepicker to choose BETWEEN two dates. It works great when I choose at minimum between 1 day, but if I choose Sunday - Sunday ( the same day ) it should bring back records that have fallen on that day.

I am assuming it is because of the epoch timestamp integrated withing the datepicker.. When you choose a date, like today, it comes back with : 

BETWEEN 1317103200 AND 1317103200 ....

I know how epoch timestamps work and it seems like it is to the second when you convert and do the math... How do I implement a DISTINT value for that FULL DAY -- so it just looks at the full 24 hours and not down to the millisecond???

SELECT category, id, date, title 
FROM myTable
WHERE date BETWEEN $beginDate AND $endDate AND...

Thanks so much!!

Solution

  • Use the mktime() function where $month, $day and $year have been populated with the month, day and year from your date text box:

    // Assumes date is in this format: dd/mm/yyyy, and your $_GET key is 'date'
list($day, $month, $year) = explode('/', $_GET['date']);

$beginDate = mktime(0, 0, 0, $month, $day, $year);
$endDate = mktime(23, 59, 59, $month, $day, $year);