New to Knime, I went through some tutorials and got a basic understanding of the tool.
Right now, I setup a small flow to read data from an SQLite database but face an issue with the date/time format.
The database contains a “State” table with a column named last_changed. According to DB Browser, the type of this column is DATETIME, and it contains the date and time of the event.
I used the SQLite connector in Knime which loads the data successfully. Then I use a DB Query reader to only acquire a subset of data. It works well and I can see the data in the data table.
The problem is that the last_changed column only contains the date. The time is gone.
Looking into the SQLite connector Input Type Mapping, I only see DATE, no DATETIME.
From testing, I can see that the SQLite Connector sees the last_changed column as a DATE type, not a TIMESTAMP or other format.
How can I go around this problem? The timestamp is essential to the analytics I want to perform.
I do not fully understand your question. If you want to upload a timestamp to an SQLite database you might want to check the Mappings settings in the DB Writer node.
Thanks for the reply, I will try to clarify my issue. It is linked to reading the database in Knime. The main problem is that Knime converts DATETIME type into DATE type, thus I loose the time value.
Here is my simple flow:
When I look at the table from the DB Query reader, the time column only contains the date
Why is Knime then only giving me the date? It does not seem to recognise DATETIME and I do not see that in the Input Mapping table.
Can I do something in the input mapping to change that?
I want the Knime DB Query Reader table to show me the complete date and time.
A typical way would be to convert the time into a string and later convert that in KNIME to use it.
I am still a little bit confused about the type DATETIME in your database. Could you provide us with a sample workflow that contains a SQLIte file and that kind of column and also the SQL in the DB Query Reader. This DB Query reader other than the DB query has settings for some type mapping one might be able to explore.
I am not really sure how to do the sample work flow you ask. If you don’t mind guiding me I will do it.
The SQLite file is 500Mb, so if there is a way to send only a subsection, I would gladly share it.
The SQL query in the DB reader is as follow:
SELECT * FROM “states” WHERE entity_id = ‘sensor.light_level’
I’ve looked in the type mapping. Two approach I tried without success:
DATETIME does not exist as a type - so I can’t influence that
I tried to modify DATE to do something else, but I can’t change the Mapping to any other values that the one in this picture.
@dchabauty you might have to see if you can change the Source Type to TIMESTAMP or something, becaus judging from your screenshot the variable already is a date type. So from this stage you will not be able to derive a precise TIMESTAMP from there. So you might have to deal with it in the import section somewhere (Maybe with a CAST function or a type mapping)
For an example you best would select only a few lines and columns into a separate SQLite file and share that with us if it does not contain any sensitive informations. You might also provide us with some original data that you the load into an SQLite table (but that is what my example should hav already demonstrated).
@mlauber71 - Thanks a lot, I will try to extract some data from this SQLite to a subset and share it. There are no sensitive data, just some measurements from my smart home hub.
Actually, the SQLite is filled by Home Assistant running on a Raspberry Pi 4. Home Assistant is connected to various temperature, brightness and humidity sensors and records their state in that database. I want to use Knime to analyse this data.
So I don’t really have influence on the database and the way the data is recorded, it’s all handled by Home Assistant.
The easiest thing to do would be to place the sqlite file in the subfolder /data/ of the knime workflow and reference it by the knime protocol, like in this example.
That way you have a fully functional example with all the data regardless of the full path or operating system.
@dchabauty - I set up an example how to handle the DATETIME columns from your example. It seems on the way from SQLite to KNIME these columns get converted to DATE. If you use “strftime” function you could extract the TIMESTAMP and convert that to a real Timestamp in KNIME (and bring that back to SQLite later). I have not found a way to directly bring DATETIME to TIMESTAMP in SQLite or from there to KNIME.
Thank you very much. Indeed, this behaviour is for me strange from Knime that the DATETIME type is unknown. Anyway, thanks a lot for the workaround, I will use it in my workflow. Looking forward to investigate the data now!
Probably some driver issue but not sure cause if you create new table and write timestamp from KNIME to into it, it will be read back into KNIME as expected. You can check it in example as well.
Yes you can load the DATETIME as a TEXT string and then convert it into a KNIME Datetime format. And you can also upload a KNIME Datetime into a TIMESTAMP in SQLite. But there seems to be no genuine/direct way to map/convert DATETIME in SQLite into a Dattime in KNIME. If try that it would automatically convert it into a DATE as far as I can see.
Not a big thing because you can easily make everything work but still; a more systematic approach would be to offer to convert DATETIME into KNIME Datetime directly via the type mapping …
I’m interested in this topic because of the way KNIME writes the TIMESTAMP into the database (SQLITE).
Because sqlite does have a native date time object 1 of the 3 methods described in the docs of sqlite should be used. Store it as TEXT, INTEGER or REAL.
What would be the preferred method? When looking at other code it is suggest the ISO string format would it be. KNIME stores it at an integer.
How should I store the date and time data in the table? The table is also accessed with python code. And in the future the database could be switched to mysql or other database engine.
