PostgreSQL interval and Go: The Runtime Trap Nobody Warns You About

Mulaimu00 — Sun, 09 Aug 2026 11:51:04 +0000

While building a Go application backed by PostgreSQL, I made a database design decision that seemed perfectly reasonable until it wasn't. This is the story of that mistake, why it happened, and the right way to handle durations across PostgreSQL and Go.

The Setup

I needed a table to store items with a duration field how long something lasts. Simple enough. My first instinct was to use PostgreSQL's interval type:

CREATE TABLE packages (
    id         bigserial PRIMARY KEY,
    name       text NOT NULL,
    duration   interval NOT NULL,
    price      integer NOT NULL,
    active     boolean DEFAULT true,
    created_at timestamptz NOT NULL DEFAULT now()
);

And in Go, I mapped it to time.Duration:

type Package struct {
    ID        int64
    Name      string
    Duration  time.Duration
    Price     int64
    Active    bool
    CreatedAt time.Time
}

This looks correct. PostgreSQL has a native duration type. Go has a native duration type. They should map cleanly, right?

The Runtime Surprise

Everything compiled. The server started. Then I hit the endpoint that queried the table:

sql: Scan error on column index 2, name "duration": converting driver.Value type []uint8 ("01:00:00") to int64: invalid syntax

The Go PostgreSQL driver (lib/pq) has no built-in conversion between PostgreSQL interval and Go time.Duration. The driver receives the interval as raw bytes ("01:00:00") and tries to stuff it into an int64 which is what time.Duration is under the hood. It fails silently at runtime, not at compile time.

This is the dangerous kind of bug. It compiles perfectly. It only surfaces when real data flows through the system.

Why This Happens

PostgreSQL's interval is a complex type. It can represent:

"1 hour" → 01:00:00
"24 hours" → 24:00:00
"1 day" → 1 day
"1 month" → ambiguous (30 days? 31 days?)

Go's time.Duration is simply an int64 counting nanoseconds. There is no universal, lossless conversion between these two representations especially for month/year intervals. So lib/pq doesn't try.

The Right Solution

Store duration as integer seconds in PostgreSQL:

ALTER TABLE packages
ALTER COLUMN duration TYPE integer
USING EXTRACT(EPOCH FROM duration)::integer;

Update the Go model:

type Package struct {
    ID        int64
    Name      string
    Duration  int64 // seconds
    Price     int64
    Active    bool
    CreatedAt time.Time
}

Now 1 hour = 3600, 24 hours = 86400. Dead simple.

Why Integer Seconds Is The Right Call

Arithmetic is trivial:

expiresAt := startedAt.Add(
    time.Duration(seconds) * time.Second,
)

No parsing ambiguity. "01:00:00", "1 hour", "3600 seconds" are all valid PostgreSQL intervals. Integer seconds has exactly one representation.

Industry standard. Stripe stores subscription intervals as integers. Unix timestamps are integers. The payments and infrastructure world converged on integers for time values decades ago for good reason.

Easy to display:

hours := seconds / 3600
minutes := (seconds % 3600) / 60

The migration is safe. EXTRACT(EPOCH FROM duration) converts interval to seconds with no data loss for hour/day durations.

The Broader Lesson

This bug exposed a general principle: don't assume that similar concepts in two systems map cleanly to each other.

PostgreSQL interval and Go time.Duration are both "duration types" conceptually. But they live in different layers of the stack — database and application — with a driver in between that has to translate between them. When that translation isn't implemented, you get a runtime error instead of a compile error.

The safe approach: use primitive types at database boundaries. Integers, strings, booleans. Let the application layer handle the semantics.

Building in Go, learning in public. Follow along for more.