Hi, I need some help with the following OQL query using DATEPART: SELECT Location.Name AS Location_Name, SUM(BG/Quantity) AS Quantity, DATEPART(YEAR,BG/TrxDate) as Years, DATEPART(MONTH,BG/TrxDate) as Months, DATEPART(DAY,BG/TrxDate) as Days, DATEPART(HOUR,BG/TrxDate) as Hours FROM ForecastManagement.BathGeneration BG LEFT JOIN BG/ForecastManagement.BathGeneration_Location/CustomerManagement.Location Location GROUP BY Location.Name, DATEPART(YEAR,BG/TrxDate), DATEPART(MONTH,BG/TrxDate), DATEPART(DAY,BG/TrxDate), DATEPART(HOUR,BG/TrxDate) The result is the following: Location_Name Quantity Years Months Days Hours Sept-Iles 550.00000000 2022 1 31 22 Loon-Plage 250.00000000 2026 5 31 22 Sept-Iles 550.00000000 2023 10 31 22 The TrxDate attribute is a non-Localized Date and time attribute. For some reason if I use the DATEPART function to get some part of the date (Year and Month is relevant here), I got wrong values. It seems DATEPART treats the date value as it would be a local date (I’m in GMT+2) and changes to UTC by substracting 2 hours which in some cases results in different days, even months in my case. I’m using PostgreSQL, the field type is timestamp without time zone. I didn’t find any parametrization for the DATEPART behavior to not transform the date before getting the parts. Does anyone have an idea how to solve this issue?
asked
Bálint Bokor
3 answers
0
Well, actually, I have to speak for the server here as the server can’t speak for itself and is not inclined in any way to do so: it always uses the only data that is available in the database, the datetimestamp without timezone.
I have never tried it yet, but maybe you can change the definition of the date-attribute to also contain the timezone:
to “timestamp with time zone”.
But then still you have the problem of how to retrieve it, only having DATEPART, which only reads the UTC.
The best way is renaming your attribute TrxDate to TrxDateTime (since that is what it is) and adding an attribute TrxDate, type string, only containing the date that you determine upon Trx-creation with the client-side timezone in mind. And take it from there.
answered
Tim v Steenbergen
1
It is NOT recommended to change the definition of a column/field in the database by hand. This is handled by Mendix can lead to unexpected results.
However, dates can be complex in Mendix. I would recommend to store the integer date part values in separate attributes.