Wrong interpretation of a UNIX timestamp by DB Table Selector

[blck77]

I access a SQLite database using the SQLite Connector. The setup looks like this:

Everything works as expected except for a UNIX timestamp included in one of the tables.

When I fetch the first 100 values of the Table Selector Node, the values in the Timestamp column are entirely different from the original values.

Examples

Original value |||||||| Value in DB data selector

1700776930470 |||||||| -30118746
1700776958389 |||||||| -30090827
1700776984467 |||||||| -30064749
1700776990824 ||||||||| -30058392
::::::: ::::::
1700777094392 ||||||| -29954824
1700816719803 ||||||| 9670587
1700816740845 ||||||| 9691629
1700816757548 ||||||| 9708332

I have no idea why I have negative values in the DB data selector or why it changes to positive values at a certain point.
The data type is Number (integer).
Maybe the original Number has too many digits in the first place, but I do not receive any error messages.
Please advise!

regards

michael

[mlauber71]

@blck77 welcome to the KNIME forum. You should check if the values on the SQLite are BIGINT and the UNIX Timestamps seem to be MILISECONDS. Maybe check both.

SQLite - Long Integers (BIGINT) and UNIX Timestamps (MILISECONDS) – mlauber71

[blck77]

I know the timestamp is in milliseconds; sorry, I forgot that point to mention.

Thanks for the advice with the BIGINT; that was the reason. However, I had to do the mapping right at the beginning in the SQLite Connector to get it working.

Now, everything works as it should.

Thanks for the support!

[mlauber71]

@blck77 glad to hear, maybe you can mark my post as a solution

[blck77]

One can find more details about how SQLite stores UNIX time stamps here:

SQlite: Column format for unix timestamp; Integer types - Stack Overflow

The point is that an integer in a SQLite database may have up to 64 bits.