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):
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?
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'?
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.