Skip to main content

Working with Time columns in SQL Server

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:

App showing time columns from SQL Server in the duration ISO 8601 format

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")

Updated display form for showing time columns

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.

Updated edit form for showing time columns

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:

Updated display form for showing time columns using dropdown controls

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.