Sugan Krishna Sugan Krishna - 1 year ago 72
MySQL Question

filtering data from mysql with two timestamp column in that same table

i have a table in MySQL with columns as open_time and close_time and with some data with that. i need filter data using difference between open_time column and close_time column should be one minute. that means close_time should be more than one minute of open_time.

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