The Real Data Type for Dates and Times; and Microsoft Flow’s Perspective

It’s OK to call me old school. For me, dates are stored as floating-point numbers. It’s still that way in many applications, including Microsoft Excel. Sometimes I think of date and time as a series of seconds after a fixed time – so, a very large integer, because that’s another way we’ve classically represented dates and times in code. However, Microsoft Flow takes a different approach. To Microsoft Flow, a date/time is a string – a standard ISO timestamp.

Background

The next three sections provide some background on how we came to understand dates and times and how we represent them. If you’re just interested in the details of implementing dates in Flow, you can just know that they’re strings – and skip the background.

Tracking Dates and Times

Most of us take it for granted that we all agree it’s the same day – but until 1875, that wasn’t the case. That’s when everyone officially agreed upon the Gregorian calendar that we’re all familiar with. Similarly, few people need worry about the fact that the agreement only stabilized dates back to 1582.

Our relationship with time became more concrete around then. While we had chronometers – which were capable of relatively stable timekeeping – since 1764, it wasn’t until the introduction of the railroads that people officially standardized times to allow for train schedules that worked across large distances. Before that, “high noon” literally meant when the sun was directly overhead in the place where you are. Time zones started to be ratified across the globe, and by about 1885, we had a framework for the time zone system we use today.

The ISO 8601 Timestamp

Getting dates and times from one computer system to another is a classic problem. Storing dates as the number of days past a fixed point, as is done for both floating point and integer representations of a date, requires that everyone agree on what the fixed starting point is. The problem is that people didn’t. Sometimes they used January 1st, 1900, and other times other dates were used. This created problems where dates would seem to shift. As an attempt to resolve this issue, the ISO 8601 standard was first published in 1988.

It spells out the details of how to convey a date and time (as well as durations) in a standardized way. This addresses the difference between the folks in the US, who often represent dates in month-day orientation, and folks in Europe, who are more accustomed to day-month approaches. More than once, I’ve had to look at a date carefully to figure out what was intended because of the possibility of these transitions.

Time Zones and Daylight Savings Time

One of the tricky challenges that happens in computer systems is addressing times across time zones, particularly dealing with daylight savings time. I’m based in Indiana, and for my formative years in computers, we didn’t observe daylight savings time. Though we do today, I vividly remember my joy at not having to adjust every clock in the organization I worked for twice a year. Today, this is mostly handled automatically, but back then it would have been a task for some poor human.

When we refer to our time, we do it conveniently. I refer to 5PM in my current time zone. Two people speaking of meeting at 5PM are fine if both are in the same time zone. If they’re not, one of the people may have to wait a while. Because of time zones, 5PM in one place isn’t the same time as 5PM in another place. It sounds obvious, but it leads to a challenge when sharing times across time zones.

The solution in aviation is that all times are referenced in Coordinated Universal Time (“UTC” came about as a compromise due to the different order of the words in French), which is also called Greenwich Mean Time or, in aircraft parlance, Zulu time. This is the time zone from which every other time zone is referenced. Eastern time – my home time zone – is UTC-5. Thus, if I want to meet someone at 5PM, I’d say 10 PM, or, more properly, 2200 Zulu. That is, I’d use a 24-hour clock instead of the more common 12-hour clock with AM and PM designators that we’re used to. Well, that’s not exactly correct.

If we were observing daylight savings time when I was trying to meet, I’d have to subtract one hour, because daylight savings time advances our clocks one hour. So, it would be 2100 Zulu. One would think that daylight savings time would be consistently observed, but it’s not. Each country chooses when to observe and stop observing daylight savings time. The United States Congress has changed the official dates of observance more than a few times. So, even today, trying to understand what time it is in another place on a given day can be challenging.

The result is that the ISO 8601 standard defines a time zone designator that can indicate the time added to or removed from UTC time to get to the time indicated. This keeps the time human-readable and ensures that it’s possible to get to the exact time.

Standard Date Problems

There are a set of problems when working with dates that we’ve grown accustomed to. Adding days is easy, but adding months isn’t. Because months don’t have a standard number of days, adding a month could mean adding 28, 29, 30, or 31 days to the current date. Similarly, adding a year to the current date could mean adding 365 or 366 days – though, in practice, few people worry about this problem. The good news is that Flow’s Date and Time activities allow you to address these standard problems.

There are a second cluster of problems that Flow’s activities don’t solve. That is the common requirement that dates be expressed in terms of business days. So, if you’re a five-day per week operation, the dates should be advanced five business days. This isn’t possible directly with the actions provided, but it’s possible to leverage the dayOfWeek() method to determine how to address weekends. However, holiday observance isn’t something that’s feasible today.

Times in Microsoft Flow

The good news is that all the challenging activities – like adding and subtracting from dates and converting from one time zone to another – are available as activities. You must remember that the dates are strings. If you want to see more about the out of the box flow activities for managing dates, take a look at Working with Dates and Times inside of your flows.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.