Data type for Duration in SQL

[Bellave_Jayaram]

I used a VARCHAR(10) data type for a duration column. Now, I am getting this error :

Unable to add/edit/delete row in table ‘Schedule’. → One or more SQL errors has been encountered: One or more SQL errors has been encountered:
The insert/update operation attempted to write data that was too large for a column in your MySQL table.
Original error message from the database: Data too long for column ‘TotalWorkDuration’ at row 1

I am not seeing what the issue here is when duration is only 8 characters such as “00:10:45”?

What is the recommended datatype in SQL for a duration column?

Thanks,
Jayaram

[LeventK]

@Bellave_Jayaram
For a multiplier not involved with price, you can use FLOAT. I may advise working in seconds (or minutes if you prefer). That can be in INT UNSIGNED. You can use any one of appropriate DATETIME functions to convert seconds/minutes to hh:mm or whatever output you desire. However, I believe the most efficient way could be using a DATETIME(3) or DATETIME(6) setting and then use a DATEDIFF expression to calculate the duration. I may also propose checking this page as well:
https://www.sqlteam.com/articles/working-with-time-spans-and-durations-in-sql-server

[Bellave_Jayaram]

Thanks @LeventK!!

I find it easier to work with minutes in AppSheet formulas than Date functions in SQL so, I came up with this.

The app I am working on needs to keep track of PauseDuration which should be ignored in calculating the TotalWorkDuration. As there could be multiple breaks in a single job, I am maintaining the PauseDuration as an Enumlist of minutes. Whenever the job resumes from a Pause, I update the PauseDuration using a formula such as:

SPLIT(ANY(SELECT(Schedule[PauseDuration],[WorkOrder]=[_THISROW].[WorkOrder])))+LIST(HOUR([ResumeTime]-[PauseTime])*60+MINUTE([ResumeTime]-[PauseTime]))

where PauseTime is the timestamp when the job was paused and ResumeTime is the timestamp when the job was resumed.

So, TotalWorkDuration in minutes will be something like:

HOUR([EndTime]-[StartTime])*60+MINUTE([EndTime]-[StartTime]) - SUM([PauseDuration])
or
HOUR([EndTime]-[StartTime])*60+MINUTE([EndTime]-[StartTime]) - SUM(SPLIT([PauseDuration]))

Now, minutes can be used/converted/displayed as required.

[VAN_KENNETH_Aqu]

hi @Bellave_Jayaram. in order to use SUM, [PauseDuration] must be of Base Type Number.
however, it only lists up to two numbers. any workaround?

[Bellave_Jayaram]

There should be no such limitation from AppSheet - must be something about the list formula or data that limits the list length.

[VAN_KENNETH_Aqu]

@Bellave_Jayaram
It becomes NaN (not a number) after listing 2 numbers.

SPLIT((SELECT(BET[On Hold Duration],[BET Title]=[_THISROW].[BET Title])))+LIST(HOUR([On Hold Stop]-[On Hold Start])*0.04167) – this returns no. of days each time I update On Hold Start/Stop. Pretty much the same as your formula above. This has Enumlist type and Base type Number.

[Bellave_Jayaram]

You need to take care that the value isn’t NaN. If you want to share the app with me, jayaram@able3ventures.com, I can take a look.