I do a lot of integration work, and often it involves REST Web Services and other forms of XML data transfer. Generally I want to provide dates and times in RFC3339 format, or some variant of ISO8601; for example,
1996-12-19 16:39:57 -0800.
When writing a Web Service in LotusScript, it’s not too hard; I built myself a function which converts a NotesDateTime or Variant of type 7 into UTC and then formats it appropriately, and I use that all over the place. Similarly, in Java it’s easy enough to convert everything to UTC and avoid needing to work out the correct numeric time zone offset.
Then one day I had to put the value of @Modified into a view column, for export. Since view columns can only use formula language, I was faced with trying to produce the appropriate format using only @Formula and column properties. To make things worse, DST absolutely had to be handled correctly. So did time zones that aren’t on an hour boundary, such as -0330. (We don’t currently have any servers in Newfoundland, but I don’t want to get phone calls if we ever do set up a site there.)
For a while, I struggled with @Zone, @Text, and a mess of string manipulation — but it didn’t seem to behave properly. There’s something odd about the output of @Zone that makes it behave differently from an identical pure string when converted to a number.
Date/time columns have various ways to customize their output. However, there’s no way to say “always show dates and times in UTC”; that would be far too useful. There’s an “always show dates and times in local time zone”, but that changes twice a year because of the DST madness, and frankly I don’t trust it to change on the right dates. I could set the server to be in UTC — and in fact I often do — but I don’t want my application to break if deployed to a server with different locale.
Then I suddenly remembered that Notes 6 gained an @GetCurrentTimeZone formula which returns the current time zone where the code is running — either the client PC’s time zone, or the server’s time zone, whichever context it is in. The formula language also has @TimeZoneToText, which will convert a time zone value to a string like (GMT-06:00). Suddenly the answer was obvious: it’s trivial, so long as you use two columns (or two fields, in the case of a form).
The first column/field just displays the date/time value, formatted with custom preferences to look like RFC3339 minus the time zone part. The second field is then
@Right(@TimeZoneToText(@GetCurrentTimeZone; "S"); "GMT");
The result is a clean ISO-formatted date/time value. Most importantly, because both fields/columns use the local time zone but display that zone numerically, the output indicates the correct instant in time even if the system changes to/from DST on the wrong day.
True, this isn’t rocket science. However, it’s one of those interesting situations where I was unable to see the obvious solution for a long time, because I was in a mindset: I was looking for a single formula to produce all the output at once. If such a thing exists, I bet it’s very large.