Localize timestamps in OQL query output

0
We have several plants world wide using the same Mendix applications.DateTimes in the data records are logged UTC time but when I query the data via OQL, I prefer to convert it to the local time zone of the plant. Our Plant entity has a TimeZone reference, so I try to use the time zone Code from the System.TimeZone in DATEPART functions to convert, but that is not allowed. Only when I hard coded e.g. 'Europe/Berlin' as a string it works. But of course, in the same table I have records from other time zones like China.How to cimply convert UTC times into the local time zones in OQL query output?I'm executing OQL statements to generate simple exports of certain data from our application, without the need of adding screens for any type of export.
asked
1 answers
0

To avoid “dynamic timezone” limitations in OQL, a practical workaround is to build one UNION ALL query by looping over your TimeZones (not over the data rows). The idea is to generate one long OQL string in a microflow and then execute it once for your export.


First, retrieve the list of System.TimeZone objects that are actually used by your Plant records (for example: retrieve Plants, then build a distinct list of their linked TimeZones). This keeps the query smaller and avoids generating blocks for timezones you don’t use.


Next, iterate over that TimeZone list. In each iteration, append one OQL “block” that (1) hard-codes the timezone code inside the conversion function and (2) filters the dataset to only the plants that belong to that timezone. After each block (except the first), add UNION ALL.


Finally, pass the completed OQL string to Execute OQL Statement.


Example (template/pseudo):

SELECT
  O.OrderID,
  DATEPART(hour, O.OrderDate, 'Europe/Berlin') AS LocalHour
FROM MyModule.Order O
JOIN O/MyModule.Order_Plant/MyModule.Plant P
WHERE P/MyModule.Plant_TimeZone = <TimeZoneId>

UNION ALL

SELECT
  O.OrderID,
  DATEPART(hour, O.OrderDate, 'Asia/Shanghai') AS LocalHour
FROM MyModule.Order O
JOIN O/MyModule.Order_Plant/MyModule.Plant P
WHERE P/MyModule.Plant_TimeZone = <TimeZoneId>



answered