How to compare two timestamps in MySql?

Question

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)

What type are your 'timestamp' columns? I ask because if they are TIMESTAMP or DATETIME, the `>` operator should work just fine. — Marcus Adams, Apr 30 '12 at 13:28

Yago Riveiro · Answer 1 · 2012-04-30T15:00:26.757

8

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)

edited Apr 30 '12 at 15:00

answered Apr 30 '12 at 13:47

Yago Riveiro

727
13
28

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 Apr 30 '12 at 15:09
check the table in this image, if u want. http://filaty.com/i/1204/11946/Untitled.jpg – Aymen Apr 30 '12 at 15:18
1

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 – Yago Riveiro Apr 30 '12 at 15:51
1

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 Apr 30 '12 at 16:23
note when I remove every think related to timestamp, the updating will work just fine! – Aymen Apr 30 '12 at 16:32
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) – Yago Riveiro Apr 30 '12 at 16:47
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? – Aymen May 01 '12 at 02:03
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](http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) – Yago Riveiro May 02 '12 at 09:19

score 2 · Answer 2 · answered Apr 30 '12 at 13:31

2

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

answered Apr 30 '12 at 13:31

Ashwini Chaudhary

244,495
58
464
504

Aymen · Accepted Answer · 2012-05-01T04:39:55.557

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)

edited May 01 '12 at 04:39

answered May 01 '12 at 04:22

Aymen

83
1
2
12

The God who creates and inspires us. – Aymen Feb 12 '15 at 16:12

How to compare two timestamps in MySql?

3 Answers3