I am reading a table into KNIME from Microsoft Server 2005 using the database reader node. The table contains a datetime field where the values always are the very start of a month, for instance '1/1/2004 12:00:00 AM' or '6/1/2010 12:00:00 AM'. The field comes across into KNIME as a DateandTimeCell field so the datatype is correct. The values however are all shifted one or two hours back in time so '1/1/2004 12:00:00 AM' is now suddenly '31.Dec.2003 22:00:00...'. This in effect changes the month and for January observations also the year of the record. A bit messy. Before I start working on a workaround of the problem is there anyone who have seen this bug before and/or have an easy solution?
I guess this has something to do with a limited time zone support in KNIME, and is not related to any database-specific problem. In this thread, this issue is discussed in more details. We are still in the progress of discussing alternatives to our current date/time/timestamp implementation.
We currently don't have support for time zones, that why you get this shift in time as soon as you read data from any source. This thread http://tech.knime.org/node/20823 shows a possible workaround where the hours' field is manually changed to fir the new, different time zone.
Hi, I have a similar problem but in my case is adding four hours to date, and in this case is very important times of the day and the date of each record in the database. What could I do?? i´m using knime 2.5.2
regards
Lisi
Same problem here. Database reader showed each date/time with a value of -2 hours of the actual/correct value. The workaround for us was to set KNIME timezone by adding -Duser.timezone=GMT in knime.ini file.
There's an alternative workaround to use the SQL CAST statement, thereby reading date/time fields as Strings for manual conversion. works OK for individual dates, but is of course cumbersome for multi-date tables.