Skip to content
orm
ENRU

Dates and intervals

Truncating, extracting, and an interval type that refuses to lie about months.

Interval is not a Duration#

time.Duration is a count of nanoseconds. A PostgreSQL interval is three independent parts — months, days and microseconds — and it keeps them apart because they are not convertible:

  • a month is 28 to 31 days
  • a day is 23, 24 or 25 hours across a daylight-saving boundary
go
iv := orm.IntervalOf(months, days, micros)
iv := orm.IntervalFromDuration(90 * time.Minute) // no months, no days

Converting back only works when there is nothing calendar-shaped in it:

go
d, err := iv.Duration()
if errors.Is(err, orm.ErrCalendarInterval) {
    // months or days are present; what they mean depends on when they start,
    // and the library will not pick 30 days on your behalf
}

That error is the whole design. A library that silently returned 720 hours for one month would be right most of the time and wrong at every month boundary.

Arithmetic#

go
orm.AddInterval(Events.At, orm.Val(iv))   // timestamp + interval
orm.SubInterval(Events.At, orm.Val(iv))
orm.IntervalPlus(a, b)                    // interval + interval
orm.IntervalMinus(a, b)
orm.IntervalTimes(a, 3)                   // interval * n

Each has a …Null form for nullable inputs, because arithmetic with NULL is NULL.

Truncating#

go
orm.DateTrunc(orm.Month, Events.At)   // date_trunc('month', at)
orm.DateTrunc(orm.Day, Events.At)
orm.DateTrunc(orm.Hour, Events.At)

The classic use is grouping a time series into buckets:

go
bucket := orm.DateTrunc(orm.Day, Events.At)

var perDay = orm.Project2(
    bucket, orm.Count[Event](),
    func(day time.Time, n int64) Bucket { return Bucket{day, n} },
)

orm.Select(db.Events, perDay).
    Where(Events.At.Gte(since)).
    GroupBy(bucket).
    OrderBy(bucket.Asc()).
    All(ctx)

Group by the same expression you selected. Two DateTrunc calls with the same arguments render the same SQL, so PostgreSQL matches them — but binding it to a variable says so, and reads better.

Extracting#

go
orm.Extract(orm.Year, Events.At, orm.Integer)         // -> int32
orm.Extract(orm.DayOfWeek, Events.At, orm.Integer)    // 0 = Sunday
orm.Extract(orm.EpochSecond, Events.At, orm.BigInt)   // -> int64

The third argument is the type you want back, as a PGType value. PostgreSQL's extract returns numeric, so something has to say what to cast it to, and saying it here means the Go type is decided rather than asserted.

The fields:

go
orm.Year   orm.Quarter  orm.Month  orm.Week  orm.Day
orm.Hour   orm.Minute   orm.Second
orm.DayOfWeek  orm.DayOfYear  orm.EpochSecond

Comparing#

Timestamps are ordered columns, so the ordinary predicates apply:

go
db.Events.Query().Where(Events.At.Between(dayStart, dayEnd))
db.Events.Query().Where(Events.At.Gte(cutoff))
db.Events.Query().OrderBy(Events.At.Desc())

For "within the last N", compute the boundary in Go rather than in SQL when you can — a bind parameter is a better plan input than an expression the planner has to evaluate per row.

Worked examples#

A daily signup chart#

go
day := orm.DateTrunc(orm.Day, Accounts.CreatedAt)

var perDay = orm.Project2(
    day, orm.Count[Account](),
    func(d time.Time, n int64) Point { return Point{d, n} },
)

rows, err := orm.Select(db.Accounts, perDay).
    Where(Accounts.CreatedAt.Gte(since)).
    GroupBy(day).
    OrderBy(day.Asc()).
    All(ctx)

Monthly is the same query with one word changed, which is the point of naming the bucket.

Opening hours#

go
hour := orm.Extract(orm.Hour, Visits.At, orm.Integer)
dow  := orm.Extract(orm.DayOfWeek, Visits.At, orm.Integer)

var heat = orm.Project3(
    dow, hour, orm.Count[Visit](),
    func(d, h int32, n int64) Cell { return Cell{d, h, n} },
)

orm.Select(db.Visits, heat).GroupBy(dow, hour).All(ctx)

A trial that expires#

go
// Trials ending in the next three days.
soon := time.Now().Add(72 * time.Hour)
db.Trials.Query().Where(Trials.EndsAt.Between(time.Now(), soon))

// Extending one, in SQL, without reading it first.
db.Trials.Update().
    Set(Trials.EndsAt.SetExpr(orm.AddInterval(Trials.EndsAt, orm.Val(orm.IntervalOf(0, 14, 0))))).
    Where(Trials.ID.Eq(id)).
    Exec(ctx)

IntervalOf(0, 14, 0) is fourteen days, not 336 hours. Across a daylight-saving boundary those are different instants, and the interval keeps the distinction that a Duration would throw away.