0
\$\begingroup\$

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
\$\endgroup\$
3
  • \$\begingroup\$ Why not JOIN the table to itself on traceid with t1.event = 'accepted' AND t2.event = 'published' ? \$\endgroup\$
    – user1149
    Jan 17, 2016 at 15:40
  • \$\begingroup\$ When refactored accordingly to your question, it seems I lose all performance and the query executes seemingly "forever". There is ~349k rows so I can't off the top of my head explain why this would happen. \$\endgroup\$ Jan 18, 2016 at 0:14
  • \$\begingroup\$ Do you have indexes on traceid and event? \$\endgroup\$
    – user1149
    Jan 18, 2016 at 0:37

1 Answer 1

2
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.