Bug: Wrong display of Date and Time fields read from Postgresql

KNIME Analytics Platform
#1

(If this is not the right place to report bugs, please correct me)

 

The contents of DATE and TIME fields read from Postgresql are displayed wrong.

 

System: Linux Fedora Core 18 - 64bit

KNIME: 2.7.4

Postgresql: 9.2.4

JDBC driver: postgresql-9.2-1002.jdbc3.jar

 

Table:

CREATE TABLE "EQ_NEIC"
(
"EQDATE" date,
"EQTIME" time without time zone,
);

 

 

Nodes where seen:

a) "Database Reader"

b) Combination of "Database Reader" and "Cache"

c) Combination of "Database Connector" and "Database Connection Reader"

 

Query:

select

"EQDATE",

"EQTIME"


from "EQ_NEIC"

where

"EQDATE" = '2011-03-15'

and "EQTIME" >= '08:00:00'::TIME

and "EQTIME" < '09:00:00'::TIME


order by

"EQDATE",

"EQTIME"

 

Results in Postgresql:

EQDATE      | EQTIME

------------±------------

2011-03-15  | 08:01:43.76

2011-03-15  | 08:08:37.08

2011-03-15  | 08:11:47.7

2011-03-15  | 08:13:50.1

2011-03-15  | 08:16:59.52

2011-03-15  | 08:28:25.71

2011-03-15  | 08:36:21.6

2011-03-15  | 08:45:33.53

2011-03-15  | 08:46:35.53

2011-03-15  | 08:48:24.13

2011-03-15  | 08:56:41.87

(11 rows)

 

Results in KNIME:

EQDATE      EQTIME


2011-03-14  07:01:43.760

2011-03-14  07:08:37.80

2011-03-14  07:11:47.700

2011-03-14  07:13:50.100

2011-03-14  07:16:59.520

2011-03-14  07:28:25.710

2011-03-14  07:36:21.600

2011-03-14  07:45:33.530

2011-03-14  07:46:35.530

2011-03-14  07:48:24.130

2011-03-14  07:56:41.870

 

Expected behavior: KNIME should not alter data on its own initiative.

 

#2

The difference in the day looks really strange. Can you double-check that it is in fact one day off? The different in the hour is caused by timezones. KNIME expects dates in the database in UTC time but displays it in your local time zone. This can be confusing and we are currently working on better time zone in KNIME in general.

#3

> Can you double-check that it is in fact one day off?

Yes, it IS one day off. I imported all data in the DB and it corresponds to what was in the original text files (mostly CSVs)

 

 > The different in the hour is caused by timezones.

> KNIME expects dates in the database in UTC time but displays it in your local time zone.

> This can be confusing and we are currently working on better time zone in KNIME in general.

That is indeed confusing smiley

IMO KNIME should pass input data 1:1 unless told to change (or assume) something.

 

My system TZ is UTC+1 plus summertime, what means UTC+2 is in effect.

 

Two more examples:

Content of a "timestamp without timezone" field: "2012-07-27 12:47:18.416116"

In KNIME it becomes: 2012-07-27T10:47:18.416

 

select now() from dual (output has type timestamp with timezone)

Postgresql gives "2013-05-21 14:42:02.418295+02"

KNIME says "2013-05-21T12:42:35.140"

(Local time was 14:43:something when writing this)

#4

Yeah this problem has been around for a while and either my team mate or I (I cant remember who is was) has reported this in the past. We actually use a Simple Java Snippet node in order to correct dates.

DateFormat format =
new SimpleDateFormat("yyyy-MM-dd");

Date dt = new Date();
try{
 dt = format.parse($Put the date colum here$);
dt.setTime(dt.getTime()+1 * 24  * 60 * 60 * 1000); }
catch(Exception e){}
return format.format(dt);


 

#5

Hi, i have this problem, but i need a way to fix the time part too. Some body can helpme???

Reggards