Yes, me. I do. I know what time it is.
First, though, you have to tell me where you are and what the date is. Then I can tell you what the local time is where you are.
Simple, eh?
Yes, and No. Obviously, we do this all the time without really thinking about it all that much. If it's 10:02 PM in Seattle in June, then it must be 1:02 AM the next day in New York.
With Access Web Apps, unfortunately, it's not that simple. When Access Web Apps were launched, they weren't fully gestated. Lots of good things went into AWAs, but a lot of important stuff got left out.
More importantly, the basic technology behind the AWA made it impossible to NATIVELY figure out what time it is within the AWA itself. We're talking about server technology which runs in a browser. As far as the AWA is concerned, the current time is the time on the database server where the SQL Azure--or SQL Server--database resides. If the server is somewhere in central Washington state, then Now() returns the current local time in the Pacific time zone. Washington state observes Daylight Saving time. If you ask for Now() in July, you get PDT, but if you ask for Now() in December, you get PST.
Let's say, for example, you happen to be in Arizona when you log in to this AWA. Arizona is one of a handful of states that don't observe Daylight saving time, so it'll be one hour ahead of PST, but it'll be the same hour as PDT.
And, of course, if you happen to be in New Jersey, Now() on this same database server will tell you it's three hours earlier than it really is, because of where your browser is physically located in relation to the database server.
It's enough to make a serious developer walk away from AWAs. I've actually talked to other Access developers who did just that.
Me? I'm stubborn. Besides, I figured, how hard could it be?
Not all that hard, as it turned out.
You can quite readily calculate the current local time for any location in the U. S. If you work at it, you can expand that to other countries as well.
Here's how I did it in one AWA to show how it works. It's based on a list of addresses for various locations throughout the United States. I started with family addresses, but the demo is now a generic set of locations,
First, I imported a new table of ZIP Codes which also includes the UTC offset for that ZIP code and whether that ZIP Code is in a state that observes Daylight saving time. I got that off the internet.
A word of caution. I am well aware that there is no guarantee this approach will handle every possible case, even within the U. S., where each ZIP code does fall entirely within a state. I am aware of one case, out in West Texas, where a ZIP code might be in two different time zones. There are two West Texas counties in the Mountain Time zone, while the remainder of the state is in the Central Time Zone. Therefore, it depends on whether the relevant ZIP codes in those counties fall entirely within their respective county lines. I don't know for sure, but I'm sure local residents do. I'll leave it up to them to work out the details. With that caveat, then, the table provides two of the three pieces of data we need to do the time zone offset calculation.
Next, I created a table of the dates and times when Daylight saving time goes into effect each year, and when it ends each year. This allows me to compare the current date to the date when DST changes and decide whether to apply it or not. This table will need to be maintained for future years, of course. I didn't have sufficient confidence it will be constant for the foreseeable future, although we should be able to count on the next three or four years.
The next step was to update an app table to set flags for this calculation. My app tables have other flags as needed in other operations. It's a handy way to persist such things, but also make them updateable when needed.
Then, I created a Data Macro to perform the DST calculation,
It runs in the On Start Macro. It sets a flag in the app table to indicate whether DST is in effect or not. Later calculations refer to that flag field in the app table.
A second caution. Note that this flag field is the same for everyone using the AWA. If one user logs in, they set the flag. If someone else logs in a little later, they reset it. Except for the wee morning hours on the days when DST changes, though, this really shouldn't matter. And even then, it's not likely to present a serious hurdle because what matters is that the CURRENT local time is calculated as needed.
Next, I created a set of queries to combine the local addresses in the database with the Time zone offsets in the ZIP Code table and the DST In Effect flag in the app table. The queries report current local time at each address, or for all of the ZIP codes in the ZIP code table.
A set of Data Macros calculates the current time from:
a) The time zone offset reported by the app. This is, as previously indicated, determined by the location of the database server hosting the AWA.
b) The time zone offset for the zip code at each address in the table of addresses stored in the AWA.
c) The DST In Effect flag set when the AWA first starts.
The output of the Data Macros can be used in display current local time next to an address on a view.
It's actually somewhat simpler than it might sound. Please go to my website and download the demo if you want to install it on your own Office 365 site, or on your own on-premises SharePoint site. I'd really appreciate your feedback on it.
