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.

Series Index

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.

Addition

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, durations 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”

Tom Robinson October 22, 2020 at 11:36 am

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)

Reply ↓
1. Ben Gribaudo Post authorOctober 29, 2020 at 6:04 am
  
  Good point! A number of the operators mentioned above can have their operands in either order. For example (date) + (duration) or vice versa.
  
  Reply ↓