Recording what date and time something happened and reporting it back to users is complicated in multi-user web applications.
Imagine that a user in Toronto saves a record at 3:30 pm local time on December 15th onto an SQL server in an unknown location, and a month later a user in New Delhi wants to see what time the record was saved. But the New Delhi user wants to see what time it was in New Delhi when the record was saved, not what time it was in Toronto or in Greenwich or wherever the server is. And that was 2:00 am on December 16th.
For recording dates and times in a multi-user system, you want to use something that is the same the world over, (coordinated universal time or UTC is the standard choice), while for reporting dates and times, users want to see everything in their own local time.
Beyond those issues, both R and SQL have their own date and time functions and formats. These allow you to do things like add or subtract times from each other to determine the length of periods. All of this is vastly complicated by hours and seconds having 60 units, but days have 24 hours, months have 28 to 31 days, and years have 365 days (except in leap years). And then there are the leap seconds. Since the time functions take care of keeping track of all that, you definitely want to use them. But these functions themselves can be complicated, so we should only use one system’s functions and keep everything as strings in the other.
One final complication is that when you a report a date-time to the user, you have to convert it into a string. To facilitate all this in the Open-Meta app, I use these two R functions for calculations and for storing date-times in SQL as strings:
# sTime() = now() as UTC string (aka "server time") sTime = function(t=now(tz="UTC")) { return(paste(as.character.POSIXt(t), tz(t))) } # bTime(t) = server time as "browser time" string bTime = function(s, UTC2me=session$userData$UTC2me) { t = ymd_hms(s) + UTC2me return(as.character.POSIXt(t)) }
The first function, sTime(), returns the current time, in UTC, as a string that can easily be stored in SQL. I call this server time. The second function, bTime(t), converts server time to browser time, which is a server time converted to the user’s local time, again as a string that can easily be passed to the browser.
The functions depend on the R package lubridate for the now() and ymd_hms() functions they use, as well as a JavaScript function that returns the offset between the user’s browser and UTC time. I call that offset UTC2me and store it in the $session$userData list. The JavaScript looks like this:
Shiny.addCustomMessageHandler('getUTC2me', function(x) { var offset = new Date().getTimezoneOffset(); Shiny.onInputChange("js.UTC2me", offset); });
You also need to add the following three items to your Shiny code to run the JavaScript function and obtain its result:
js$getUTC2me = function() { session$sendCustomMessage("getUTC2me", "") } js$getUTC2me() observeEvent(input$js.UTC2me, { session$userData$UTC2me = -minutes(input$js.UTC2me) })
UTC2me is in minutes and is actually the offset to convert browser time to UTC. Since we’re going the other direction, UTC to browser, and since the offset can be either positive or negative, we have to flip the sign. The observeEvent() also uses another lubridate function, minutes, which converts a string into an R date-time class called POSIXt.
Back up in the bTime(t) function, what’s going on is that UTC2me is added to the UTC string from the server, which has also been converted to POSIXt. Doing the addition in POSIXt takes care of all the issues of date-time math. The result is then converted back into a string for display on the server.
Now it’s not so complicated after all.