How to Store ISO 8601 Durations in Databases

A practical guide for developers and database administrators

1. The Challenge of Storing ISO 8601 Durations in Databases

When working with ISO 8601 duration strings like P1Y2M3DT4H5M6S, a common question arises: what is the best way to store them in a database? Unlike simple numeric values, duration strings present unique storage challenges that require careful consideration.

This can be a significant challenge when working with APIs, data imports, or applications that use this standard format. This guide provides practical approaches and best practices for storing ISO 8601 durations in your database.

2. Getting Started: Store as a String

The simplest approach is to store the duration as a string in a VARCHAR or TEXT field. This preserves the original ISO 8601 format exactly as received.

Here's how you would create a table and insert duration data:

-- Create a table with a VARCHAR field for duration
CREATE TABLE tasks (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    duration VARCHAR(50)  -- Store ISO 8601 duration as string
);

-- Insert duration data
INSERT INTO tasks (id, name, duration) VALUES 
(1, 'Project Planning', 'P1DT8H'),
(2, 'Code Review', 'PT2H30M'),
(3, 'Testing Phase', 'P3DT12H');

Pros: Simple to implement, human-readable, and preserves the original format exactly.

Cons: Difficult to perform calculations or queries on the duration components. You can't easily ask for "all records with a duration greater than 10 hours" without parsing the string first.

3. Store as an Integer (Total Seconds)

A more flexible approach is to convert the duration to a fixed unit, such as seconds, and store it as an INTEGER. This enables powerful database operations and queries.

Here's how you would implement this approach:

-- Create a table with INTEGER field for duration in seconds
CREATE TABLE tasks (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    duration_seconds INT  -- Store duration as total seconds
);

-- Insert converted duration data
INSERT INTO tasks (id, name, duration_seconds) VALUES 
(1, 'Project Planning', 115200),  -- P1DT8H = 1 day + 8 hours = 115200 seconds
(2, 'Code Review', 9000),         -- PT2H30M = 2.5 hours = 9000 seconds
(3, 'Testing Phase', 302400);     -- P3DT12H = 3 days + 12 hours = 302400 seconds

-- Now you can easily query by duration
SELECT * FROM tasks WHERE duration_seconds > 3600;  -- Tasks longer than 1 hour
SELECT AVG(duration_seconds) FROM tasks;            -- Average duration
SELECT SUM(duration_seconds) FROM tasks;            -- Total duration

Pros: Easy to perform calculations, queries, and aggregations. You can easily find all records with a duration greater than a certain value.

Cons: This approach loses the original context, especially for durations involving months and years, which have variable lengths. A duration of P1M (one month) cannot be accurately represented as a fixed number of seconds without a start date.

4. The Database-Native Approach: Using the INTERVAL Type

For databases like PostgreSQL, there is a native INTERVAL data type designed specifically for this purpose. This is the most powerful and integrated approach, as it allows the database itself to understand the duration.

-- Create a table using the native INTERVAL type
CREATE TABLE scheduled_events (
    event_name TEXT,
    event_start TIMESTAMPTZ,
    duration INTERVAL
);

-- You can insert ISO 8601 strings directly
INSERT INTO scheduled_events (event_name, event_start, duration)
VALUES ('System Maintenance', '2025-10-15 22:00:00 UTC', 'P4DT8H');

-- The magic: Perform date arithmetic directly in your SQL query
SELECT
    event_name,
    event_start,
    duration,
    event_start + duration AS event_end
FROM scheduled_events;

Pros: Enables powerful, direct date/time calculations in SQL. Enforces data integrity at the database level.

Cons: Can be less portable as the syntax and features may differ between database systems. For more details, you can consult the official PostgreSQL documentation.

5. The Verdict: When to Use Which Method

After reviewing the options, the best approach for most applications is a hybrid model. By storing both the original string and a calculated value (like total seconds or a native INTERVAL), you get the maximum flexibility.

  • Store the VARCHAR string: To preserve the original, unambiguous format for auditing and display.
  • Store the INTEGER seconds OR the INTERVAL type: To enable fast, indexed calculations and queries.

This hybrid approach, while requiring slightly more storage, provides the best of all worlds: human-readable original data and powerful database operations.

Here's how to implement the hybrid approach:

-- Create a table with both string and numeric duration fields
CREATE TABLE tasks (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    duration_string VARCHAR(50),    -- Original ISO 8601 string
    duration_seconds INT,           -- Converted to seconds for calculations
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert data with both formats
INSERT INTO tasks (id, name, duration_string, duration_seconds) VALUES 
(1, 'Project Planning', 'P1DT8H', 115200),
(2, 'Code Review', 'PT2H30M', 9000),
(3, 'Testing Phase', 'P3DT12H', 302400);

-- You can now query using either field
SELECT name, duration_string FROM tasks WHERE duration_seconds > 3600;
SELECT AVG(duration_seconds) FROM tasks;
SELECT duration_string, duration_seconds FROM tasks ORDER BY duration_seconds DESC;

This approach gives you the best of both worlds: human-readable original format and powerful database operations.

6. Next Steps and Resources

The choice of how to store ISO 8601 durations depends on your specific use case and requirements. For comprehensive handling and conversion, we recommend using our online tools: