homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

php Date comparison headache
Trying to get seconds between MySQL datetime and current datetime

Msg#: 4361277 posted 7:09 am on Sep 12, 2011 (gmt 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




Msg#: 4361277 posted 10:32 am on Sep 12, 2011 (gmt 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 ;)


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

Msg#: 4361277 posted 4:08 pm on Sep 12, 2011 (gmt 0)

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

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved