Getting difference between dates......

crawfd

I'm trying to check for comments written after a login, and if so echo a variable.
I don't believe I have both date formats in synch though. The llogin field below is in the format of '1176163100' in my db, while the field dateadd is in the form of '2006-11-26 05:01:22'. I thought by adding a unix timestamp to the field 'dateadd', it would format it to the similar timestamp but I don't think it is because it's not comparing the dates right.

$SQL= "SELECT UNIX_TIMESTAMP(usrcomments.dateadd),
usrcomments.MemberID, class_members.llogin, 
class_members.MemberID FROM usrcomments, class_members where
usrcomments.MemberID = class_members.MemberID and 
usrcomments.profid = '$a1[MemberID]' AND usrcomments.dateadd > llogin";
if(mysql_num_rows(mysql_query($SQL))>0){   

$m = "profile=true&";  

}

Weedpacket

Why do the two columns, which presumably are both supposed to be storing dates/times, using two completely different types? What types are they? It sounds like llogin isn't using any date/time type at all, because that's a Unix timestamp there (not a MySQL timestamp: a MySQL timestamp for that moment would be more like 20070409235820). Yes, using UNIX_TIMESTAMP() should convert a MySQL date/time to a Unix timestamp. Assuming that it is being stored as such, it should return 1164517282 for that example date (modulo timezone).

crawfd wrote:
but I don't think it is

So have you looked to see whether it is or not?

crawfd

My usrcomments.dateadd column is set to 'datetime' (format:'2006-11-26 05:01:22') and llogin is set to 'int' with a limit of 10 digits (format:1176163100).

When I use the function time(); the result in llogin with it being set as an int column results with a value in the format of 1176163100. Is this value storing minutes and seconds? What kind of timestamp is this considered?

I'd like to leave my llogin as that type for now so I guess my question is how do I format 'usrcomments.dateadd' in my query to a format similar to what my function time(); is giving me for llogin?



$SQL= "SELECT UNIX_TIMESTAMP(usrcomments.dateadd), 
usrcomments.MemberID, class_members.llogin, 
class_members.MemberID FROM usrcomments, class_members where 
usrcomments.MemberID = class_members.MemberID and 
usrcomments.profid = '$a1[MemberID]' AND usrcomments.dateadd > llogin"; 
if(mysql_num_rows(mysql_query($SQL))>0){    

$m = "profile=true&";   

}

Weedpacket

See [man]time[/man] for information about what time() returns.

When I use the function time(); the result in llogin with it being set as an int column results with a value in the format of 1176163100.

You might want to rethink this sentence 🙂

AND usrcomments.dateadd > llogin

Apples and oranges; if you want to compare them you'll need them to be in comparable formats, by using, say, UNIX_TIMESTAMP to format the former so that it's the same format as the latter.

NourB

i'm having a similar problem. however the two dates are stored as DATE in mysql, i would like to get the difference between them and use this difference in a for loop to do some operations?
How can i do this?

Chamelion

When you say difference what kind of result are you expecting?

e.g. do you want it in days/hours/minutes?

If you have two timestamps you can subtract one from the other to get the time in seconds between the two times which you can then format as applicable by dividing by the appropriate number of seconds.

e.g. / 3600 to get the differnece in hours. I would use the floor function to round down.

A snippet of code to find the differnece between a time stored in a db and the current time which will report back in whole hours and then in complete days when hours go above 24.

$timedifference = time() - $row['time'];

If ($timedifference < "86400") { // if less than 1 day
	$age = floor($timedifference / 3600); // 3600 = 1 hour
		if ($age == "1") {
			$age = "1 hour";
			} else {
				$age = $age . " hours";
			} 
} else {
	$age = floor($timedifference / 86400); // 86400 = 1 day
		if ($age == "1") {
			$age = "1 day";
			} else {
				$age = $age . " days";
			} 
}

NourB

Thanks.
I want the difference in days to use it as a counter, so i divide by 86400 right?

Weedpacket

Should be close enough; but depending how accurate you want it to be remember that not all days are 86400 seconds long.

That's silly; if you've got them stored as dates in the database and you want their difference, use the functions supplied in the database.

Chamelion

Weedpacket wrote:
Should be close enough; but depending how accurate you want it to be remember that not all days are 86400 seconds long.

Which days are not 86400 seconds long?

The code snippet I posted will give you the actual time in days (24 hours) however you may want to know how many days have passed which is different.

E.g difference between 20:00 wednesday and 11:00 on Thursday is 15 hours but they are on different days. My script would say that the difference is less than a day but you might want it to say that a day has passed.

bradgrafelman

Chamelion wrote:
Which days are not 86400 seconds long?

Depends where you live/what year it is/etc.

Chamelion wrote:
.g difference between 20:00 wednesday and 11:00 on Thursday is 15 hours

Not always.

Best solution would be:

Weedpacket wrote:
if you've got them stored as dates in the database and you want their difference, use the functions supplied in the database.

If being more accurate doesn't count, then consider this: doing the math with the DB's functions would result in less code to manage as well. Plus... why re-invent the wheel, y'know? :p

Chamelion

bradgrafelman wrote:
Depends where you live/what year it is/etc.

Not always.

Please can you explain this?

bradgrafelman

Daylight Savings Time. A day might be 23, 24, or 25 hours long. :p

Chamelion

bradgrafelman wrote:
Daylight Savings Time. A day might be 23, 24, or 25 hours long. :p

Yeah but if I am saying a day is a 24 hour period then daylight saving time doesn't come into it 🙂

bradgrafelman

Right, and that's what Weedpacket said; for all practical purposes, assuming a day is 24 hours is "close enough." If your calculation covers a day where DST comes into play, however, your calculation will be off slightly.

If that's fine with you, and you'd rather have extra code to calculate the difference, then by all means, go for it; we were just trying to show a different method that would use less code and be more accurate (assuming the DB's software/OS can get it right, that is :p).

Chamelion

DST has nothing to do with my code. If something happens at x time, 4 hours pass but then DST comes into play and the clocks go back, 4 hours have still passed...

bradgrafelman

Logically speaking, you're right - time travels at the same speed, DST or not.

Going back to the OP's scenario, however, the problem is that we're not given the amount of time, we're given two dates (e.g. when you login, an UPDATE query is run that updates your "login_time" column with the result of the NOW() function, and we want to compare that date with the date on comments posted which, again, were timestamped with a function such as NOW()).