Troubleshooting SQL Query for Timestamp Difference

TLDR Chris had issues creating an SQL query to locate logs where timestamps deviate by 20 seconds. Hengfei and Ashish suggested different methods for the conversion and comparison of timestamps.

Photo of Chris
Chris
Tue, 03 Oct 2023 13:45:34 UTC

i have a stream with the normal _timestamp (Int64), and a field "ingest" set to e.g '2023-10-03T13:41:22.025331626Z' (UTF8). I am trying (and failing) to construct a sql query that locates logs where _timestamp and ingest differ by more than say 20 seconds.... what's the right way to do this?

Photo of Hengfei
Hengfei
Tue, 03 Oct 2023 13:55:20 UTC

what is your query? and actually, you can override the `_timestamp` by providing the value.

Photo of Chris
Chris
Tue, 03 Oct 2023 13:57:55 UTC

i've been trying all sorts of things! just converting "ingest" to an Int64 in microseconds would be ideal.... not trying to override anything, just to identify records that have been held up in transit to us, or where the originating device has a bad time-of-day clock

Photo of Hengfei
Hengfei
Tue, 03 Oct 2023 14:04:28 UTC

for query, nothing special. you can simply do it like this: ```where ingest >='2023-10-03T13:41:22.025331626Z' and ingest < '2023-10-03T13:41:22.025331626Z' ``` and then select a time_range (for _timestamp)

Photo of Chris
Chris
Tue, 03 Oct 2023 14:07:02 UTC

i would have to run so many queries like that though.,.. i need to look back over the last 2 weeks to find any records where those two fields differ too much, which requires comparing one to the other... have been trying to convert the string format to the Int64 format for comparison but failing.... maybe will try converting _timestamp to a string and comparing strings instead...

Photo of Chris
Chris
Tue, 03 Oct 2023 14:22:44 UTC

nope, comparing strings is just too hard to find the extent of the difference.... so back to how to convert a string representation of a timestamp to an epoch value??

Photo of Hengfei
Hengfei
Tue, 03 Oct 2023 14:24:37 UTC

you time format maybe or not, you can try these functions.

Photo of Ashish
Ashish
Tue, 03 Oct 2023 14:56:21 UTC

Hi Chris

Photo of Ashish
Ashish
Tue, 03 Oct 2023 14:56:34 UTC

can you try this vrl function on ingest

Photo of Ashish
Ashish
Tue, 03 Oct 2023 14:56:35 UTC

parsed_timestamp = parse_timestamp!(.ingest, “%+“) ingestTS = to_unix_timestamp(parsed_timestamp) now = now() nowTS = to_unix_timestamp(now) .diff =nowTS-ingestTS .

Photo of Ashish
Ashish
Tue, 03 Oct 2023 14:57:04 UTC

it will calculate difference in seconds of you ingest field & current ts

Photo of Chris
Chris
Tue, 03 Oct 2023 14:57:36 UTC

we do all our parsing in vector prior to ingest into openobserve.... this data is already in oo though and i need to be able to do the above query

Photo of Ashish
Ashish
Tue, 03 Oct 2023 14:58:04 UTC

got it

Photo of Ashish
Ashish
Tue, 03 Oct 2023 15:15:03 UTC

A query like this should help `SELECT (extract(epoch FROM CAST(ingest AS TIMESTAMP)) - _timestamp/1000000) as diff_seconds ,* FROM \"stream_name\"`

Photo of Ashish
Ashish
Tue, 03 Oct 2023 15:15:21 UTC

However UI doesnt allow fireing this query

Photo of Ashish
Ashish
Tue, 03 Oct 2023 15:15:26 UTC

we will get UI fixed to allow such query

Photo of Chris
Chris
Tue, 03 Oct 2023 15:16:20 UTC

yeah.... just exploring if i can create a vrl function which just does the to_unix_timestamp() and then call that from sql?

Photo of Ashish
Ashish
Tue, 03 Oct 2023 15:16:53 UTC

```parsed_timestamp = parse_timestamp!(.ingest, "%+") .ingestTS = to_unix_timestamp(parsed_timestamp)```

Photo of Ashish
Ashish
Tue, 03 Oct 2023 15:16:59 UTC

try this

Photo of Chris
Chris
Tue, 03 Oct 2023 15:19:52 UTC

that looks like one that operates on the entire row... how do i call that from the sql? was going to try creating a function that took a string and returned an int64 and just use that as a function but struggling to find out how.... (remember it is too late to associate the vrl function with ingest)

Photo of Ashish
Ashish
Tue, 03 Oct 2023 15:21:35 UTC

If you want to use vrl ingest function , use this code : ```parsed_timestamp = parse_timestamp!(.ingest, "%+") ingestTS = to_unix_timestamp(parsed_timestamp) now = now() nowTS = to_unix_timestamp(now) .diff =nowTS-ingestTS .```

Photo of Ashish
Ashish
Tue, 03 Oct 2023 15:21:47 UTC

you dont need to calculate it using sql

Photo of Chris
Chris
Tue, 03 Oct 2023 15:22:01 UTC

but the data is already in oo !

Photo of Ashish
Ashish
Tue, 03 Oct 2023 15:22:18 UTC

yes then we better do it using sql

Photo of Ashish
Ashish
Tue, 03 Oct 2023 15:22:43 UTC

because at query time vrl will be less performant as compared to sql

Photo of Ashish
Ashish
Tue, 03 Oct 2023 15:22:55 UTC

any way vrl will also work at row level

Photo of Chris
Chris
Tue, 03 Oct 2023 15:23:05 UTC

understood.... this is a one-time diagnostic query