how to calculate timestampdiff between different rows and columns in one table?

Question

I'm trying to calculate the time difference between end_datetime and start_datetime in different rows of table statistic and result set in pause_time.

My query like this don't executed.

update statistic
set pause_time = TIMESTAMPDIFF(hour, a.start_datetime, b.end_datetime)
from statistic a
inner join statistic b on a.id = (b.id - 1)
order by a.id asc

I receive an error:

check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM statistic a INNER JOIN statistic b ON b.id=(a.id - 1) ORDER BY a.id ASC' at line 1 QMYSQL. My table statistic:

<html> <head> <title></title> </head> <body> <div> id<span class="Apple-tab-span" style="white-space:pre"> </span>&nbsp; &nbsp;| &nbsp; &nbsp;start_datetime<span class="Apple-tab-span" style="white-space:pre"> </span>&nbsp; &nbsp; &nbsp;| &nbsp;&nbsp;end_datetime<span class="Apple-tab-span" style="white-space:pre"> </span>&nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp;pause_time</div> <p> <span style="font-size:12px;">1 <span class="Apple-tab-span" style="white-space:pre"> </span>&nbsp; &nbsp; &nbsp;| &nbsp; &nbsp;2016-10-13T23:14:05<span class="Apple-tab-span" style="white-space:pre"> </span>&nbsp;&nbsp;|&nbsp; <strong><span style="color:#f00;">&nbsp;2016-10-14T07:03:02</span>&nbsp;<span class="Apple-tab-span" style="white-space:pre"> </span></strong>|&nbsp; &nbsp; &nbsp;&nbsp;</span></p> <p> <span style="font-size:12px;">2<span class="Apple-tab-span" style="white-space:pre"> </span>&nbsp; &nbsp; &nbsp; | &nbsp; <span style="color:#f00;"><strong>&nbsp;2016-10-14T15:25:56&nbsp;</strong>&nbsp;</span>| &nbsp;&nbsp;2016-10-14T22:03:50<span class="Apple-tab-span" style="white-space:pre"> </span>&nbsp;|</span></p> <p> <span style="font-size:12px;">3 &nbsp; &nbsp; &nbsp;<span class="Apple-tab-span" style="white-space:pre"> </span>| &nbsp; &nbsp;2016-10-13T15:25:30<span class="Apple-tab-span" style="white-space:pre"> </span>&nbsp; | &nbsp;&nbsp;2016-10-13T22:10:01 &nbsp;|</span></p> <p> <span style="font-size:12px;">4 &nbsp; &nbsp; &nbsp;<span class="Apple-tab-span" style="white-space:pre"> </span>| &nbsp; &nbsp;2016-10-13T01:09:11<span class="Apple-tab-span" style="white-space:pre"> </span>&nbsp; | &nbsp;&nbsp;2016-10-13T02:59:30<span class="Apple-tab-span" style="white-space:pre"> </span>&nbsp;|</span></p> <div> &nbsp;</div>  </body> </html>

Gurwinder Singh · Accepted Answer · 2017-02-26 12:14:59Z

3

The syntax you used is SQL server's.

See this for MySQL's - https://dev.mysql.com/doc/refman/5.7/en/update.html

Try this:

update statistic a
join   statistic b on a.id = (b.id - 1)
set a.pause_time = TIMESTAMPDIFF(hour, a.start_datetime, b.end_datetime)

edited Feb 26, 2017 at 12:14

answered Feb 26, 2017 at 11:36

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

VAP Over a year ago

UPDATE statistic a JOIN statistic b ON b.id = (a.id + 1) SET b.pause_time = TIMESTAMPDIFF(SECOND, a.end_datetime, b.start_datetime)

Collectives™ on Stack Overflow

how to calculate timestampdiff between different rows and columns in one table?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related