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.
what is your query? and actually, you can override the `_timestamp` by providing the value.
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
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)
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...
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??
you time format maybe or not, you can try these functions.
Hi Chris
can you try this vrl function on ingest
parsed_timestamp = parse_timestamp!(.ingest, “%+“) ingestTS = to_unix_timestamp(parsed_timestamp) now = now() nowTS = to_unix_timestamp(now) .diff =nowTS-ingestTS .
it will calculate difference in seconds of you ingest field & current ts
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
got it
A query like this should help `SELECT (extract(epoch FROM CAST(ingest AS TIMESTAMP)) - _timestamp/1000000) as diff_seconds ,* FROM \"stream_name\"`
However UI doesnt allow fireing this query
we will get UI fixed to allow such query
yeah.... just exploring if i can create a vrl function which just does the to_unix_timestamp() and then call that from sql?
```parsed_timestamp = parse_timestamp!(.ingest, "%+") .ingestTS = to_unix_timestamp(parsed_timestamp)```
try this
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)
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 .```
you dont need to calculate it using sql
but the data is already in oo !
yes then we better do it using sql
because at query time vrl will be less performant as compared to sql
any way vrl will also work at row level
understood.... this is a one-time diagnostic query
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?