Power Query M Primer (Part 8): Types—The Temporal Family

Temporal—something related to time (not to be confused with the word temporary). M offers several temporal types: `date`, `time`, `datetime`, `datetimezone` and `duration.`

This group of types has a lot in common, so we’ll explore it a little differently than the types we’ve encountered previously. First, we’ll introduce each type and look at its unique facets. Then, we’ll examine how the various types in this family play together (like common functionality they share).

As we go along, we’ll sometimes refer to the family members that in some way or another directly hold dates and/or times as the date/time siblings (these are `date`, `time`, `datetime` and `datetimezone`). The other type in family, `duration`, doesn’t hold either a date or a time, so we’ll just refer to it by its name. We can think of it as being a part of the extended family—say, a cousin, not a direct sibling.

Dates, Times & Durations

Date

Type `date` holds, well, can you guess? A date!

```#date(2018, 4, 27) // year, month, day - April 27, 2018
#date(3000, 12, 4) // December 4, 3000
```

Years between 1 and 9999 are supported. While `date` is great for a long way into the future, it not usable for dates Before Christ (B.C.).

```#date(-25, 6, 10) // not allowed - year can’t be before the first century AD
```

Time

Type `time` is used for time values (no surprise here!). Fractions of a second are supported, down to a 100-nanosecond level of precision.

```#time(11, 15, 25) // hh, mm, ss — 11:15:25 AM
#time(13, 0, 0) // 1:00:00 PM
#time(13, 0, 0.53257) // 1:00:00.5325700 PM
```

Keep in mind that a `time` value is different from a `duration` value (which we’ll talk about shortly). Type `time` represent a moment in time, a value that can be displayed on the face of a 24-hour clock. `duration` represents a quantity of time. A `time` of 1:00 AM represents, well, exactly that, 1:00 AM. In contrast, a `duration` of 1:00 represents the fact that one hour elapsed since the start—but without telling you when that start was.

`time`’s range is from the stroke of midnight through the stroke of midnight 24 hours later.

```#time( 0, 0, 0) // 12:00:00 AM (stroke of midnight)
#time(24, 0, 0) // 12:00:00 AM (stroke of midnight, 24 hours after starting)
```

Distinct Midnights?

Both 00:00 and 24:00 refer to midnight. Both refer to exactly the same point on the clock face. So, in one sense, they are one and the same.

However, from the human perspective, sometimes we want to differentiate between the two. We sometimes use 24:00 to refer to midnight when, from our perspective, it ends the day and 00:00 to refer to midnight when, again from our perspective, it begins the day. Say, you want to describe the fact that you worked from 10 pm to midnight using a 24-hour clock. You might say you worked from 22:00 to 24:00. On the other hand, if you wanted to say you worked from midnight to 2 AM, you’d probably say 00:00 to 02:00. In this sense, the times 00:00 and 24:00 are different.

So, 00:00 and 24:00 are both the same and different. This paradox spills over into how M handles the two.

In M, both `#time(0, 0, 0)` and `#time(24, 0, 0)` refer to the point on the clock face where hour = 0, minute = 0 and second = 0.

```Time.ToRecord(#time( 0, 0, 0)) // [Hour = 0, Minute = 0, Second = 0]
Time.ToRecord(#time(24, 0, 0)) // [Hour = 0, Minute = 0, Second = 0]
```

However, the two value are not equal and, when converted to numbers, return different values:

```#time( 0, 0, 0) = #time( 0, 0, 0) // true
#time( 0, 0, 0) = #time(24, 0, 0) // false
#time(24, 0, 0) = #time(24, 0, 0) // true
Number.From(#time( 0,0,0)) // 0
Number.From(#time(24,0,0)) // 1
```

In M, if you compare `time` values of 00:00 and 24:00, you’ll need to decide whether to treat them as equivalent or distinct and then use logic that performs the comparison the way you decided. If the two should be distinct, the equality operator will serve you well (as demonstrated by the previous example). If the two should be treated as equivalent, you’ll need to use more involved logic—perhaps something like:

```Time.ToRecord(#time( 0, 0, 0)) = Time.ToRecord(#time( 0, 0, 0)) // true
Time.ToRecord(#time( 0, 0, 0)) = Time.ToRecord(#time(24, 0, 0)) // true
Time.ToRecord(#time(24, 0, 0)) = Time.ToRecord(#time(24, 0, 0)) // true
```

DateTime

Combine the ideas of `date` and `time` together and you get type `datetime`.

```#datetime(2018, 4, 30, 15, 30, 15) // yyyy, mm, dd, hh, mm, ss -- April 30, 2018 3:30:15 PM
```

The time part of `datetime` differs in behavior from type `time` in one aspect. `time` supports the special time of 24 hours, 0 minutes, 0 seconds; `datetime` doesn’t. With `datetime`, to indicate a point in time exactly 24 hours after the start of a day, simply set the `datetime` to the start of the next day.

DateTimeZone

`datetimezone` takes the idea of `datetime` and adds a time zone to it. The time zone is defined as an offset of hours and minutes from UTC, not as a friendly name like “Eastern Standard Time” or “Australian Central Time.”

```#datetimezone(2018, 4, 30, 15, 30 ,15, 04, 00) // yyyy, mm, dd, hh, mm, ss, hours +/- UTC, minutes +/- UTC -- 4/30/2018 3:30:15 PM +04:00
```

Duration

`duration` represents a quantity of time. It doesn’t hold a specific date or time. Instead, it represents an amount of time that has elapsed or how much time remains.

For example, you might use a `duration` to represent the fact that a class is 2 hours and 15 minutes long:

```#duration(0, 2, 15, 0) // days, hours, minutes, seconds -- duration of 0d 2h 15m 0s
```

Like `time`, `duration` can store values down to a 100-nanosecond level of precision.

```#duration(0, 6, 50, 25.789) // 0d, 6h, 50m, 25.789s
```

Unlike `time`, a `duration` can be positive or negative. While `time` handles up to one day’s worth of time, a `duration` can last just over 10,675,199 days in either the positive or negative direction.

```#duration(2, 6, 5, 10) // 2d, 6h, 5m, 10s
#duration(-250, -12, -11, -5.5) // -(250d, 12h, 11m, 5.5s)
```

`duration` is flexible in converting between units of measure. For example, a `duration` initialized as 240 minutes is identical to a duration that’s initialized as 4 hours because duration knows that 60 minutes equals 1 hour.

```#duration(0, 4, 0, 0) // 4h
#duration(0, 0, 240, 0) // the resulting duration is identical to the previous because it represents the same quantity of time
```

When initializing, positive and negative argument values can even be combined to produce a `duration` equal to their sum:

```#duration(2, -24, 0, 0) // duration of 1 day ((2 days) +  (-24 hours) = 1 day)
#duration(0, 1, -5, 0) // duration of 55 minutes ((1 hour) + (-5 minutes) = 55 minutes)
```

Commonalities

Now that we’ve met the entire family—the date/time siblings (`date`, `time`, `datetime` and `datetimezone`) and their cousin `duration`—let’s look at behaviors and traits that are shared between more than one member of the family.

Combination

Use the combination operator between a `date` and a `time` and what do you end up with? Why, a `datetime`, of course!

```#date(2018, 4, 30) & #time(15, 30, 10) // datetime of April 30, 2018 3:30:10 PM
```

Handy when you want to combine a `date` column and a `time` column into a `datetime` column.

Conversion

Where it makes sense, date/time sibling types can be converted to other sibling types:

```DateTime.From(#date(2019, 12, 31)) // datetime of December 31, 2019 12:00.00 AM
```

Converting from a type with more information (like `datetime`) to one holding less (like `date` or `time`) results in the extra information being truncated.

```Date.From(#datetime(1000, 10, 15, 13, 25, 12)) // date of October 15, 1000
Time.From(#datetime(1000, 10, 15, 13, 25, 12)) // time of 1:25:12 PM
```

The above behavior comes in handy when working with a table column that contains a `datetime` (or `datetimezone`) where you only care about the date part or the time part. You simply convert the column’s type to `date` (or `time`) and the extraneous information is discarded.

When converting to `datetimezone`, M assumes that the input value is relative to system’s local time zone.

```DateTimeZone.From(#date(2019, 12, 31)) // December 31, 2019 12:00.00 AM with the timezone offset set to the offset of the system’s current timezone
DateTimeZone.From(#datetime(1000, 10, 15, 13, 25, 12)) // October 15, 1000 1:25.12 PM in local time zone
```

Similarly, converting from `datetimezone` to another date/time sibling uses the starting value’s time zone offset and the system’s local time zone to output a value that is relative to the system’s time zone.

For example, on a system with a timezone offset of -5 hours:

```DateTime.From(#datetimezone(2018, 5, 30, 0, 0, 0, 0, 0)) -- May 29, 2018 7:00:00 PM
```

Above, the value output is 5 hours earlier than the input because the output is relative to the local time zone offset and that offset is five hours earlier than the input’s offset.

Math

Where it makes sense, the arithmetic operators can be used with temporal values.

Add together a date/time sibling and `duration` and a result is a value that’s the same type as the date/time sibling type you started with, just with the `duration` added:

```#date(2018, 6, 1) + #duration(1, 2, 0, 0) = June 2, 2018
#time(13, 5, 25) + #duration(0, 0, 0, 35) = 1:06:00 PM
#datetime(1000, 10, 25, 6, 13, 0) + #duration(0, -6, 0, 0) = October 25, 1000 12:13:00 AM
#datetimezone(1000, 10, 25, 6, 13, 0, 0, 0) + #duration(0, -4, 0, 0) = October 25, 1000 2:13:00 AM +00:00
```

When you add a `time` and a `duration`, it may help to think of `time` as a 24-hour clock face and `duration` as spinning the hands on that clock face forward (or backward) the amount of  time specified by the `duration`.

```#time(22, 0, 0) + #duration(0, 4, 0, 0) = 2:00 AM
#time( 4, 0, 0) + #duration(2, 0, 0, 0) = 4:00 AM
```

Notice in the second example, the result is exactly the same value as the initial time. Why? Adding 2 days to the time of 4:00 AM causes `time`’s pretend clock hands to move forward 24 hours for the addition of the first day then 24 hours for the second day. After all that forward motion, the clock hands end up resting on exactly the same hour, minute and second where they started: 4:00 AM.

Since addition is commutative, whether the date/time sibling or `duration` comes first doesn’t matter.

Subtraction

`duration` can also be subtracted from date/time siblings. The returned value will be of the same type as the date/time sibling in the expression. This time, order matters: `duration` can be subtracted from a date/time sibling, but not the other way around.

```#time(13, 5, 25) - #duration(0, 0, 0, 25) = 1:05 pm
#duration(0, 0, 0, 25) - #time(13, 5, 25) // not allowed -- date/time sibling cannot be subtracted from duration.
```

If, instead of subtracting a `duration` from a date/time sibling, you subtract date/time sibling from another value of the same type, what do you end up with? Why, a `duration` describing the difference between the two values!

```#date(2018, 8, 10) - #date(2018, 8, 5) // duration of 5 days
#time(12, 0, 0) - #time(14, 0, 0) // duration of -2 hours
#datetimezone(2018, 10, 5, 16, 0, 0, 4, 0) - #datetimezone(2018, 10, 5, 15, 0, 0, -4, 0) // duration of -7 hours
```

In the last example, notice how the resulting `duration` properly accounted for the time zone offsets.

A `duration` can be added or subtracted from another `duration`.

```#duration(1, 5, 0, 0) + #duration(0, 0, 25, 0) // 1d 5h 25m
#duration(1, 5, 0, 0) - #duration(0, 0, 25, 0) // 1d 4h 35m
```

Multiplication & Division

Unlike the date/time siblings, `duration`s can even be multiplied and divided by numbers.

```#duration(1, 5, 0, 0) * 3 // 3d 15h 0m
#duration(1, 5, 0, 0) / 2.5 // 11h 36m
```

Since multiplication is commutative, the order of arguments doesn’t matter: `duration` then number and the other way around are both allowed.

```#duration(1, 5, 0, 0) * 3  = 3 * #duration(1, 5, 0, 0) // true
```

The same doesn’t hold true for division because with that operation, the order of arguments is significant.

String Formatting

Converting a temporal value to text produces a string using a default format.

```Text.From(#date(2010, 12, 31)) // 12/31/2010
Text.From(#time(6, 12, 31)) // 6:12 AM -- notice that the seconds component is not displayed
Text.From(#datetime(2010, 12, 31, 15, 16, 32)) // 12/31/2010 3:16:32 PM
Text.From(#datetimezone(2010, 12, 31, 15, 16, 32, -4, 15)) // 12/31/2010 3:16:32 PM -03:45
Text.From(#duration(15, 6, 3, 25.2)) // 15.06:03:25.2000000
```

The default formats used may vary depending on the system’s current culture setting. The text renderings shown in this section assumes a culture of en-US; if your system uses a different culture, your results may vary.

Sometimes, these default formats don’t cut it. If you’d like more control over what’s output, each temporal type provides a `ToText` method that accepts a format string.

Say you want to render a `date` as long month name followed by the 4-digit year (no day number) or maybe want to `time` that includes the first two digits of the seconds component (unlike the default rendering shown in the previous example):

```Date.ToText(#date(2010, 12, 31), "MMMM yyyy") // December 2010
Time.ToText(#time(12, 15, 18.253), "hh:mm:FF tt") // 12:15:25 PM
```

I haven’t seen official documentation defining the syntax for format strings. From the experimentation I’ve done, they seem mighty similar, if not identical, to what’s supported by the Microsoft .Net framework.

Some date/time format strings render differently depending on the culture (for example, the format string for month name will return values in English for a culture that uses English and in French for a culture that uses French). By default, format strings are rendered relative to the local system’s current culture . You can override this by specifying a culture identifier as the third argument to `ToText`.

```Date.ToText(#date(2010, 12, 25), "m", "en-US") // December 25
Date.ToText(#date(2010, 12, 31), "m", "fr-FR") // 31 décembre
Date.ToText(#date(2010, 12, 31), "m", "ja-JP") // 12月31日
```

(`Duration.ToText` doesn’t accept a culture argument. This makes sense since duration’s formatting options are culture-agnostic.)

One way to ensure that values are rendered consistently across systems is to indicate the reference culture to use, as we did above. Another option is to use a format string that is culture-agnostic. Format string “o” is one such string. Using it results in a datetime string being output in a format that stays the same regardless of the system’s current culture configuration.

```DateTime.ToText(#datetime(2018, 12, 25, 11, 50, 20), "o") // 2018-12-25T11:50:20.0000000
```

OLE Automation Date (OADate) Values

All of M’s temporal types can be created from OLE Automation Date (OADate) values. Tools like Microsoft Excel use this format to encode dates and times, so it’s most handy that M can work with it.

An OADate uses a single number to indicate how many days, including fractions of a day, it is ahead or behind a reference point. An OADate of 2.5 represents a point in time two and a half days after the reference point while -15.75 indicates fifteen and three-quarter days before the reference point.

For `date`, `datetime` and `datetimezone`, the reference point is start of day December 30, 1899.

```Date.From(2.75) // January 1, 1900
DateTime.From(2.75) // January 1, 1900 6:00 PM
```

Notice above how date ignores the fractional part of the OADate—in effect, it’s truncated off. Since `date` doesn’t know anything about time, this makes sense.

Also, in the case of `datetimezone`, OADates are always interpreted as relative to the system’s current time zone (which, in the below example, has an offset of -0500).

```DateTimeZone.From(2.75) // January 1, 1900 6:00 PM -0500
```

For `time`, the reference point of 0 represents 12:00 AM. Time supports OADates from 0 up to (but not including) 1.

```Time.From(0.75) // 6:00 pm
```

Since an OADate of 1 isn’t a valid input for `time`, it’s not possible to create the equivalent of `#time(24, 0, 0)` using an OADate.

```Time.From(1) // not allowed
```

`duration`’s 0 reference point is the start of the duration. A positive value includes a positive duration and a negative value indicates (not surprisingly) a negative duration.

```Duration.From(1052.252) // 1,052d 6h 2m 52.8s
Duration.From(-10) // -10d
```

All of the temporal types can be converted to an OADate value using `Number.From`.

```Number.From(#date(2015, 12, 25)) // 42363
Number.From(#time(11, 32, 18.5)) // 0.4807696759259259
Number.From(#datetime(2015, 12, 25, 11, 32, 18.5)) // 42363.480769675924
Number.From(#datetimezone(2015, 12, 25, 11, 32, 18.5, -4, 0)) // 42363.397436342595
Number.From(#duration(35, 10, 15, 25.2)) // 35.427375
```

As we’ve seen before, when `datetimezone` is converted to a value that doesn’t contain a time zone offset, the outputted value is adjusted to be relative to the local system’s time zone.

Conclusion

Did you have any idea there could be so much to say about temporal values?! Hopefully, you now have a good grasp of the functionality supported by this family so that you can fully leverage it in the M code you write. Thankfully, except for the `time` 00:00 vs. 24:00 paradox, these types are mostly straightforward to work with.

Next time, let’s see if we can get through the remainder of the primitive types (like `null` and `logical`).

2 thoughts on “Power Query M Primer (Part 8): Types—The Temporal Family”

1. Tom Robinson

You might point out that the date/time combination operator ‘&’ isn’t concatenating string representations, and that these are equivalent:
#date(2018, 4, 30) & #time(15, 30, 10)
#time(15, 30, 10) & #date(2018, 4, 30)

1. Ben Gribaudo Post author

Good point! A number of the operators mentioned above can have their operands in either order. For example (date) + (duration) or vice versa.