Date/Time column subtracting one day

KNIME Analytics Platform
1

I’m not sure if this is a bug or if I am just using the “Time Series > String to Date/Time” node incorrectly.

I have a string date column in this format 2010-02-06 (yyyy-MM-dd)

Using the String to Date/Time node converts it to date format and shows up in the preview window in this format 06.Feb.2010

But then if I write this to a CSV file or a mySQL database it gets written out with one day subtracted as 05.Feb.2010

The good news (I guess) is that it is consistent across any date I’ve tried.

Has anyone else seen this issue or do you know if I might be doing something incorrectly?

Thanks,
Troy

2

Hi Troy,

that’s a strange issue. I tried to reproduce your problem with the CSV Writer but without success. Could you please try the following simple setup
Time Generator -> Time To String -> String to Date/Time -> CSV Writer
and compare the two appended date columns with the original one? Do the dates match?

Best regards,
Dominik

3

Same issue.

Here is exactly what I did.

Time Generator, 1 row, left default “Use date” set for start and end, date = 2009-2-22 and 2010-2-22
Time to String, left defaults but changed format to yyyy-MM-dd
String to Date/Time, left defaults but changed format to yyyy-MM-dd as well

Preview output is
22.Feb.2009, 2009-02-22, 22.Feb.2009
But…
Here is the .csv file output
"2009-02-21",“2009-02-22”,“2009-02-21”

I’m wondering if it might be a time zone issue? When I open the Time Generator the end point date is defaulted to 2-22-2010 which is tomorrow for me. I am running this at 8:40PM on 2-21-2010.

4

Did some more testing by including the hour.
I set the hour to 10 which shows up correctly in all three columns in the preview but I get this when it is written to the .csv file…
“2009-02-22T02:00:00”,“2009-02-22;10:00:00.0”,“2009-02-22T02:00:00.0”

Thanks for any help.
Troy

5

OK, figured out a work around for now.
I’m using the “Preset Date/Time” node to set the hours on my date to 10.
This changes my column from 2010-02-22 to 2010-02-22 10:00:00
which then writes out as 2010-02-22 02:00:00AM in an Oracle database. Since I only care that the year, month, day is correct this works for now.

6

Hi Troy,

good that you found a workaround for that. I can confirm now that there is a time zone related problem which needs to be fixed.

Best,
Dominik

7

Hi, I have a similar problem but in my case is adding four hours to date, and my work is very important times of the day and the date of each record in the database. What could I do??
regards
Lisi

8

Ah!!!, im using knime 2.5.2

regards Lisi