Geek asks and answers

How to compare two timestamps in MySql?

The timestamp field is a column DATETIMEin format 2012-03-19 00:23:14. How can I compare two rows of timestamps and find a larger one? The following query I am using does not work.

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)
+3
source share
3 answers

Thank God, now the question is in order.

There is no SET syntax after the table name, and GROUP BY syntax should be excluded 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)
+1
source

You can try using an if statement to check if the timestamp A is longer than the timestamp.

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

More articles:


All Articles