Sugan Krishna Sugan Krishna - 1 year ago 89
MySQL Question

Filtering table in MySQL with difference between two timestamp columns

I have a table in MySQL which includes the datetime columns


How can I select only rows in which
is at least one minute greater than

Answer Source

You could use TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2) and something like

select * from MyTab T where TIMESTAMPDIFF(MINUTE,open_date,close_date) > 1

From docs


Returns datetime_expr2 − datetime_expr1, where datetime_expr1 and datetime_expr2 are date or datetime expressions. One expression may be a date and the other a datetime; a date value is treated as a datetime having the time part '00:00:00' where necessary. The unit for the result (an integer) is given by the unit argument. The legal values for unit are the same as those listed in the description of the TIMESTAMPADD() function.

mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01'); -> 3
mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01'); -> -1
mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55'); -> 128885

Note The order of the date or datetime arguments for this function is the opposite of that used with the TIMESTAMP() function when invoked with 2 arguments.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download