UTC, BST and GMT - Excel Importer / Export to CSV

0
I have an app which imports CSV files from another system. The source system stores date/times in local format BST/GMT. When the CSV flat file importer brings the date into Mendix it believes its UTC already, note it does not convert it to UTC, it just assumes it is UTC. The attribute that it is importing to in Mendix is ‘localised’ but because it thinks the date/time is already UTC when it is presented in the front end it shows as 1 hour ahead in BST. i.e. 27/09/2020 09:00 BST imported into Mendix becomes 27/09/2020 09:00 UTC when it should be 27/09/2020 08:00 UTC. This causes me issues down the time where I want to export date in BST or UTC as the relevant formatdatetime functions are not working off the correct baseline. Any ideas how I can resolve this problem?
asked
3 answers
1

One way to handle this could be to use the 'parse with' option in the excel importer, and create a custom microflow containing some logic to set it to the relevant timezone.

(Just make sure you adjust for daylight saving time)

 

 

answered
0

Yes this was my original thought but it seems that Mendix always presumes that the value being imported is UTC before any parsing in the flat file importer. As such, I always have the wrong baseline which means I think I’m going to have to maintain a calendar that I can reference. 

i.e. I import a day light saving time which is assumed UTC at which point I use ‘formatDateTime’ function on this date and it converts incorrectly because the UTC is actually BST so I get BST + 1 rather than UTC + 1.

 

answered
0

When importing a datetime with the flat file importer the convertion from string to datetime is done with the UTC timezone.

If you want to change this open the shared.java class and look for the method convertDateStr.

Add the following line after :

SimpleDateFormat dateFormat = new SimpleDateFormat(sdf);
dateFormat.setTimeZone(TimeZone.getTimeZone("BST"));

If you want to create a configurable timezone, some more work would be involved, but seems quite feasible.

Hope this helps in your specific case.

answered