Timestamdiff sql doesn't work

Question

I want to make a sql query that shows me those fields and the difference between the time stored in the database and the current clock time is greater than 1 hour (60 minutes). This is my query but something is not working well, the query shows all field in the database table. I want to show only those that are more than one hour from the datetime field time

This is my query:

SELECT tipo, nombre, descripcion, lugar, duracion, formatoFecha
FROM servicio
WHERE ABS(TIMESTAMPDIFF(MINUTE, formatoFecha, NOW())) > 60
ORDER BY TIMESTAMPDIFF(MINUTE, formatoFecha, NOW()) DESC";

formato fecha is a datetime field as this:

2017-04-10 17:30:00

Sorry, i will explain it better, when i execute the query i obtains the same result that i do this query: SELECT tipo, nombre, descripcion, lugar, duracion, formatoFecha FROM servicio, for this reason i say "is not working", I will edit the post — wiki, Apr 11 '17 at 11:14

score 2 · Accepted Answer · answered Apr 11 '17 at 11:13

I would recommend writing the query as:

SELECT tipo, nombre, descripcion, lugar, duracion, formatoFecha
FROM servicio
WHERE formatoFecho > NOW() + INTERVAL 1 HOUR OR
      formatoFecho < NOW() + INTERVAL 1 HOUR
ORDER BY formatoFecho DESC;

Note: If you only want future times, remove the second condition (and vice versa).

When comparing columns, you should try to avoid function calls on the columns (if you can). This allows the optimizer to use indexes.

Thanks so much for your answer, it's a great solution , it works very well — wiki, Apr 11 '17 at 11:22

Timestamdiff sql doesn't work

1 Answers1