# How to correlate the timestamps of 2 systems?

Whenever I've done (simple) correlation in the past, I've always had 2 sets of data that had "connected" axes:

Time of Day  |  Am I Hungry?

================================
7 AM      |     No
8 AM      |     Yes
9 AM      |     Yes
...
11 PM     |     Yes
12 AM     |     No


Now it's easy to see: was I hungry at 8 AM? Yes. Obviously these two data sets will not be correlated, because my hunger waxes and waynes throughout the day (I don't get hungrier or less hungry as time goes on).

I now have a problem where I have 2 different software systems that are showing bizarre errors in their logs. Each log is showing its own set of bizarre errors, and I want to see how closely they are correlated.

For instance, App Log #1 produces "Fizz Errors", whereas App Log #2 produces "Buzz Errors". I want to see if there is a correlation of Fizz Errors to Buzz Errors, because I know what produces Fizz Errors and want to know if they are also causing Buzz Errors on the other system. For each Fizz/Buzz error, I have a specific timestamp (given in YYYY-MM-DD HH:MM:ss format).

However, since each axis represents timestamps given in seconds, they don't necessarily have similar plot points. For instance there might have been a Fizz Event at 2013-04-02 21:46:58, but no such Buzz Event at that time. So as opposed to the above example, where I had an "Am I Hungry" reading for every hour of the day, I don't have the same luxury here.

So I ask: how do I correlate these two sets of timestamps so I can see if they tend to crop up at the same times? Thanks in advance.

-
You say that the time of the day and when you are hungry are uncorrelated, but I would disagree since I would imagine you are hungry every day at least around lunch and dinner time. It may not be a perfect correlation, but there is one nonetheless. So, I'm not sure what you mean by correlation. – Jeremy Apr 3 '13 at 19:32
@Jeremy - very true. I guess my point is that for every time of day I have a corresponding "Am I Hungry" reading. But with my timestamps, for every Fizz Event (occurring at a specific timestamp), I don't necessarily have a corresponding Buzz Event. So I'm not sure how to correlate them, or if it's even possible to correlate them. – Adam Tannon Apr 3 '13 at 19:38

## 1 Answer

You are looking for a time correlation function of the two datasets with unknown offset. The simpleminded approach is to offset one dataset with respect to the other by a variable amount, then look for a correlation between the two. For each dataset, let "event happening" be $1$ and "event not happening" be $-1$. Then if the events were perfectly correlated, the product of the two will be constant $1$ if you find the correct offset. If you look at the data, you may well see a typical duration for an event. You can then take ($\frac 12$ of that) as your search step.

You can be more formal about this by taking the Fourier transform of the datasets and looking for a correlation. Section 13.2 of Numerical Recipes has a short discussion, but you will need chapter 12 to make sense of it. Other numerical analysis books will discuss it as well.

-
Thanks @Ross Millikan (+1) - it's starting to make sense, but I have a few followups for you: (1) how much do I offset one of the datasets by, and how do I determine this offset? And (2) I understand that 1 * 1 = 1 and 1 * -1 = -1 :-) but still not seeing how to convert each timestamp in both datasets to a 1 or -1. Any chance you could update your answer with a simple concrete example to help me "see the forest through the trees"? Thanks again! – Adam Tannon Apr 3 '13 at 20:18
@AdamTannon: 1) you have to experiment with the offset. I was suggesting that if a typical event lasts 1 minute, you try increments of 30 seconds. If you see a peak, you can then adjust the offset by small amounts around that. 2) I was suggesting you convert the event present/absent to $\pm 1$. The time stamps are left as is or converted to seconds since the start of the record. You essentially integrate the product of the event values over time. – Ross Millikan Apr 3 '13 at 20:25
Thanks again @Ross Millikan (+1 again) - I follow you completely now. Just one last quick followup: why is the search step 1/2 the value of the (variable) duration? For example 30 seconds if the events last 1 minute? Thanks again! – Adam Tannon Apr 3 '13 at 20:41
@AdamTannon: No magic, just to find a reasonable overlap. If you just have one event in each dataset that lasts 1 minute, you are guaranteed to line them up if you step in increments of 1 minute. With more noise you might need shorter steps. If you are asking a computer to do it, it shouldn't lengthen the computations much. – Ross Millikan Apr 3 '13 at 20:47