Question

CurrentDateTime challenges

3

Hi, I am have difficulties with the use of the currentdatetime parameter in the following 2 cases: Background Session time = UTC+2 Time of test (session) = 0.30 AM June 5 Organisation is using the application (almost) 24x7 worldwide. Using an Entity named Transaction containing a TransactionDate field (not localized) and a quantity (integer). When user wants to create a transaction record he presses a Microflow button which starts a microflow A that contains a Create object action and a Show Form action. When the user completes entering the transaction details he presses a Save button containing a microflow B that will Validate and Commit the record. 1: Using CurrentDateTime in a comparison User creates a transaction and picks the transaction date by using the date picker and sets it to 5 June 2014 and saves the record. The validation included in Microflow B will now have to do a check whether the total quantity of all transactions entered in the system exceeds a specific quantity (e.g.100). In order to do this a retrieve action will retrieve all transactions with a TransactionDate <= CurrentDateTime. In this case the retrieve does not retrieve the record just entered by the user. When using the debugger it turns out that the TransactionDate of the entered record is set to UTC 5-jun-2014 00:00:00 and Session 5-jun-2014 02:00:00. While the currentdatetime is UTC 4-jun-2014 22:30:00 Session 5-jun-2014 00:30:00. Assuming that UTC is always used to compare dates in the microflow it is correct that I do not get any records in my retrieve (5-jun-2014 00:00:00 <= 4-jun-2014 22:30:00 is not ok). However this is not what I want (and need)… I need a way to properly perform this validation. 2: Using CurrentDateTime as a default value When I set a default transactiondate (currentdatetime) using the create object in Microflow A, the form displays 4-jun-2014 in the datapicker, while my session time is 0.30 AM 5-jun-2014. This is very cinfusing for the user… How to resolve/workaround these issues? Thanks. --- Added after reviewing the comments: Thanks for all contributions. The matter seems to be rather complex... Especially since I still think that the business requirement of wanting to store something as simple as a contractual date (e.g. a start date of a contract) that is absolutely not depending on timezones or actual creation dates/times, is a very valid requirement (95% of our date fields cover dates like this). The fact that this requires considerable effort and thought to ensure that users are presented consistent and correct dates does not fit with the overall ease of use of Mendix. Point 1: In our case I am now thinking about using the following variable (datetime) as a substitute to the standard CurrentDateTime parameter in Microflow retrieve actions: parseDateTimeUTC(formatDateTime([%CurrentDateTime%], 'yyyy-MM-dd HH:mm:ss'),'yyyy-MM-dd HH:mm:ss') This will ensure that the user (currentdate) session time is available in the microflow to be used in comparisons of dates entered by a date picker (assuming a date picker stores the date like this UTC 2014-06-05 00:00:00 > which it seems to do). In other words if someone is retrieving the records with a session time of 2014-06-05 00:30:00 (and UTC 2014-06-04 22:30:00), he will see the record since the variable ensures that his session date is used to compare with the date (UTC) in the database. Second point, defaulting a date in a form opened via a microflow. Unfortunately also this is not a straightforward point. Am thinking about using the following variable (datetime) as a substitute to the standard CurrentDateTime parameter in Microflow create/update actions (setting a attribute of type date to the current date): parseDateTimeUTC(formatDate([%CurrentDateTime%]),'dd-MM-yy') This will ensure that the date entered is of the exact same format as it would be when the user would select the date of today (in his session e.g. > UTC 2014-06-05 00:00:00). Additional finding: I also noticed that when defaulting the date using the standard CurrentDateTime parameter, the actual time is inserted in the date in the database e.g. 2014-06-04 22:30:00, in the case the user overwrites the default by using the date picker and sets the date to another date the time is left unchanged... e.g. in this case if the user changes the date to 2014-06-18, it will end up in the database as 2014-06-18 22:30:00... Maybe correct if assumed that only the date is adjusted, but it kind of surprised me anyway (I would expect: 2014-06-18 00:00:00). I would appreciate your opinion on this approach and am of course interested in knowing the best practice Mendix solution for cases like this. EDIT Have filed a Feature request for a Date data type instead of a date time data type. ID: 101628

asked 2014-06-05

Brian Golsteijn

2 answers

Bart Luijten · Answer 1 · 2014-06-05

In both cases I'd say the issue is that the TransactionDate field is not localized. If you set that to localized, I think the following should happen:

Case 1. the user selects 5-jun, interpreted as "5-jun 00:00 UTC+2 // 4-jun 22:00 UTC". Comparing to the currentdatetime will give you the result you want.

Case 2. The default transactiondate will be set to the same value as in your case (4-jun 22:30 UTC), but when shown to the user will be localized, and render as 5-jun

Of course, setting the TransactionDate to localized could have consequences for date handling elsewhere in your application. The data in your database won't be changed (that's always in UTC), but it will suddenly be shown in a localized way, which might cause your users some surprise.

*Edit: If you haven't seen it already, read this document, which will give you a lot of background info: https://world.mendix.com/display/refguide4/DateTime+handling+FAQ

*Edit 2 (Since this is not an option): If you want a date to be displayed the same all over the world, you're not actually talking about a real "date" or "date-time" instance. For instance, the 1st of january is not the same time-period all over the world. To prevent massive headaches in trying to do this with real datetime attributes anyway, I'd advise storing the transaction date as a string and doing any comparisons using that. If you want to do date comparisons, convert the string into a local time using parseDate, which will interpret it as a local time and probably make your case work.

(Also, please be very aware that this means that a transaction marked as "1st of january", might not have been created at the 1st of january in the timezone where the transaction is displayed, because of time-zone differences)

*Edit to reflect the 2014-06-10 changes:

To answer your last question: The best practices are: if the exact times matter, use localized dates. If the times do not matter, use UTC dates, and don't compare to currentDateTime but use {Begin,End}OfCurrentDayUTC. Keep in mind that BeginOfCurrentDayUTC might not have the same date part as BeginOfCurrentDay in this case (this is correct behaviour, and at the root of your question).

Comparing UTC datetimes to localized times (in different user timezones) is a recipe for headaches. If you want to compare an Unlocalized datetime to CurrentDateTime, convert everything to strings.

Regarding your two points: in both cases you are not actually comparing against the session datetime, you are comparing against [sessiondatetime - {timedifference vs UTC}]. In your case this has the effect of seeming to compare a sesison time, but you're still actually comparing a UTC datetime. This will probably give you a working workaround, but at the cost of always having to keep this in mind. Using strings prevents you from making mistakes, since you'll need to convert types anyway.

Finally, I agree that there are valid use cases for a date (i.e. NOT datetime) data type. This is one of them, birthdays are another, and there are probably more. The best way to increase priority for this is by filing feature requests.

Note to your additional finding: yes, the datepicker only adjusts the date. Normally, you'd use a time picker to adjust the time separately. In this case you can probably either use BeginOfCurrentDay as default, or use trimToDays().

Ronald Catersels · Answer 2 · 2014-06-06

I think you still both make a mistake. As in the documentation [%CurrentDateTime%] is always an UTC date / time. This time is for everybody the same wherever I am on the planet. And this should be used to check the transaction date of an process. This transaction date should be a localized attribute. This way I now in which time zone the user is (IF the timezone of the user is set at least). So the user that has his localtime of 0030 on the 6th of june in +1 GMT time zone has an UTC date time of 2330 on the 5th of june.

If you use the calendar widget to pick a date of lets say the 6th it will set the time to 00:00. But since localize is on the date that will be committed to the database will be the UTC date which in this case will be 5th 23:00.

Now if you compare the dates throughout the world you need to do this in UTC because only then all the date times can be compared to each other.

I could help the user if you displayed both times (so local time and UTC time, call it global time to make the concept easier for the end user). Not every user will grasp the concept of timezones but most will understand the concept of a time that is the same everywhere on this planet and that this time is used to compare the transactions.

Regards,

Ronald

[EDIT]

Copy paste of the documentation about currentDateTime: If I am comparing something with a [%CurrentDateTime%] token in a DataGrid, which time should it use as a constraint for a localized and for a non-localized date? So if I do an XPath with the following constraint [LocalDateAttr > [%CurrentDateTime%] or the following constraint [NotLocalDateAttr > [%CurrentDateTime%] what should I expect in the result when it is 12:10pm in boston ET? Should it show all records with a date after 12:10 or all records after 17:10?

Whether something is a local date or not is irrelevant in this case. Note that there is no UTC variant of the [%CurrentDateTime%] token because this wouldn't make any sense, a moment in time is the same everywhere in the world, even if it may be displayed differently depending on the place. To answer the question, this is yes to both. It will show all records after 12:10 EST (for the localized dates) which is the same as 17:10 UTC (which is how your non-localized dates would show), but these times are the same.