New Bug Encountered: Duration format changes on save

[David_Hoffman]

My app has a duration field called “FirstBreakDuration” with appsheet data type DURATION for when someone takes a break we can record the duration taken.

When the user enters into the form the duration as 00:30 and then saves, the value is changed to 0 00:30:00.000000. Once this happens any equations that I have using the TOTALHOURS() function do not work after the save.

I am using mysql as the app data source and the field is set to VARCHAR(20) for the mysql datatype. Has anyone experience this before?

[Marc_Dillon]

Not sure about that, but I have a suggestion of a different way to build this part of your app. Give a Number type input field for “Duration of first break (in minutes)”. Then use an expression to convert that into the Duration in another column. This makes it an easier value for a user to input, and then gives you validation control so that the duration value is generated correctly.

[David_Hoffman]

Hi @Marc_Dillon, updated the solution as @LeventK has shown that MySQL can use TIME as the right data type for DURATION in AppSheet. Formatting comes through correctly now. Thanks for your solution though.

[David_Hoffman]

I will check that now. Thanks @Marc_Dillon

[David_Hoffman]

Hi @Marc_Dillon, your solution was a good work around. Thanks for your help. Still doesn’t answer why the value keeps getting changed though…

[Marc_Dillon]

David_Hoffman:

When the user enters into the form the duration as 00:30 and then saves

Does it happen when it is entered as “000:30:00” ?

Probably should have asked that first…

[David_Hoffman]

Yes it does. I think it might be the way that Mysql was storing the information.

[Steve]

I encourage you to engage support@appsheet.com on this. It might be a bug.

[David_Hoffman]

Have submitted to Support for a check. Thanks.

[LeventK · Accepted solution]

David_Hoffman:

I am using mysql as the app data source and the field is set to VARCHAR(20) for the mysql datatype. Has anyone experience this before?

You are using completely a wrong SQL data time for your purpose. VARCHAR is for text type and you should be using TIME data type instead.

dev.mysql.com

MySQL :: MySQL 8.0 Reference Manual :: 11.2.3 The TIME Type

With VARCHAR(20) all you will be seeing is absolutely this:

I may also advise checking this post from StackOverflow:

stackoverflow.com

MySQL storing duration time - datatype?

mysql, time, types, duration

asked by Juds on 10:55AM - 21 Dec 10 UTC

[David_Hoffman]

Just updated the field to time in MySQL and duration in AppSheet. This worked. Thanks @LeventK.

[LeventK]

You’re welcome @David_Hoffman, my pleasure to be helped of.