How to get date difference between two columns

Help
1

My start and finish columns are in the format ‘Yyyy-mm-dd HH:mm:ss.ms’ I want the difference between the two in HH:mm:ss.ms format. How do I go about this?

My query looks like this:

Select *,convert(time,   Dateadd(s,
    Datediff(s,
      A.Finish,A.Start),
       Cast('1900-01-01 00:00:00.000000' as    datetime2)))as dif from(select *,dateadd(s,convert(int,left(start,10)),'1970-01-01') as Start,convert(int,left(finish,10)),'1970-01-01') as Finish from tableB)A order by dif asc

I’ve converted unix time stamps to standard format in inner query. When I run this the start date and start time appear as ‘2019-12-11 15:45:20.000’ and ‘2019-12-12 15:45:17.000’ but my dif appears as ‘00:00:03’

2

Try TIMESTAMPDIFF(microsecond, time1, time2) and then worry about the formatting. DATEDIFF only works for dates, not the time parts.

singlestore> set @ts1 = now(6);
Query OK, 0 rows affected (0.01 sec)

singlestore> set @ts2 = now(6);
Query OK, 0 rows affected (0.00 sec)

singlestore> select timestampdiff(microsecond, @ts1, @ts2);
+----------------------------------------+
| timestampdiff(microsecond, @ts1, @ts2) |
+----------------------------------------+
|                               12368012 |
+----------------------------------------+
1 row in set (0.00 sec)

singlestore> select timestampdiff(microsecond, @ts1, @ts2)/1000000;
+------------------------------------------------+
| timestampdiff(microsecond, @ts1, @ts2)/1000000 |
+------------------------------------------------+
|                                        12.3680 |
+------------------------------------------------+