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

Question

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:

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)

A timestamp *of a few seconds ago* would certainly not be returned by the query which returns rows with a timestamp older than 6 hours ago. — forpas, Dec 27 '21 at 22:04
@forpas I was wrong in the question, I meant the sessions younger than 6 hours not the other way around. I corrected — Conta, Dec 27 '21 at 22:10
@forpas in both cases (whether it is> or when it is <) the query never returns anything, so I think it is not a logical problem — Conta, Dec 27 '21 at 22:13
I don't know if there is another problem because you did not post any sample data. What I know is that there is a logical problem with your code. — forpas, Dec 27 '21 at 22:14
You have `WHERE members=1` in your code. How do you expect to get a row with `members=2`? — forpas, Dec 27 '21 at 22:26
@forpas it was just an example record, the same thing happens when members is at 1 — Conta, Dec 27 '21 at 22:30
Then your problem is not reproducible. Can you reproduce it with sample data in a fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0? — forpas, Dec 27 '21 at 22:31
(Possible) side note: Do not use string interpolation or concatenation to get values into SQL queries. That's error prone and might make your program vulnerable to SQL injection attacks. Use parameterized queries. See ["How to include a PHP variable inside a MySQL statement"](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) and ["How can I prevent SQL injection in PHP?"](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — sticky bit, Dec 27 '21 at 23:03

score 0 · Accepted Answer · answered Dec 28 '21 at 18:17

0

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'

answered Dec 28 '21 at 18:17

Conta

236
1
6
21

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

1 Answers1