asked on September 19, 2017
Hello,
I am getting report data from a CSV file. There is a date field that stores date only, no time. If I prefix the CSV column with "D+" I can use the date with hierarchy in my row headers. However, the date shifts exactly one day back: if the CSV file contains 2017-01-01, the date will change to 2016-12-31 in the hierarchical row header. I suppose this is due to my being in GMT+7 time zone.
When I prefix the respective CSV column with "ds+" and then use datePattern in the options of the report object, the date displays correctly as 2017-01-01 or whatever pattern string is used.
But as I do need to use the hierarchical date for this report, I really have to use the "D+" prefix for my CSV date column.
Is there any other way I can induce hierarchical date levels? Or any other way to manipulate the date-only, "D+" prefixed column to display the date as the value saved in the CSV file without shifting the date to previous day?
Any help/hint is appreciated.
⋅ September 20, 2017
Hello Mart,
Thank you for your question. We have not managed to reproduce the issue on the latest version of Flexmonster which is 2.403. Could you please provide us with the information of what version you are currently using(Press Ctrl+alt+i)? If your version is different from the version above please update the component to the latest version.
Please let us know if it works for you.
Regards,
Dmytro
⋅ September 20, 2017
Hello Dmytro,
As per attached screenshot, I'm using 2.403, too.
I found a workaround, though cumbersome, for this: by adding a few hours to the date only field in CSV and then saving it in date/time format the date assignment is correct. So when the CSV date field prefixed with D+ stored as "1/1/2017" (which Flexmonster interprets as 31/12/2016 in my browser at GMT+7 while server is at GMT+1) is updated to "1/1/2017 14:00" then the error does not happen. But if the field is date only, every date read out of the CSV file is interpreted as one day earlier, at least when the CSV field is used hierarchically with prefix D+.
I'm rather certain this is a time zone issue, but it would be great if there was a feature like the datePattern also for fields being read as hierarchical row headers, and not only for date strings in general. Because, as mentioned before, if the CSV field is used as normal date field without D+ prefix then the use of datePattern makes it work fine and there is no date shift.
In fact, to test the problem, I wrote a quick PHP script to convert my data set to JSON and then read in the data with the respective date field set to type "year/month/day", but the error was the same: date got shifted to one day previous. As soon as I changed the date field to date-and-time and added 14 hours (like above: "1/1/2017 14:00") there was no more problem and the data displayed all correctly.
I can live with the workaround, though it involves one more sep preparing the data, but I just wanted to let you know in case you receive similar problems. It might simply be the difference of time zones between browser and web server or Flexmonster host server.
Best regards,
Mart
⋅ September 22, 2017
Hello, Mart,
Thank you for the quick reply.
We would like to emphasize that our component supports ISO 8601 date (other formats may be used, but results can be unexpected). With this format, you can specify time zone designators. Please find more information about this format by the following link: https://www.w3.org/TR/NOTE-datetime.
Let me know if using time zone designators resolves your issue.
Regards,
Tanya
⋅ October 4, 2017
Mart,
Are you also using the Flexmonster Data Compressor with Java. I have found that one their classes, CSVReader, is resetting the default timezone to "UTC" (overwriting your actual timezone--which I think you said was GMT+7). This was messing up my date formatting as I was constructing my CSV output data. My workaround was to just save the current default timezone before I instantiated their Compressor classes and then restored it immediately after. This solution has worked for me. I have already reported this to the Flexmonster Support team
