How to compare two timestamps in MySql?

mysql timestamp sql-update

22,025

Solution 1

Thanks God, the query is OK now.

The missing was SET syntax after table name, and also GROUP BY syntax should be omitted from EXISTS.

UPDATE report 1 SET status = 'time is larger' WHERE EXISTS 
  (SELECT ip_src, layer4_sport, timestamp FROM  
  (SELECT ip_src, layer4_sport, timestamp from report 1) AS tmpb  
   WHERE  report 1.layer4_sport = tmpb.layer4_sport 
   AND report 1.ip_src = tmpb.ip_src  
   AND  report 1.timestamp > tmpb.timestamp)

Solution 2

You can try use an if statement to validate if the A timestamp is greater than B timestamp.

select if(UNIX_TIMESTAMP('2009-02-01 00:00:00') > UNIX_TIMESTAMP('2009-01-01 00:00:00'), true, false)

Solution 3

try the UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date) functions to compare two timestamps.

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp

22,025

Author by

Aymen

Updated on May 01, 2020

Comments

Aymen almost 4 years

The timestamp field is a DATETIME column in a format like 2012-03-19 00:23:14. How can I compare two rows of timestamps and find the larger one? The query below that I am using isn't working.

UPDATE report 1 status = 'time is larger' WHERE EXISTS 
  (SELECT ip_src, layer4_sport, timestamp FROM  
      (SELECT ip_src, layer4_sport, timestamp from report 1) AS tmpb  
   WHERE  report 1.layer4_sport = tmpb.layer4_sport 
   AND report 1.ip_src = tmpb.ip_src  
   AND  report 1.timestamp > tmpb.timestamp 
   GROUP BY ip_src, layer4_sport,  timestamp HAVING COUNT(*) = 2)

Aymen almost 12 years

I have changed my query, but still have the same problem, status isn't changed UPDATE report 1 status = 'time is larger' WHERE EXISTS (SELECT ip_src, layer4_sport, timestamp FROM (SELECT ip_src, layer4_sport, timestamp from report 1) AS tmpb WHERE report 1.layer4_sport = tmpb.layer4_sport AND report 1.ip_src = tmpb.ip_src AND UNIX_TIMESTAMP('report 1.timestamp') > UNIX_TIMESTAMP('tmpb.timestamp') GROUP BY ip_src, layer4_sport HAVING COUNT(*) = 2)
Aymen almost 12 years

check the table in this image, if u want. filaty.com/i/1204/11946/Untitled.jpg
Yago Riveiro almost 12 years

I think the problem is the EXISTS statement, try to use a IN statement. In the subquery now you only need return the identifier of row that will be update
Aymen almost 12 years

How can I use IN statement?!! The condition in subquery should be achieved and as I think it is only can be with EXISTS statement. Anyway, if you can help me to change the query and make it running without EXISTS, please write it down for me. Thanks
Aymen almost 12 years

note when I remove every think related to timestamp, the updating will work just fine!
Yago Riveiro almost 12 years

Try this: UPDATE report 1 status = 'time is larger' WHERE cid in ( SELECT cid FROM report JOIN report as _tmp ON(report.layer4_sport = _tmp.layer4_sport AND report.ip_src = _tmp.ip_src) WHERE if(UNIX_TIMESTAMP(report.date) > UNIX_TIMESTAMP(_tmp.date), 1, 0) = 1)
Aymen almost 12 years

After changing to this UPDATE report.cid status = 'time is larger' WHERE report.cid in ( SELECT report.cid FROM report JOIN report as _tmp ON(report.layer4_sport = _tmp.layer4_sport AND report.ip_src = _tmp.ip_src) WHERE if(UNIX_TIMESTAMP(report.date) > UNIX_TIMESTAMP(_tmp.date), 1, 0) = 1). It says "You can't specify target table 'report' for update in FROM clause". Any help please?
Yago Riveiro almost 12 years

To perform an update in same table, first is necessary create a temporary table. UPDATE report.cid status = 'time is larger' WHERE report.cid in ((from (SELECT report.cid FROM report JOIN report as _tmp ON(report.layer4_sport = _tmp.layer4_sport AND report.ip_src = _tmp.ip_src) WHERE if(UNIX_TIMESTAMP(report.date) > UNIX_TIMESTAMP(_tmp.date), 1, 0) = 1) as _tmp). For more information here
Aymen about 9 years

The God who creates and inspires us.