Timestamp

[klik]

Hi,

I have the following code:

//method to only update points on a per hour basis
		mysql_query("UPDATE cms_users SET user_points = user_points+1 WHERE username= '$username' AND  point_interval < NOW()- 3600");

	mysql_query("UPDATE cms_users SET point_interval = now() WHERE username='$username'");

What I'm trying to acheive with this, is to update the users' points table, if they havent logged in within the last hour. If the duration between log ins is mre than an hour, then proceed to update the points table. The point_interval column is a timestamp column, storing the time of the last log in..

However, it doesn't seem to be working correctly.

Is my logic correct?

[weekender]

Your problem is caused by mysql timestamps being different to unix timestamps.

A unix timestamp is the number of seconds since the start of the unix epoch - midnight on Jan 1st, 1970. As this is an integer (number of seconds), you can do things like add an hour (+3600) or subtract a day.

A mysql timestamp is more readable, but less usable. It's the date in the format YYYYMMDDHHMMSS (ie reverse polish notation). To subtract an hour, you'd need to decrement the HH bit and possiby the DD bit too.

To get a mysql query to give you a unix timestamp, you can use the UNIX_TIMESTAMP mysql function

SELECT UNIX_TIMESTAMP(point_interval) AS unix_interval FROM cms_users WHERE 1

similarly, you should be able to use this function to get all the records from the last hour. Using your query with the function gives

UPDATE cms_users SET user_points = user_points+1 WHERE username= '$username' AND  UNIX_TIMESTAMP(point_interval) < UNIX_TIMESTAMP(NOW()) - 3600

although i'm not entirely sure about that very last bit without checking...

hope that helps

adam

[klik]

Thanks for the response.

I have tried putting the following in my code, but still, the user_points clolumn is not updating.

//method to only update points on a per hour basis
		mysql_query("UPDATE cms_users SET user_points = user_points+1 WHERE username= '$username' AND  UNIX_TIMESTAMP(point_interval) < UNIX_TIMESTAMP(NOW() - 3600)");

	mysql_query("UPDATE cms_users SET point_interval = now() WHERE username='$username'");

[weekender]

change these to select queries and take a look at the output

adam

[devinemke]

Originally posted by weekender
...A mysql timestamp is more readable, but less usable. It's the date in the format YYYYMMDDHHMMSS

be advised: as of mySQL 4.1 the timestamp format has changed to the same as the normal mysql DATETIME format (YYYY-MM-DD HH:MM:SS)

[weekender]

whoops. not too hot on mysql. The theory's all there, though

adam