DB Connect 3.1.1 - Unable to ingest SQLite data when using a rising column and a column as a timestamp field.

[franzferd]

So I am trying to ingest data from a SQLite database using DB Connect 3.1.1. I would like to use a rising input. When I choose my own timestamp column, the data will not ingest. When I use the system timestamp, the data will ingest.

I have narrowed my test down to two fields, and am still unable to ingest. Below is my statement:

SELECT id, date
FROM results

WHERE id > ?
ORDER BY id ASC

The "id" column is my rising column.
The "date" is the column I would like to use as my timestamp.
The "date" column data is in epoch format.
I have tried converting epoch to standard time, and am having the same result.
DB Connect recognizes both columns as integers.
My DB Connect is on a heavy forwarder and the data is forwarding other SQLite data (when a custom timestamp is not used).

Any thoughts?

[franzferd · Accepted solution]

I figured out the problem.

When using a custom timestamp, I was unable to use the field as epoch. I ended up with a select statement that used strftime to change the epoch time to a more legible format:

SELECT id,
    strftime('%m/%d/%Y %H:%M:%S', datetime(date, 'unixepoch'), "localtime") AS timestamp
FROM results
WHERE id > ?
ORDER BY id ASC

I then used this Datetime format: MM/dd/yyyy HH:mm:ss

From here, my SQLite data is propagating correctly and using the date field I want as the _time.