Can't get my head aroud date time MYSQL - hopefully simple question.

[Anidazen]

Hello,

 

Cheers for reading.

 

 

 

Basically, I simply want to record a time with every SQL insert, and then use a function to count how many times that same user (identified by IP) has run the script in the last 24 hours.

 

So all I want is a format to record in SQL and then a query to check this. With PHP unix timestamp this would be a simple matter of returning where the timestamp is greater than time() - 86400 (the number of seconds in 24 hours). However, I don't know how to set UNIX timestamps...

 

 

 

Anyone able to help?

[monk.e.boy]

In SQL you can use the built in function NOW(), so you INSERT would look like:

 

INSERT INTO table (ip,user_id,time) VALUES('10.01.10.01',99,NOW())

 

This will use the current time as the value.

 

You can also use the DATE TIME functions (google mysql date functions ) to do some maths like: NOW()-24hours

 

Put this in you select

 

SELECT name FROM table JOIN users ON users.id=table.user_id WHERE time > (NOW()-24hours)

 

monk.e.boy

[monk.e.boy]

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

 

monk.e.boy

[Anidazen]

Thanks monk.e.boy - that's very helpful. Good man.