mysqlsqldateselectsequelpro

Need to find time difference in hours between 2 timestamps for multiple rows in a result sql table

I had 3 tables on which I performed certain operations and was able to arrive at the following table Table1

OID     SID     OID_create          SID_create
817953  951261  2020-05-01 00:00:16 2020-05-01 10:15:32
817953  951407  2020-05-01 00:00:16 2020-05-01 10:30:13
817954  952564  2020-05-01 00:00:16 2020-05-01 12:01:33
817954  954544  2020-05-01 00:00:16 2020-05-01 17:45:18
817955  956318  2020-05-01 00:00:16 2020-05-02 07:00:59
817956  959146  2020-05-01 00:00:16 2020-05-02 10:46:26

To find the difference of SID_create and OID_create I used the following query

select OID, SID, OID_create, SID_create, (SID_create - OID_create) as Difference
from Table1

which resulted in a table like this 

OID     SID     OID_create          SID_create          Difference
817953  951261  2020-05-01 00:00:16 2020-05-01 10:15:32 101516
817953  951407  2020-05-01 00:00:16 2020-05-01 10:30:13 102997
817954  952564  2020-05-01 00:00:16 2020-05-01 12:01:33 120117
817954  954544  2020-05-01 00:00:16 2020-05-01 17:45:18 174502
817955  956318  2020-05-01 00:00:16 2020-05-02 07:00:59 1070043
817956  959146  2020-05-01 00:00:16 2020-05-02 10:46:26 1104610

Please help understand if the Difference value is in seconds or minutes or hours or something else. I would prefer if I get the result in hours directly, How to achieve it

Solution

  • Directly substracting dates in MySQL is not a good idea. Although, this does not raise errors, you are likely to get unexpected results. As I understand, each date is turned to a numeric representation (eg '2020-05-01 00:00:16' would become 20200501000016), which are then subtracted.

    In MySQL, a simple way to get the difference between to date expressions in a given unit is to use date function timestampdiff():

    timestampdiff(hour, OID_create, SID_create)