SQLite DATETIME to Knime format

Hi everyone,

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.

Thank you,
Dominique

@dchabauty welcome to the KNIME forum

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.

You could play around with this example and maybe share one of your own that would illustrate your problem:

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:
Screenshot 2020-11-20 at 19.48.35

When I look at the table from the DB Query reader, the time column only contains the date

But in my database, this column contains date and time (Format DATETIME)
Screenshot 2020-11-20 at 19.52.19

If I read the database with DB BROWSER, I can see the date and time in that column.

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.

Thank you,
Dominique

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:

  1. DATETIME does not exist as a type - so I can’t influence that
  2. 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).

1 Like

@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.

1 Like

@mlauber71 - How can I share the db file with you? I can’t upload it or the zip to this forum?

You could upload a KNIME workflow here that would contain the sample database and the problem or you could use the KNIME hub.

@mlauber71 - here is the workflow with the data I hope. I exported without reseting the workflow.

HomeAna_Test.knwf (19.6 KB)

Thanks a lot for your patience and for looking into this.

I would need the SQLite database. In your workflow the data is already a date

Sure, how can I send you the SQLite file? it is about 20Kb.

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.

Ok, got it - thanks for the example.

I think I made it right, so the SQLite database should be in the workflow now.

Many thanks again!
HomeAna_Test.knwf (30.6 KB)

@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!

Hello @dchabauty,

here is one way how to read whole information into KNIME using cast in your query.

HomeAna_Test.knwf (37.6 KB)

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.

Br,
Ivan

1 Like

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 … :slight_smile: