Mysql update database where date == -15 from current

Question

i've been looking around for a way to do this; I found one tutorial that suggested using the dateadd (which i found out is date_add in mysql) function... but I've had no luck with that.

I need to have something that updates the database every 15 minutes or so via a Cronjob... The cron job is already set up (Php file), but i cannot get the DB to update every fifteen minutes.

anyway, this is the code i've got currently--i was really stressing out trying to figure out how to get it to work... so i know what i'm doing doesn't actually work.

 UPDATE starinformation SET starOwner = nextOwner, death = 'off', wifi = 'on' WHERE underSiege = 1 AND siegeStart = (current_timestamp)-15

I've also tried doing this

$date=date('-15 minutes'); $sql = "UPDATE starinformation SET starOwner = nextOwner, death = 'off', wifi = 'on' WHERE underSiege = 1 AND siegeStart = ".$date

Answer 1

You can try this.-

<?php
date('Y-m-d H:i:s', strtotime('-15 minutes'));
?>

Answer 2

Please take a look here, how to deal with times using MySQL

Add 2 hours to current time in MySQL?

Answer 3

Do you mean something like

 DATE_ADD(NOW(), INTERVAL -15 MINUTE) 

Answer 4

If siegeStart of datetime or timestamp data type then

Use

siegeStart = ( current_timestamp - interval 15 minute )

in the update statement.

But the update will only work if the seconds part of the siegeStart also matches.

If you can omit seconds part of your date, then

Use

date_format( siegeStart, '%Y-%m-%d %H:%i' ) = 
date_format( current_timestamp - interval 15 minute, '%Y-%m-%d %H:%i' )

Refer To:

• INTERVAL clause: INTERVAL expr unit
• DATE_FORMAT(date,format)

Answer 5

The current time minus 15 minutes can be represented by the two functions CURTIME and SUBTIME:

SUBTIME(CURTIME(),'00:15:00.0');

So, based on your posted statement, the modified version is:

UPDATE starinformation SET starOwner = nextOwner, death = 'off', wifi = 'on' WHERE underSiege = 1 AND siegeStart =SUBTIME(CURTIME(),'00:15:00.0')

Answer 6

This might be what you have been looking for:

UPDATE starinformation SET starOwner = nextOwner, death = 'off', wifi = 'on' WHERE underSiege = 1 AND siegeStart = DATE_SUB(NOW(), INTERVAL 15 MINUTE)

Here DATE_SUB subtracts the given date NOW() with given INTERVAL.