[SOLVED] Using the TIMESTAMP field type

[jordanwb]

I have a table like so:

 

CREATE TABLE `jscm_login_attempts` (
  `user_id` int(11) NOT NULL,
  `user_attempts` tinyint(2) NOT NULL,
  `user_locked_out` tinyint(1) NOT NULL,
  `user_locked_out_till` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

When a user tries to login, a row will be inserted with the user's id, attempts made, false, and the current timestamp. If and when the "user_attempts" field reaches a preset number (say 3), "user_locked_out" will be set to true. That I know how to do but what I want to do is change the timestamp so its X number of minutes in the future (say 15). How would I do that?

[xtopolis]

Here is an example of selecting the current time, and the time + 15 minutes.

SELECT CURRENT_TIMESTAMP(),DATE_ADD(CURRENT_TIMESTAMP(),INTERVAL + 15 MINUTE);

 

You could also do this in php using functions like date and strtotime.

[jordanwb]

You could also do this in php using functions like date and strtotime.

 

Oh yeah, I forgot about that.

 

Thanks.

[fenway]

That syntax before was incorrect... try

 

SELECT CURRENT_TIMESTAMP() + INTERVAL 15 MINUTE

[jordanwb]

That syntax before was incorrect... try

 

SELECT CURRENT_TIMESTAMP() + INTERVAL 15 MINUTE

 

So I'd put that (minus the SELECT) in the UPDATE query, right?

[jordanwb]

I wish I could edit my posts. What if the Interval was >= 60 minutes? Would MySQL be smart enough to know its one hour?

[fenway]

I don't know what you mean by that... an interval can't be an inequality... but you can use an inequailty in your comparison.  and there is a "1 HOUR" option too.

[jordanwb]

I don't know what you mean by that... an interval can't be an inequality... but you can use an inequailty in your comparison.  and there is a "1 HOUR" option too.

 

Don't worry about it. I got it all figured out.