cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BrianHFASPS
Responsive Resident
Responsive Resident

Dealing with Time from SQL and doing comparisons

I am have built a sign-in / out app for childcare. Depending on when you sign-out you may get charged extra. I have (maybe mistakenly) saved the in/out times to SQL in time(7) fields. When I look at the time in SQL Management Studio it looks great but in PowerApps I end up using this little thing:

First(ForAll(MatchAll(Text(SignoutTime),"PT(?:(?<hours>\d+)H)?(?:(?<minutes>\d+)M)?(?:(?<seconds>\d+)S)?"),Time(Value(hours),Value(minutes),Value(seconds)))).Value

Which works well for showing it in a readable format but doesn't help for mathmatical comparions. I have begun doing something like:

First(MatchAll(Text("PT18H5M2S"),"PT(?:(?<hours>\d+)H)?(?:(?<minutes>\d+)M)?(?:(?<seconds>\d+)S)?")).hours

To get hours or mintues out. I just want to make sure there isn't a "better" way to be doing all this. I seem to be wasting a lot of time converting stuff that maybe I don't have to? Any suggestions on better ways to do this?

1 ACCEPTED SOLUTION

Accepted Solutions

Ah, understood. Yes, there is no easier way today to understand time columns in SQL Server; this is in our backlog, please vote up the feature request at https://powerusers.microsoft.com/t5/PowerApps-Ideas/integration-of-SQL-time-columns-in-PowerApps/idi... to help increasing the priority of the work.

View solution in original post

4 REPLIES 4
CarlosFigueira
Power Apps
Power Apps

If you want to compare time values, you can "subtract" two of the values, and you'll get the result in milliseconds. For example, if you have a label with the following expression in your gallery (assuming that the table/view where 'SignoutTime' comes from is the source of the gallery):

 

(
    First(
        ForAll(
            MatchAll(
                Text(SignoutTime),
                "PT(?:(?<hours>\d+)H)?(?:(?<minutes>\d+)M)?(?:(?<seconds>\d+)S)?"),
            Time(Value(hours), Value(minutes), Value(seconds)))).Value -
    Time(18, 0, 0)
) / (60 * 1000)

That will give you the number of minutes between 6:00PM (18:00) and the SignoutTime value - I'm using here 6:00PM as the time after which you want to charge extra. If that value is positive, then SignoutTime is after 6PM, and an extra charge would be applied.

 

For example, if the expression above is in the Text property of a (possibly hidden) label called 'lblDiffMinutes', then you can have this expression to calculate the extra charge (in this example, each extra minute costs $5):

 

If(Value(lblDiffMinutes.Text) <= 0, 0, 5 * Value(lblDiffMinutes.Text))

Hope this helps!

 

Thanks for the reply, this is what I came up with:

If(
    Value(
        First(
            MatchAll(
                Text(SignoutTime),
                "PT(?:(?<hours>\d+)H)?(?:(?<minutes>\d+)M)?(?:(?<seconds>\d+)S)?"
            )
        ).hours
    ) >= 18 && Value(
        First(
            MatchAll(
                Text(SignoutTime),
                "PT(?:(?<hours>\d+)H)?(?:(?<minutes>\d+)M)?(?:(?<seconds>\d+)S)?"
            )
        ).minutes
    ) > 00,
    Value(
        First(
            MatchAll(
                Text(SignoutTime),
                "PT(?:(?<hours>\d+)H)?(?:(?<minutes>\d+)M)?(?:(?<seconds>\d+)S)?"
            )
        ).minutes
    ) * 1.5,
    0
)

I am not assigning it to variables but I think the end result is similar to your suggestion. My main question was if there was some way to do it without the whole MatchAll stuff but it seems for now the answer is no.

Ah, understood. Yes, there is no easier way today to understand time columns in SQL Server; this is in our backlog, please vote up the feature request at https://powerusers.microsoft.com/t5/PowerApps-Ideas/integration-of-SQL-time-columns-in-PowerApps/idi... to help increasing the priority of the work.

View solution in original post

I will add the other workaround I have found is bringing the SQL data into a local collection and then date comparisons work more easily. Since you can't use date comparisons in delegation currently it doesn't really change that. You have convert date to text both in SQL and PowerApps to do delegated comparion.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (60,639)