mysqlsqltimestampdiff

Timestamdiff sql doesn't work

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

Solution

  • 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.