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.
Hengfei
Tue, 03 Oct 2023 13:55:20 UTCwhat is your query? and actually, you can override the `_timestamp` by providing the value.
Chris
Tue, 03 Oct 2023 13:57:55 UTCi'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
Hengfei
Tue, 03 Oct 2023 14:04:28 UTCfor 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)
Chris
Tue, 03 Oct 2023 14:07:02 UTCi 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...
Chris
Tue, 03 Oct 2023 14:22:44 UTCnope, 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??
Hengfei
Tue, 03 Oct 2023 14:24:37 UTCyou time format maybe or not, you can try these functions.
Ashish
Tue, 03 Oct 2023 14:56:21 UTCHi Chris
Ashish
Tue, 03 Oct 2023 14:56:34 UTCcan you try this vrl function on ingest
Ashish
Tue, 03 Oct 2023 14:56:35 UTCparsed_timestamp = parse_timestamp!(.ingest, “%+“) ingestTS = to_unix_timestamp(parsed_timestamp) now = now() nowTS = to_unix_timestamp(now) .diff =nowTS-ingestTS .
Ashish
Tue, 03 Oct 2023 14:57:04 UTCit will calculate difference in seconds of you ingest field & current ts
Chris
Tue, 03 Oct 2023 14:57:36 UTCwe 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
Ashish
Tue, 03 Oct 2023 14:58:04 UTCgot it
Ashish
Tue, 03 Oct 2023 15:15:03 UTCA query like this should help `SELECT (extract(epoch FROM CAST(ingest AS TIMESTAMP)) - _timestamp/1000000) as diff_seconds ,* FROM \"stream_name\"`
Ashish
Tue, 03 Oct 2023 15:15:21 UTCHowever UI doesnt allow fireing this query
Ashish
Tue, 03 Oct 2023 15:15:26 UTCwe will get UI fixed to allow such query
Chris
Tue, 03 Oct 2023 15:16:20 UTCyeah.... just exploring if i can create a vrl function which just does the to_unix_timestamp() and then call that from sql?
Ashish
Tue, 03 Oct 2023 15:16:53 UTC```parsed_timestamp = parse_timestamp!(.ingest, "%+") .ingestTS = to_unix_timestamp(parsed_timestamp)```
Ashish
Tue, 03 Oct 2023 15:16:59 UTCtry this
Chris
Tue, 03 Oct 2023 15:19:52 UTCthat 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)
Ashish
Tue, 03 Oct 2023 15:21:35 UTCIf 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 .```
Ashish
Tue, 03 Oct 2023 15:21:47 UTCyou dont need to calculate it using sql
Chris
Tue, 03 Oct 2023 15:22:01 UTCbut the data is already in oo !
Ashish
Tue, 03 Oct 2023 15:22:18 UTCyes then we better do it using sql
Ashish
Tue, 03 Oct 2023 15:22:43 UTCbecause at query time vrl will be less performant as compared to sql
Ashish
Tue, 03 Oct 2023 15:22:55 UTCany way vrl will also work at row level
Chris
Tue, 03 Oct 2023 15:23:05 UTCunderstood.... this is a one-time diagnostic query
OpenObserve
Indexed 419 threads
OpenObserve is an open-source, petabyte-scale observability platform for the cloud native realm, offering a 10x cost reduction and 140x less storage use compared to competitors like Elasticsearch or Splunk. Built in Rust for exceptional performance, it offers comprehensive features like logs, metrics, traces, dashboards, and more | Knowledge Base powered by Struct.AI
View in App
Chris
Tue, 03 Oct 2023 13:45:34 UTCi 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?