Question about Database Connector node time zone setting

Database Connector node has 3 options for time zone setting, and node description covers them in some detail, but I’m still confused.
Does anyone know what exactly KNIME does with dates and times for each of those options, or mainly UTC vs local/selected?
I observed that when UTC is selected, it seems to convert dates from DBMS timezone to UTC, i.e. for my timezone it applies the displacement and moves 10 hours back. For Local - it does nothing. But the DBMS and my laptop (on which KNIME runs) are in the same time zone, so I can’t observe what would happen if those were different, or if database data types were with time zones.

Thank you.

Hi Dimitri,

  • No correction: The current DB integration always assumes the dates stored in a DB to be UTC. It just prompts the dates as they are in the DB and that is why it is called “no correction”.
  • Use local timezone: It uses the timezone information of your machine to transform from the UTC in the database to your timezone.
  • Selected timezone corrects the UTC datetimes to the time of the selected zone

Attention: The two last options can lead to unexpected timezone conversions if the timestamps in the DB are anything else than UTC. E.g. if the timestamps in your DB are GMT+1 and you want to correct them to GMT+3, you have to select timezone GMT+2 to convert them.

We are adressing these counterintuitive issues in the already ongoing database rework and will keep you posted as soon as anything changes here.

I hope that helps.

Cheers,
Marten

Thank you, Marten, this makes sense.

However, if I select “Use local timezone”, and the database I’m connecting to is in a time zone different to my local one, and I get database time, then the loaded data will not be local relative to me (the machine KNIME runs on). This can be confusing.
Also, the UTC option states “No correction (use UTC)”, while in fact the dates/times will be corrected, i.e. converted to UTC, if the database is not on UTC time zone.

Maybe the options would be more clear if they we called:

  • Convert to UTC
  • Keep database time zone
  • Convert to selected time zone

And is there a difference between dates/times without time zone and dates/times with time zone?
I guess there isn’t because the node description mentions that time zones are not supported, but I wanted to confirm.

Hi Dimitri,

My last response was not completely correct, that is why I edited and extended it a little. If you’re still puzzled don’t hesitate to shoot your question. :wink:

Cheers,
Marten

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.