DateTime Madness

I would like to know what kind of black magic KNIME does behind the scenes when it handles DATETIMEs coming from a database.

Here's what's in the database:

DeviceId    LogDate    Value
257    2016-03-27 00:00:00.000    0.057500
257    2016-03-27 01:00:00.000    0.057500
257    2016-03-27 02:00:00.000    0.057500
257    2016-03-27 03:00:00.000    0.060000
257    2016-03-27 04:00:00.000    0.057500
257    2016-03-27 06:00:00.000    0.060000
257    2016-03-27 07:00:00.000    0.060000
257    2016-03-27 08:00:00.000    0.057500

Here's what KNIME gives me as output from a database reader node:

257    2016-03-27T00:00:00.0    0.0575
257    2016-03-27T01:00:00.0    0.0575
257    2016-03-27T03:00:00.0    0.0575
257    2016-03-27T03:00:00.0    0.06
257    2016-03-27T04:00:00.0    0.0575
257    2016-03-27T06:00:00.0    0.06
257    2016-03-27T07:00:00.0    0.06
257    2016-03-27T08:00:00.0    0.0575

Notice that now I have TWO records with a 03:00 AM timestamp.

If i write this dataset back to a new database table, specifying the DATETIME type for the LogDate column, I get the following when I read from the databse directly (using another SQL query tool):

DeviceId    LogDate    Value
257    2016-03-27 00:00:00.000    0.0575000000
257    2016-03-27 00:00:00.000    0.0575000000
257    2016-03-27 03:00:00.000    0.0575000000
257    2016-03-27 03:00:00.000    0.0600000000
257    2016-03-27 04:00:00.000    0.0575000000
257    2016-03-27 06:00:00.000    0.0600000000
257    2016-03-27 07:00:00.000    0.0600000000
257    2016-03-27 08:00:00.000    0.0575000000

Notice that I now have TWO records with a 00:00 AM timestamp.

So reading data, and simply writing it back, changes the datetime fields!

I've tried several settings in the database connector configuration (no correction - use local time) but they just change which timestamps are being changed, they don't prevent it.

Anybody who can shed some light on this?? And especially, how do I prevent KNIME from changing my data?

Tim

 

Does Daylight savings, timezone come into play somewhere?

The two records with 03:00 AM make sense because 02:00 AM doesn't exist on the 27th of March 2016 (the clock is immediately advanced to 03:00). The two records with 00:00 do indeed look strange. We need to look into it.

 

Yes, I understand the 02:00 AM one: it does not exist on 27/03, at least not in this timezone. So when the driver is configured to do local timezone correction (the default), it changes the 02:00 AM to 03:00 AM. I've run the same query with a Java based query tool (Squirrel) as opposed to Microsoft's own SQL Server Management Studio, and this tool also returns two records with 03:00 AM.

When I configure the driver to use 'No correction', instead of not touching the dates, it thinks the dates are in local time and 'corrects' them to UTC. Effectively this means that all timestamps up to 02:00 AM are corrected by -1 hour, timestamps after 02:00 AM are corrected by -02h. So I now end up with two 01:00 AM timeslots, and all other timestamps corrected by either -1h or -2h.

Basically, this option does not do what it says.

So how do I force KNIME to NOT change my datetime fields? What is the 'KNIME best practice'?

Tim

 

 

I agree the timezone handling is confusing (at the very least). The times shown in KNIME tables views are always times in your current local timezone. Internally Java stores the UTC time and just adds the timezone offset when displaying dates and times. The settings in the database writer specify what to do with the internal UTC values when writing to the database:

  • either leave it as is, which means the UTC time will get written (which are usually not the same values as you see in tables)
  • convert the internal UTC value to your local timezone, i.e. write the very same value as is displayed (except for the weird problem with 01:00 and 00:00 already mentioned above)
  • or convert to any other timezone

For the reader it's opposite: the setting controls which timezone to assume for the values in the database. They will first be converted to UTC (using the offset of the selected timezone) and then displayed in your current local timezone.

Obviously there is room for improvement...

WHy is there no option to simply write the date as is, and not to do any conversions?