SQLite datetime

KNIME Analytics Platform
1

Hi,

Just curious when I select a field converted to datetime in query… why does it show up as a string in knime? Can’t a query of sqlite be retrieved in knime as a datetime datatype?

Thanks,
Jeff

2

@Jeff I think you might have to see how SQLite stores data. It does not have its own storage format but would represent date and time variables in a specific way:

https://sqlite.org/lang_datefunc.html

Date and time values can be stored as

  • text in a subset of the ISO-8601 format,
  • numbers representing the Julian day, or
  • numbers representing the number of seconds since (or before) 1970-01-01 00:00:00 UTC (the unix timestamp).

I have collected some examples how to handle that: