Working with Time columns in SQL Server
This post shows some expressions that we needed to use to consume SQL Server time columns – and it was fairly complex. We have since released a new feature in PowerApps that makes this easier. Take a look at the announcement for the new regular expression functions for more information.
When we use time columns in SQL Server and try to use them in PowerApps, we don’t get a time value that we can use directly – instead, we get a text (string) type, with an encoding of the time that is not very user-friendly:
This is currently a missing feature in PowerApps – time columns should have a better support (since there is a Time type in PowerApps), so while this is not addressed, I’ll show in this post a way to work with the time columns today.
The problem
A few months ago I wrote about working with date/time values in SQL, as there are a couple of different ways to represent those values. Time columns have a similar issue – they can represent two different concepts:
- A certain time in day: school starts at 08:25, ends at 14:55; tea will be served at 17:00.
- A duration of an event: NBA basketball quarters last for 12 minutes; the current marathon record is 2 hours, 1 minute and 29 seconds.
The SQL time column type can be used for both concepts, so we don’t have a semantic problem this time. Instead, the issue arises because of a mismatch between the SQL connector and PowerApps – the former transmits time values as durations (in the ISO 8601 duration format), while the latter only accepts absolute time values over the wire. This issue will likely be addressed in an upcoming release, but since we may have apps that rely on the current behavior (time columns returned as string values), we can’t just change it to return time values or those apps would be broken.
Converting between the duration format and a time value
There’s currently no built-in function that can be used to convert the duration format and a time value in PowerApps, so we’ll use some string manipulation functions for that. If all you want from this post is an expression that does that, then here you go (this assumes that the time value can be referenced as ThisItem.Time):
Time(
If(
IsBlank(Find("H", ThisItem.Time)), // Check if there is an hour component
0, // If not, the value is zero
Value( // Otherwise, take the substring between 'PT' and 'H'
Mid(
ThisItem.Time,
3,
Find("H", ThisItem.Time) - 3))),
If(
IsBlank(Find("M", ThisItem.Time)), // Check if there is a minute component
0, // If not, the value is zero
Value( // Otherwise take the substring between the rest of the value
Mid( // after the hour component and the 'M' indicator
ThisItem.Time,
If(
IsBlank(Find("H", ThisItem.Time)),
3,
Find("H", ThisItem.Time) + 1),
Find("M", ThisItem.Time) -
If(
IsBlank(Find("H", ThisItem.Time)),
3,
Find("H", ThisItem.Time) + 1)))),
If(
IsBlank(Find("S", ThisItem.Time)), // Check if there is a second component
0, // If not, the value is zero
Value( // Otherwise take the substring after the minute or hour indicator,
Substitute( // remove the 'S' indicator, then take the value
Mid(
ThisItem.Time,
If(
!IsBlank(Find("M", ThisItem.Time)),
Find("M", ThisItem.Time) + 1,
!IsBlank(Find("H", ThisItem.Time)),
Find("H", ThisItem.Time) + 1,
3)),
"S",
""))))
Or if you want to display it in a certain format, you can use it inside a Text expression that converts it to what you want, such as
Text(<the big expression above>, DateTimeFormat.LongTime24)
Drilling down the above expression
If you want to go deeper, let’s go over that expression, and maybe you’ll be able to use some of the concepts for another scenario. When trying to parse this kind of data, it’s always good to find some examples of data on that format, so that we can keep an eye for special cases that may arise. After populating a sample SQL table with some of those values and looking at how they’re represented in PowerApps, here are some examples of the types of values that we’ll need to parse:
- PT2H1M39S (2:01:39)
- PT12H (12:00:00)
- PT2H2S (2:00:02)
- PT58M18S (0:58:18)
- PT34S (0:00:34)
- PT5M (0:05:00)
The value always starts with the ‘PT’ (period / time) identifier, followed by non-zero components of the period (hour / minute / second). Notice that the components are not always present – that makes breaking down the parts a little harder, for example, as we cannot rely on the presence of a ‘H’ and a ‘M’ to find the minute component between them.
We start then by finding the hour. There are two cases here: there is an hour component (in case we find a ‘H’ character in the string) or not. If not, the value is simple: 0. Otherwise, we can use the Mid function to take the value from the 3rd character (after ‘PT’) and the ‘H’ marker:
If(
IsBlank(Find("H", ThisItem.Time)), // Check if there is an hour component
0, // If not, the value is zero
Value( // Otherwise, take the substring between 'PT' and 'H'
Mid(
ThisItem.Time,
3,
Find("H", ThisItem.Time) - 3))) For the minute, there are now three cases: there is no minute component (simple, value is zero). But if there is a minute component, then there may be an hour component or not, so we need to account for both cases. If there is an hour component, then we need to account for it when calculating the indices for taking the substring from the duration format.
If(
IsBlank(Find("M", ThisItem.Time)), // Check if there is a minute component
0, // If not, the value is zero
Value( // Otherwise take the substring between the rest of the value
Mid( // after the hour component and the 'M' indicator
ThisItem.Time,
If(
IsBlank(Find("H", ThisItem.Time)), // If there is no hour component,
3, // Start right after 'PT'
Find("H", ThisItem.Time) + 1), // Else start after the hour component
Find("M", ThisItem.Time) -
If(
IsBlank(Find("H", ThisItem.Time)),
3,
Find("H", ThisItem.Time) + 1))))
To get the seconds component, we could do something similar to the minutes – take a substring from the minute component (if it exists), or the hour component (again, if it exists), or the ‘PT’ identifier. Using the Mid function in this way starts to get quite long, so another alternative is to first remove everything that is not the second component, remove the ‘S’ identifier (using the Substitute function), then taking the value of it:
If(
IsBlank(Find("S", ThisItem.Time)), // Check if there is a second component
0, // If not, the value is zero
Value( // Otherwise take the substring after the minute or hour indicator,
Substitute( // remove the 'S' indicator, then take the value
Mid(
ThisItem.Time,
If(
!IsBlank(Find("M", ThisItem.Time)), // If there is a minute component
Find("M", ThisItem.Time) + 1, // Then start right after it
!IsBlank(Find("H", ThisItem.Time)), // Else if there is an hour component
Find("H", ThisItem.Time) + 1, // Then start after it
3)), // Else start after 'PT'
"S",
"")))
Hopefully that helped clarify a little about the structure of this (quite large) expression. We’ll try to make this simpler in the future, but that’s something that can be used today.
Using time values in forms
The (quite large) expression from the previous section can be used to display the proper time value in a gallery, for example, but how would it work in forms?
On display forms it’s fairly straightforward, we can update the Default property of the data card that displays the time value so that it is formatted as we want. For example, we can use the following expression to display the time in hours:minutes:seconds format (again, assuming that the column name is ‘Time’):
Text(
Time(
If(
IsBlank(Find("H", ThisItem.Time)), // Check if there is an hour component
0, // If not, the value is zero
Value( // Otherwise, take the substring between 'PT' and 'H'
Mid(
ThisItem.Time,
3,
Find("H", ThisItem.Time) - 3))),
If(
IsBlank(Find("M", ThisItem.Time)), // Check if there is a minute component
0, // If not, the value is zero
Value( // Otherwise take the substring between the rest of the value
Mid( // after the hour component and the 'M' indicator
ThisItem.Time,
If(
IsBlank(Find("H", ThisItem.Time)),
3,
Find("H", ThisItem.Time) + 1),
Find("M", ThisItem.Time) -
If(
IsBlank(Find("H", ThisItem.Time)),
3,
Find("H", ThisItem.Time) + 1)))),
If(
IsBlank(Find("S", ThisItem.Time)), // Check if there is a second component
0, // If not, the value is zero
Value( // Otherwise take the substring after the minute or hour indicator,
Substitute( // remove the 'S' indicator, then take the value
Mid(
ThisItem.Time,
If(
!IsBlank(Find("M", ThisItem.Time)),
Find("M", ThisItem.Time) + 1,
!IsBlank(Find("H", ThisItem.Time)),
Find("H", ThisItem.Time) + 1,
3)),
"S",
"")))),
"hh:mm:ss")
For edit forms, if we want to let the user edit the time value in a text input control, then the expression above will work fine – it will display the editable text in the hh:mm:ss format which can be updated by the user. Writing the value back is not a problem – the SQL connector accepts the data in that format when writing back to the server.
But like with a date column where we have a date picker, we can use a “time picker” as well, with a trio of dropdown values that can be used to select the time in a more user-friendly way:
To implement that, we can add three dropdown controls (I’ll call them ‘ddHour’, ‘ddMinute’ and ‘ddSecond’ here) to the data card corresponding to the time column, and set their properties as follows:
ddHour.Items: ["00","01","02","03","04","05","06","07","08","09","10","11","12","13","14","15","16","17","18","19","20","21","22","23"] ddMinute.Items: ["00","01","02","03","04","05","06","07","08","09","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","31","32","33","34","35","36","37","38","39","40","41","42","43","44","45","46","47","48","49","50","51","52","53","54","55","56","57","58","59"] ddSecond.Items: ["00","01","02","03","04","05","06","07","08","09","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","31","32","33","34","35","36","37","38","39","40","41","42","43","44","45","46","47","48","49","50","51","52","53","54","55","56","57","58","59"] ddHour.Default: Left(Parent.Default, 2) ddMinute.Default: Mid(Parent.Default, 4, 2) ddSecond.Default: Mid(Parent.Default, 7, 2)
And then set the Update property in the time data card to
ddHour.Selected.Value & ":" & ddMinute.Selected.Value & ":" & ddSecond.Selected.Value
And that closes the circle, allowing us to update the time values back to the server.
Wrapping up
The expressions above can be used to better work with time columns in a SQL Server database. Currently it’s not as straightforward as it can be, but it can be done. As we get more requests for it, we can prioritize it appropriately and make improvements to the product to make this scenario simpler. If you feel that this is important, please vote up on this feature request on the PowerApps Ideas board.