Welcome to WebmasterWorld Guest from

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

php Date comparison headache

Trying to get seconds between MySQL datetime and current datetime

7:09 am on Sep 12, 2011 (gmt 0)

New User

joined:July 14, 2011
posts: 19
votes: 0


probably a very basic one but I have been banging my head against it for the past 3 hours and it is proving to be a bit of a head doer - hopefully somebody could point me in the right direction.

I have a datetime value pulled from a mysql database - this date is originally inserted into the db using NOW(). This date is publishdate - it can be a date before today or a date after today.

What I would like to do is:
- display results that have a publish date before today
- display results that have a publish date within 1 week of today, then insert this value into a javascript counter which counts down to the time of publishing in days:hours:mins:seconds. So I guess I will need to get the difference in seconds, and calculate days, mins etc from this seconds value. No problem there, only with the comparison.

So far, the closest I have gotten is to use date_create on the dates:

$datevar = date_create($date);
$today = date_create(date('Y-m-d H:i:s'));

Then use date_diff (only tried difference in days so far) - however date_diff gives a negative value for published < today, and a zero value for anything at all > today. Plus, that is days anyway so I am probably barking up the wrong tree.

I have put this into a function, passing the db publish date as a parameter straight from the db:

function timeUntil($date){
$datevar = date_create($date);
$today = date_create(date('Y-m-d H:i:s'));
//do something good here
return $seconds;

//db published - e.g. 2008-06-27 14:32:59
echo daysUntil($mediaData['published'])." seconds";

// desirable output e.g...
//-568835 seconds
// 699893 seconds

Then minus value results are displayed, and a quick calculation on the seconds-until-publish to ascertain which are to be published within a week. So I do need the positive or negative I guess.

I have tried this with mktime, date, date_create, strtotime, plus a few very convoluted array methods that seem hugely complex for something that should seem to be quite straightforward (for someone that knows what they are doing).

Hopefully some helpful folks here can throw me a line, as I am slowly sinking...

Many thanks

10:32 am on Sept 12, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:July 8, 2010
posts: 114
votes: 0

Something like this should work.....

$query = "SELECT *, TIME_TO_SEC(TIMEDIFF(now(), start)) as difference FROM {myTable} WHERE start <= ( date_add(Now(), interval 1 week) )";

[id] => 2
[start] => 2011-09-08 12:24:15
[difference] => 346112

[id] => 4
[start] => 2011-09-14 12:24:45
[difference] => -172318

Good luck ;)
4:08 pm on Sept 12, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
votes: 0

Right, use the time and date functions, nothing like making more work for yourself by trying to sort it out in programming. :-)

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members