CODEWITHSUNDEEP
phpsqldatetime
Cory Dee
Cory Dee

Reputation: 2891

Date Range In PHP?

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

Upvotes: 0

Views: 1545

Answers (7)

Anthony
Anthony

Reputation: 37045

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.

Upvotes: 4

Jeremy L
Jeremy L

Reputation: 7797

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.

Upvotes: 6

Paolo Bergantino
Paolo Bergantino

Reputation: 488384

strtotime - the awesomenest PHP time function ever.

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

Upvotes: 5

Eddy
Eddy

Reputation: 1862

If you need to pick arbitrary days, use gmmktime

Upvotes: 1

dog
dog

Reputation: 410

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)

Upvotes: -1

Paul Dixon
Paul Dixon

Reputation: 300825

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

Upvotes: 3

Vordreller
Vordreller

Reputation: 2530

The time() function in PHP might be of use here: https://www.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

Upvotes: 0

Related Questions