General Programming Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsProgrammingGeneral Programming Help

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old January 11th, 2004, 03:11 PM
dwamianm dwamianm is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2004
Location: Texas
Posts: 14 dwamianm User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
calculate time difference from a datetime stamp

I am trying to learn the script to calculate time difference between two date time stamps......for example......i have a script that is inserted at the top of each page that tracks a users last activity on the site...so everytime they visit another page on the site that last activities row of the database get updated to the current time of the server........however i am also making a script that will run through a cron and go through the database and delete all users who hasnt had any activity in....lets say 15 - 30 mins.......how do i calculate their time difference using


NOW() - (the lastactivity datetime stamp in the database)


any help would be appreciated

Reply With Quote
  #2  
Old January 11th, 2004, 05:19 PM
dwamianm dwamianm is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2004
Location: Texas
Posts: 14 dwamianm User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
ok heres what im thinking so far se if you can follow:

I am going to use the code below to split up the time from the date. After doing this i will wirte functions to calulate the difference between each part such as

the day (from the last activity table) - (minus) today is greater than 1 then set the online status field in the database to N (it should get to this anyway but as a precautionary measure)

furthurmore i am going to check the year and month for the same thing (which again it shouldnt get to but its a precaution)

The most important for me is the time

after i split the time into its three section

hour
minute
seconds

i will do calculations to on the hour and the seconds to make sure that the 15 - 30 minute conditions are met

tell me if any of you see anything wrong with this method.....


the code below has alot of echo just so that i can see if the date and time were split the way i wanted them to

<?php

include('dbconnect.php');

$query_lastactive = "SELECT

mytable.last_active


FROM

mytable";

$result_lastactive = @mysql_query($query_lastactive);
$numrows_lastactive = @mysql_num_rows($result_lastactive);



if ($result_lastactive){


while ( $row_lastactive = @mysql_fetch_assoc($result_lastactive) ){
$time1="{$row_lastactive['last_active']}";//put the returned time into a variable

$time2 = explode(" ",$time1); //seperate the date from the time

echo "$time2[0]<br>"; //date portion

//=========================== the date is broken down below=================//

$date_part = "{$time2[0]}";
$date1 = explode("-",$date_part);
echo "$date1[0]<br>"; //show the year from the time

echo "$date1[1]<br>"; //show the month from the time

echo "$date1[2]<br>"; //show the day from the time



//==================== time is broken down below==================//


echo "$time2[1]<br>"; //time portion

$time3 = "{$time2[1]}"; //set the time portion as a variable

$time4 = explode(":",$time3); //seperate the time portion

echo "$time4[0]<br>"; //show the hour from the time

echo "$time4[1]<br>"; //show the minute from the time

echo "$time4[2]<br>"; //show the seconds from the time

}
}
else{
echo "OH OH!!!";
}


?>

Reply With Quote
  #3  
Old January 11th, 2004, 07:28 PM
dwamianm dwamianm is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2004
Location: Texas
Posts: 14 dwamianm User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
ok guys look at the script i have below.....alot of it are echos for debuggin purposes (the are commented out) but see if it makes sense


<?php

include('mysql_connect.php');

$query_lastactive = "SELECT

table.username,
table.last_active,
table.online_status


FROM

table


WHERE

table.online_status = 'Y'
";

$result_lastactive = @mysql_query($query_lastactive);
$numrows_lastactive = @mysql_num_rows($result_lastactive);



if ($result_lastactive){


while ( $row_lastactive = @mysql_fetch_assoc($result_lastactive) ){
$time1="{$row_lastactive['last_active']}";//put the returned time into a variable


$time2 = explode(" ",$time1); //seperate the date from the time

//echo "$time2[0]<br>"; //date portion

//=========================== the date is broken down below=================//

$date_part = "{$time2[0]}";
$date1 = explode("-",$date_part);
//echo "$date1[0]<br>"; //show the year from the time

//echo "$date1[1]<br>"; //show the month from the time

//echo "$date1[2]<br>"; //show the day from the time



//==================== time is broken down below==================//


echo "$time2[1]<br>"; //time portion

$time3 = "{$time2[1]}"; //set the time portion as a variable

$time4 = explode(":",$time3); //seperate the time portion

//echo "$time4[0]<br>"; //show the hour from the time

//echo "$time4[1]<br>"; //show the minute from the time

//echo "$time4[2]<br>"; //show the seconds from the time


//================== get current date and time ==========//

$now_year = date('Y'); //current year
//echo "$now_year<<br>"; //show the current year

$now_day = date('d'); //current day
//echo "$now_day<<br>"; //show current day

$now_month = date('m'); //current month
//echo "$now_month<<br>"; //show current month

$now_hour = (date('H') - 3); //current hour the negated (3) is because my database resides on a different server in a different time zone. it will chabge once i finish testing
//echo "$now_hour<<<br>"; //show current hour

$now_min = date('i'); //current minute
//echo "$now_min<<<br>"; //show current minute


$now_sec = date('s'); //current seconds
//echo "$now_sec<<<br>"; //show current seconds


//=============== ok now the fun part. calculations and expansions=============//

//--------------------------------------------first set up some variables to store all the data we need and change the hours and mins to seconds----------//

$la_year = "{$date1[0]}";
$la_month = "{$date1[1]}";
$la_minute = "{$time4[1]}";
$la_hour = "{$time4[0]}";

$la_mininsec = ($la_minute * 60);
$la_hourinsec = ($la_hour * 3600);

$la_finaltime = ($la_mininsec + $la_hourinsec);

//echo "$la_finaltime<br>";


$curr_mininsec = ($now_min * 60);
$curr_hourinsec = ($now_hour * 3600);

$curr_final = ($curr_mininsec + $curr_hourinsec);
//echo "$curr_final<br>";

$time_check = ($curr_final - $la_finaltime);
echo "$time_check<br>";

//====================== now log out anyone that has been inactive for more than 15 mins==============//

If ($time_check >= 120){

$query_logoff = "UPDATE table

SET

table.online_status = 'N'

WHERE

table.username = '{$row_lastactive['username']}'

";

$result_logoff = @mysql_query($query_logoff);

If ($result_logoff){

echo "Logged Off<br>";
}
else{

echo "Could do it<br>";
}
}








}
}
else{
echo "OH OH!!!";
}


?>


SO FAR ITS WORKING!!! DO YOU ALL SEE ANY PROBLEMS WITH IT>>>OR ANY LONG TERMS FLAWS?

Reply With Quote
  #4  
Old January 11th, 2004, 08:44 PM
MadCowDzz's Avatar
MadCowDzz MadCowDzz is offline
I'm Internet Famous
Dev Articles Frequenter (2500 - 2999 posts)
 
Join Date: Jan 2003
Location: Toronto, Canada
Posts: 2,886 MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level)MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level)MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level) 
Time spent in forums: 1 Week 16 h 19 m 35 sec
Reputation Power: 18
I think you're posting way too much code... its discouraging to want to help but not feel like poking through 50-some-odd lines of code... its best to narrow down the problem to a certain block of code...
also, its best to post blocks of php code in [php ][/php ] (without the spaces) syntax... this way the forum will automatically color your code and help ease the readability of yourcode...

With all that aside, you say it works now?
[still willing to help... ;-)]

Reply With Quote
  #5  
Old January 12th, 2004, 09:51 AM
dwamianm dwamianm is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2004
Location: Texas
Posts: 14 dwamianm User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Mr moderator....sorry about the miriad lines of code i have posted...in any case.......the problem with my code is that so far there is no problem so for me to narrow it down would be like narrowing down a paper clip........anyway....please forget all the code that ive posted and refer back to my initial question. i want to see how everyone else would takle this problem.

Reply With Quote
  #6  
Old January 12th, 2004, 09:41 PM
MadCowDzz's Avatar
MadCowDzz MadCowDzz is offline
I'm Internet Famous
Dev Articles Frequenter (2500 - 2999 posts)
 
Join Date: Jan 2003
Location: Toronto, Canada
Posts: 2,886 MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level)MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level)MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level) 
Time spent in forums: 1 Week 16 h 19 m 35 sec
Reputation Power: 18
> calculate time difference between two date time stamps

PHP Code:
if (time() - $timestamp mktime(0,15,0,0,0,0)) { 
         
// Difference it greater than fifteen minutes. 
} else { 
         
// Difference is less than fifteen minutes.



Does that answer the question?

This code compares two timestamps... assuming $timestamp is already set somewhere as a unix time stamp.

Reply With Quote
  #7  
Old January 12th, 2004, 09:52 PM
dwamianm dwamianm is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2004
Location: Texas
Posts: 14 dwamianm User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
can that code calulate the time difference between a DATETIME stamp returned from a database and the current time?

and is the $timestamp variable a predefinded variable?

Reply With Quote
  #8  
Old January 12th, 2004, 10:27 PM
MadCowDzz's Avatar
MadCowDzz MadCowDzz is offline
I'm Internet Famous
Dev Articles Frequenter (2500 - 2999 posts)
 
Join Date: Jan 2003
Location: Toronto, Canada
Posts: 2,886 MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level)MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level)MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level) 
Time spent in forums: 1 Week 16 h 19 m 35 sec
Reputation Power: 18
Wait a second... did you said you're just deleting information from the database that's over 15 minutes old?

how about simply using SQL for this?

MySQL has a lot of handy date/time functions
http://www.mysql.com/doc/en/Date_an..._functions.html

Perhaps TIMEDIFF() is a good function for you ;-)

Reply With Quote
  #9  
Old January 12th, 2004, 11:03 PM
dwamianm dwamianm is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2004
Location: Texas
Posts: 14 dwamianm User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
it would seem that that would be a good solution. However I keep getting SQL errors with this code:

PHP Code:
 $query "SELECT 

DATEDIFF (NOW(), last_active)

FROM 

mytablename"




Whats the problem here?

Reply With Quote
  #10  
Old January 12th, 2004, 11:57 PM
dwamianm dwamianm is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2004
Location: Texas
Posts: 14 dwamianm User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
sorry i meant to put TIMEDIFF

Reply With Quote
  #11  
Old January 13th, 2004, 09:32 AM
MadCowDzz's Avatar
MadCowDzz MadCowDzz is offline
I'm Internet Famous
Dev Articles Frequenter (2500 - 2999 posts)
 
Join Date: Jan 2003
Location: Toronto, Canada
Posts: 2,886 MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level)MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level)MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level) 
Time spent in forums: 1 Week 16 h 19 m 35 sec
Reputation Power: 18
what version of mysql are you running?
[it is mysql you're using, right?]
TIMEDIFF was added in 4.1.1... perhaps you're using an earlier version


Here's an alternate solution:
SELECT TIME_TO_SEC(NOW())-TIME_TO_SEC(last_active) AS time FROM table;

Reply With Quote
  #12  
Old January 18th, 2004, 08:33 AM
dwamianm dwamianm is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2004
Location: Texas
Posts: 14 dwamianm User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Talking

The above piece of code was perfect!!

THANKS MADCOWZZ!!!

here is the completed code:

PHP Code:
<?php

include ('db_connect.php');

$query "SELECT username, (TIME_TO_SEC(NOW()) - TIME_TO_SEC(last_active)) AS time FROM table WHERE online_status = 'Y' ";
$result mysql_query($query);
while (
$row mysql_fetch_assoc($result)){


if (
$row['time'] >= 900){

$query_logoff "UPDATE table

SET

table.online_status = 'N' 

WHERE

table.username = '
{$row['username']}'

"
;

$result_logoff = @mysql_query($query_logoff);

If (
$result_logoff){

echo 
"Logged Off<br>";
}
else{

echo 
"Couldn't do it<br>";
}
}

else {

echo 
"{$row['time']}<br>";

}
}

?>

Reply With Quote
  #13  
Old May 1st, 2004, 04:20 PM
georgek georgek is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: May 2004
Posts: 1 georgek User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
TIMEDIFF in older MySQL

the code above will not work around midnight.
for PHP you are better using strtotime()
something like this should work better

$query = "SELECT username, NOW() as now, last_active AS before FROM table WHERE online_status = 'Y' ";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)){
if (strtotime($row['now'])-strtotime($row['before']) >= 900){


instead of fetching NOW() from mySQL using strtotime("now") may work,
but i'm not sure if there are differences interpreting daylight savings and
timezones between PHP and mySQL

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsProgrammingGeneral Programming Help > calculate time difference from a datetime stamp


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.

© 2003-2018 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap