Calculation of duration between two timestamps in different rows

Question

In my sample data table below, all datatypes are varchar(255).

|      event      |            timestamp           |       traceid       |
| accepted        | 2016-01-16T09:35:48.653418931Z | AIP1452936948-44467 |
| published_event | 2016-01-16T09:35:48.659767348Z | AIP1452936948-44467 |

Here is the expected output:

|       traceid       | duration |
| AIP1452936948-44467 |   108    |

And the code I would like reviewed...

select traceid, bUnixMS - aUnixMS as duration
from (
select 
a.traceid, 
CONVERT(UNIX_TIMESTAMP(STR_TO_DATE(a.mydatetime,'%Y-%m-%d %H:%i:%s')), SIGNED) as aUnix, 
CONVERT(UNIX_TIMESTAMP(STR_TO_DATE(b.mydatetime,'%Y-%m-%d %H:%i:%s')), SIGNED) as bUnix, 
(CONVERT(UNIX_TIMESTAMP(STR_TO_DATE(a.mydatetime,'%Y-%m-%d %H:%i:%s')), SIGNED) + (CONVERT(LEFT(a.ms, 3), SIGNED) / 1000)) * 1000 as aUnixMS,
(CONVERT(UNIX_TIMESTAMP(STR_TO_DATE(b.mydatetime,'%Y-%m-%d %H:%i:%s')), SIGNED) + (CONVERT(LEFT(b.ms, 3), SIGNED) / 1000)) * 1000 as bUnixMS,
CONVERT(LEFT(a.ms, 3), SIGNED) as aMS,
CONVERT(LEFT(b.ms, 3), SIGNED) as bMS

from (
   select
      event,
      traceid,
      CONCAT(SUBSTRING_INDEX(timestamp, 'T', 1), ' ', SUBSTRING_INDEX(SUBSTRING_INDEX(timestamp, 'T', -1), '.', 1)) as mydatetime,  
      SUBSTRING_INDEX(SUBSTRING_INDEX(timestamp, 'T', -1), '.', -1) as ms
   from applogs
   where event = "accepted"
) a

join (
   select
      event,
      traceid,
      CONCAT(SUBSTRING_INDEX(timestamp, 'T', 1), ' ', SUBSTRING_INDEX(SUBSTRING_INDEX(timestamp, 'T', -1), '.', 1)) as mydatetime,
      SUBSTRING_INDEX(SUBSTRING_INDEX(timestamp, 'T', -1), '.', -1) as ms
   from applogs
   where event = "published_event"
) b on a.traceid = b.traceid
) as x

Answer 1

You are not giving enough details (such as specific table structure, index, volume, types of queries, etc.) nor your constraints.

Nor do you explain how you come with value 108.

So my answer supposes you have only two timestamps per traceid and that the events are ordered logically in the same order as the timestamps.

SELECT
TIMESTAMPDIFF(MICROSECOND,
    STR_TO_DATE(MIN(`timestamp`), '%Y-%m-%dT%H:%i:%S.%fZ'),
    STR_TO_DATE(MAX(`timestamp`), '%Y-%m-%dT%H:%i:%S.%fZ'))
FROM applogs
GROUP BY traceid 

This gives you 6319 which is a number of microseconds between the two. No idea on how you come to 108 from there. Note that it works only if you change your values to have only 6 digits after the dot, as the parser searches for microseconds. See here and the documentation.

If you really need your sub microseconds format you will need to split the string in two, first part being what is used above and last part being the last 3 digits as fractions of microseconds. And then add all of it.

This begs to ask why you are storing such a timestamp in a text field, this is not a good design. That would be the main point of the review: change your model and do not store timestamps in text fields, use the appropriate types of your RDBMS.

PS: since timestamp is the name of a type I do not recommend naming your column with it. Also, on the modeling part, your structure seems to reflect a key-value pattern, which is not always a good idea in RDBMS.