Date Range In PHP?

Question

I have a DB created by a third party vendor that I'm now writing a new UI for.

The DB stores event start times as unix timestamps (in GMT). What I need to do is query this for a one day range. So presumably I simply need to do a:

SELECT * WHERE start > $last_night_at_midnight AND start < $tonight_at_midnight

The problem I'm running into is a simple way to combine the date/time functions in PHP to create those variables. It feels like everything I'm doing is way too complicated for such a simple procedure.

Does anyone have a simple solution to this?

Cheers

Answer 1

The time() function in PHP might be of use here: http://be2.php.net/manual/en/function.time.php

More specificaly, the example given on that page will probably tell you what you need to know. Take a look at the 3rd echo statement

Answer 2

Check out PHP's strtotime() for plain-English to unix time conversion.

This lets you use "today midnight" and "tomorrow midnight" and other similar constructs, as Paolo Bergantino demonstrates.

Answer 3

strtotime - the awesomenest PHP time function ever.

$last_night_at_midnight = strtotime("today midnight");
$tonight_at_midnight = strtotime("tomorrow midnight");

Answer 4

strtotime is a useful one here...

$t1=strtotime("today");
$t2=strtotime("tomorrow");

$sql="select * from foo where timecol between $t1 and $t2";

At time of writing (09-03-2009 19:06), today equates to 09-03-2009 00:00:00 and tomorrow equates to 10-03-2009 00:00:00

Answer 5

You could either create those Variables in PHP or in Mysql...

$tonight_at_midnight    = mktime(0,0,0);
$last_night_at_midnight = $tonight_at_mindnight - 86400;

The other possibility is in SQL:

SELECT * FROM tbl1 WHERE start > UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY) 
AND start < UNIX_TIMESTAMP(CURDATE())

(not tested)

Answer 6

If you need to pick arbitrary days, use gmmktime

Answer 7

This is a really backwards way of doing it, but if you need to be able to do various date ranges, not just today or one day, you could combine the odd time/date features of both languages and go with:

$php_start = strtotime("Some valid date expression");
$php_end = strtotime("Some other valid date expression");

$result = my_sql_query("
          Select * FROM someDB 
          WHERE DATE(FROM_UNIXTIME(date_column)) 
          BETWEEN FROM_UNIXTIME($php_start) AND FROM_UNIXTIME($php_end)");

using the DATE() in the query ensures that the sql won't bother looking at the time part of the data, and using FROM_UNIXTIME for all three means that the sql server is consistent in how it derives the dates.

My favorite way of getting a date range for just today is:

  $today_start = strtotime("today");
  $today_end = strtotime("+1 day", $today_start);

strtotime is actually better, I've found, then adding 86400 or anything like that, because strtotime handles DST better. Found that out last week.