Data type for Duration in SQL

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

@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

1 Like

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.

2 Likes

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?

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

@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.

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.