CODEWITHSUNDEEP
mysqlsql
shadi1989
shadi1989

Reputation: 9

sql change date time in query

I have a script that adds content in my mysql database with a date/time stamp. It's not possible to change the input date/time for me.

i want the date-time changed when i query out of my mysql database.

cell in my db: 2016-08-13 17:34:29 It needs to be: 2016-08-13 19:34:29 ( so +02:00 hour shown trought the query) I cannot UPDATE the time cause other applications depend on the data in the db.

Then i want to query only items added with an experation time between NOW and +15minutes.

Dont know if it matters but im running MariaDB SQL.

Thanks in advance!

Website coding now:

<?php
//make connection
mysql_connect('localhost','root','');

//select db
mysql_select_db('database');

$sql="SELECT * 
FROM table
WHERE `id` = 147 
ORDER BY `disappear_time` DESC";

$records=mysql_query($sql);
?>

<html>
<body>
<table width="1000" border="1" cellpadding="1" cellspacing="1">
<tr>
    <th>encounter_id</th>
    <th>disappear_time</th>
</tr>

<?php
while($item=mysql_fetch_assoc($records)){
    echo "<tr>";
    echo "<td>".$item['encounter_id']."</td>";
    echo "<td>".$item['disappear_time']."</td>";
    echo "</tr>";
}
?>
</body>
</html>

--- Already tried: SELECT * FROM table WHERE time >= NOW() AND time <= NOW() + INTERVAL 15 MINUTE

ERRORS: The SELECT is not working: Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in .....

Upvotes: 0

Views: 146

Answers (2)

shadi1989
shadi1989

Reputation: 9

Solution for my question: NOW() didnt work.

SELECT * FROM `table` 
WHERE `time` > SUBDATE( CURRENT_TIMESTAMP, INTERVAL 2 HOUR)
AND `id` = 147
ORDER BY `time` DESC";

Upvotes: 0

jussius
jussius

Reputation: 3274

To change the time:

UPDATE table
SET time = time + INTERVAL 2 HOUR

To query between now and +15 mins:

SELECT * FROM table
WHERE time >= NOW() AND time <= NOW() + INTERVAL 15 MINUTE

Add WHERE ID = 1234 if you only want to update or get rows with that id.

Upvotes: 1

Related Questions