We are facing problems when reading data from PostgreSQL to KNIME.
It seems that the data is read as it is specified in the database connector node (UTC - no correction- or Eurpe/London time zone), however when we want to see the data in a different time zone, the results provided by KNIME are incorrect. In particular, we obtain exactly the same results for values in UTC and in Europe/London time zone (regardless of the time zone selected in the connector node). This shouldn't be the case as local time should increase one hour when the clock goes forward.
Please find attached a document where it is possible to see the different queries and results (from Postgres and KNIME).
A bug was reported in this forum a couple of years ago, but it seems that it hasn't been fixed:
Would you be able to attach a simple example workflow that outlines the steps needed to reproduce the error.
You could use the sqlite connector in place of the Postgres connector. Then use e.g. the 'Time generator' to generate an example dataset and write this to the sqlite database using the database writer. Following this add in the steps that you follow to reproduce the problem. Don't worry if the SQLite connector doesn't actually reproduce the problem, I'll look into that at my end.
Please find attached a zip file with an example workflow using the SQLite connector. The "readingdatetime" column is read as a integer or string instead of timestamp... So we haven't been able to execute the last node (where the different time zones are specified and where the problem appears).
We see your point and understand that the options in the DB reader node are confusing (if not wrong for certain cases). The root problem is that the current datetime representation in KNIME is suboptimal as it doesn't
support timezone information. Future versions of KNIME will be using Java 8, which comes with a new DateTime lowlevel API. We have already allocated resources to develop new KNIME nodes and type to better deal
with this type of data.
In the meantime I think that there is a possible workaround. You can read the field as a string, and then convert to a Date/Time cell, using String to Date/Time. See the attached workflow for more information.