Just a quick q with how dates are stored in records vs how we compare dates in JavaScript (namely, the SLA calculation event action for Citizen Hub). If we create a date object in JavaScript with new Date() to compare against an existing date value saved in a Date property type, can we assume the already saved date is already converted to UTC once we retrieve it, or does it need to be converted to UTC as well?
We know there’s already a bit of a quirk where dates are saved as dd/mm/yyyy hh:mm rather than the standard ISO8601 YYYY-MM-DDTHH:mm:ss.sssZ, and we already have field processors to convert this for us, but I was moreso wondering if we’re perhaps overlooking UTC and timezone differences.
It depends how you retrieve the date from the Property field.
All date times are stored in the database as “YYYY-MM-DD HH:MM:SS” format.
If you use cs.search() to retrieve property values, by default they will be returned in their ‘saveable’ format, e.g. the format in the database, unless you set the “mode” option to anything other than ‘saveable’. This is good if you’re doing date calculations with those values in JS, as it is consistent and unambiguous.
If you use record.get() though to retrieve a date/datetime, by default it will be returned in it’s display format (which will depends on app settings, but assume something more like dd/mm/yyyy hh:mm). This is usually undesirable if you’re trying to do any calculations with it in JS, so you should use the ‘saveable’ mode option in this case to return it in the “YYYY-MM-DD HH:MM:SS” format as above, e.g.
I think date fields store the date and time as the timezone offset value, not the UTC value, and I dont believe there’s an itnergated way to retrieve the UTC value natively. Timestamp fields store a unix timestamp so could be easier for calculations if you then rely on the frontend to display them in the relevant locale. You can check out the way the different base formats work here: https://docs.netcall.com/docs/liberty-create/2026.2/build-studio/data-store/base-formats
We’ve decided that the current solution to calculate SLAs in Citizen Hub with JavaScript is pretty brittle, and won’t work for us when we have different services with different ways of working and different definitions of how an SLA should work. We’re going to deprecate the event action and just use composites to do datetime calculations, and use rules and subsets to determine days remaining and whether a case is overdue or not.
Regardless, thank you both for the very useful information on how datetimes are handled in Code Studio. It sounds like it’s more of a headache than it needs to be, particularly with the potential for UTC and BST mismatch, so we’re going to stick with composites from now on where possible, which only uses the set timezone in locale.
with regards to this – would we still be able to wrap this in new Date()? I always thought it needed to be in an ISO8601 format. I’ve read that we could replace the space with a ‘T’ to indicate it’s based on the local timezone the server Code Studio is running on (does that match the locale we set in Build Studio?), and we could then use new Date() by itself to get a NOW() equivalent in the timezone we set, but again this seems more complicated than it needs to be when we know we can just do it low-code via composites.
Thanks Bob, this is really helpful. I’ll have a look through timers and see if it works for us; one of our service teams has a different definition on what counts as a working day (whereby the first day isn’t day 1, it’s actually day 0), so we naturally need a very flexible solution that can different ways of providing a case SLA on a per-service basis. Glancing over that page though, it definitely seems like an option to experiment with.
I had to do some major changes to the default event action to make it work with working hours rather than just days, and i think perhaps the whole lot could do with a refactor to simplify some aspects. Not had the time/inclination to take a poke myself just yet.
At the same time i also adapted it so that you could specify which calendar to use when running the code, which should allow for different definitions of a working day (though it still assumes that each working day has the same working hours). One thing that would have been useful is the ability to retrieve the “Weekday windows” values from a calendar.
I think certainly the biggest challenge with using an event action to compare working hours/working days is the data processing you need to do, ensuring the datetime values are prepped correctly for JS comparison.
Conditional calendars is an interesting challenge that we haven’t needed to solve just yet, and that’s certainly a limitation with using composites as you’d only be able to set a single calendar. Although, I suppose you could just have multiple composites with different calendars on a service-by-service basis?
I know it’s not exactly the same, but you could use cs.is_working_hours() to pass in the time and calendar ref, which returns a Boolean true/false. That way, you wouldn’t need to retrieve the ‘weekday window’ values when checking if a timestamp is during working hours or not, you’d just run it through the is_working_hours() method.