1

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

GMB
  • 216,147
  • 25
  • 84
  • 135
sup
  • 15
  • 4
  • I can't figure out what the units of `Difference` might be. You might want to include the full query you used to arrive at these results. – Tim Biegeleisen Jun 12 '20 at 08:19
  • Thank you for the feedback, I have updated the question with the query i have used to find the difference between 2 time stamps – sup Jun 12 '20 at 08:23

1 Answers1

2

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)
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thank you, this worked for me. But it was giving the result in negative value, so I swapped the values to >>>>> timestampdiff(hour, OID_create, SID_create) – sup Jun 12 '20 at 08:29
  • 1
    @sup: I fixed my answer accordingly. I also added an explanation on what happens when you substract dates. – GMB Jun 12 '20 at 08:31
  • Thank you so much for the explanation, it clears up a lot of doubt – sup Jun 12 '20 at 08:32