Skip to content
Advertisement

Select a record when the timestamp has passed by a certain amount of time MYSQL/PHP

I’m trying to select sessions that are younger than 6 hours old with this query like so:

"SELECT * FROM sessions WHERE members=1 AND ipRCreator != '$usn' AND tStamp < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 6 HOURS";

The problem is that the query condition is always false and never manages to find the sessions even when a record actually has a timeStamp of a few seconds ago.

obviously I am very sure that the problem is in the condition of the:

tStamp < DATE_SUB (CURRENT_TIMESTAMP, INTERVAL 6 HOURS)


I insert the data records in the table like this:
 $timestamp = date("Y-m-d H:i:s");
 $mysqli->query("INSERT INTO sessions (sessionId, members, ipRCreator, tStamp) VALUES('$sId', 1, '$usn', '$timestamp');") ;

I thought the problem was the formatting of the date but I don’t think since the insert works well and the date is correctly inserted in the DB.

This is the sql structure of the table:

`sessionId` text NOT NULL,
  `members` int NOT NULL,
  `ipRCreator` text NOT NULL,
  `gender` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `age` int NOT NULL,
  `tStamp` timestamp NOT NULL,
  `id` int NOT NULL

Where could the problem be? Why does the condition never come true?

Sample record: enter image description here

So as in this case, I’m trying to fetch this record since the timeStamp is less than 6 hours old with the query I showed; the problem is that the return query is null, it is as if there were no records that have a timeStamp younger than 6 hours (even if it is older it does not work)

Advertisement

Answer

To solve this problem I used this query:

SELECT * FROM sessions WHERE members=1 AND ipRCreator != '$usn' AND TIMEDIFF(CURRENT_TIMESTAMP, tStamp) < '06:00:00'
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement