Question about storing Time durations in DB

General
1

Hello,

I’m developing an application where I need to record every working day the time spent on specific tasks, such as 10 minutes, 60 minutes, 1 hour, 4 hours, etc.

I’ve created a table with 2 columns:
-timeEstimated: the estimated time for completing the task.
-timeExecuted: the total time spent on a task.

image
image789×201 12.1 KB

Could you advise me on the best data type for these columns?

  • Should I use “Number” and store the durations in minutes (e.g., 10 for 10 minutes, 60 for 1 hour)?
  • Should I use “String” to store it in a readable format (e.g., “10m”, “1h”, or “01:00:00”)?
  • Or would a “DateTime” data type be a better approach?

I’d like to know which approach is more suitable or if there’s a better way to handle this in Backendless.

Thank you for your guidance!

Regards,
Manuel

2

Hi @Manuel_Germano,

1. Number (e.g., INT or DECIMAL)

Storing the durations in minutes as a numeric value is often the best choice for time tracking.

Pros:

  • Simple for calculations: Easy to perform arithmetic operations (sum, average, etc.).

  • Efficient: Numeric types are faster and take up less storage compared to strings or complex types.

  • Flexible: You can convert it to any time format for display purposes (e.g., 60 → “1h”).

  • Easy comparison: Compare estimated vs. executed time with simple queries.

Cons:

  • Not immediately “human-readable” (e.g., “60” instead of “1h”).

2. String (e.g., VARCHAR)

Storing durations as strings like "10m", "1h", or "01:00:00" might feel readable but comes with limitations.

Pros:

  • Human-readable: Easy to display as is without conversion.

Cons:

  • Harder to calculate: You can’t directly sum or compare string values. For example, "10m" + "1h" won’t work.
  • Error-prone: Risk of inconsistent formatting (“1h”, “60m”, “01:00:00”, etc.) leading to data integrity issues.

3. DateTime

Using a DateTime type (e.g., “01:00:00” ) might seem reasonable for representing durations.

Pros:

  • Readable format: Displays durations in a time-like format (e.g., 01:00:00 for 1 hour).

Cons:

  • Ambiguity: DateTime usually represents specific timestamps, not durations.
  • Complex operations: Calculations or comparisons might require additional logic (e.g., converting to seconds).
  • Less intuitive: Using DateTime or TIME for durations isn’t common and can confuse developers/maintainers.

Recommendation

For your use case:

  • Use Number (e.g., INT) to store durations in minutes.
  • It’s simple, efficient, and makes calculations straightforward (e.g., totaling all task times).
  • You can always format or convert the value when displaying it to the user (e.g., 60 → "1h" or 75 → "1h 15m").

Regards,
Bohdan

3

Hello @Bohdan_Vynarchuk,
Perfect!
Thank you!
Regards,
Manuel