Hypertables and time buckets
On this page
On TimescaleDB:
- the first migration runs CREATE EXTENSION IF NOT EXISTS timescaledb, then create_hypertable, which Seagull reads as a note on the table rather than a change to its columns
- time_bucket returns timestamptz as time.Time; time_bucket_gapfill's bucket arrives as *time.Time
- the bucket width is written INTERVAL '1 hour'; a bare '1 hour' reads as an interval too
- first, last and locf are polymorphic; Seagull types first and last from their value but leaves locf of an aggregate as any, so the queries cast them to double precision
- the gapfill bounds are cast to timestamptz, because PostgreSQL cannot infer a parameter's type from time_bucket_gapfill
TimescaleDB is PostgreSQL with an extension for time series. Its central idea is the hypertable: to your queries it is an ordinary table, but TimescaleDB stores it as a series of chunks, each holding one stretch of time. Around it come functions for summarising by time: time_bucket, gap filling with time_bucket_gapfill and locf, and first and last.
Seagull's timescaledb dialect is the PostgreSQL dialect plus the types of those functions, so queries that use them come back as typed rows.
The example records sea temperatures from two buoys either side of midnight, with a gap of more than an hour in one buoy's readings.
The hypertable
The migration enables the extension, creates the table, and turns it into a hypertable with one chunk per day:
db/migrations/0001_readings.up.sql
CREATE EXTENSION IF NOT EXISTS timescaledb;
CREATE TABLE readings (
ts TIMESTAMPTZ NOT NULL,
buoy TEXT NOT NULL,
temperature DOUBLE PRECISION NOT NULL
);
SELECT create_hypertable('readings', 'ts', chunk_time_interval => INTERVAL '1 day');
Seagull reads create_hypertable as a note on the table rather than a change to its columns. Queries see the columns the CREATE TABLE declared, so inserting and selecting look exactly as they would on PostgreSQL.
ListChunks calls show_chunks to list the chunks behind the hypertable. The readings cross midnight, so they fill two.
db/queries/readings.sql
-- sgl.query(ListChunks, many)
SELECT show_chunks('readings') AS chunk;
time_bucket
time_bucket rounds each timestamp down to the start of its bucket, here the hour, and grouping by the bucket gives one row per hour that has readings.
db/queries/readings.sql
-- sgl.query(HourlyAverages, many)
SELECT time_bucket(INTERVAL '1 hour', ts) AS bucket, avg(temperature) AS mean
FROM readings
WHERE buoy = $1
GROUP BY bucket
ORDER BY bucket;
The bucket is a timestamptz, so it arrives as a time.Time. avg can return NULL, so the mean is a *float64.
internal/store
type HourlyAveragesRow struct {
Bucket time.Time `json:"bucket"`
Mean *float64 `json:"mean"`
}
func (queries *Queries) HourlyAverages(ctx context.Context, buoy string) ([]HourlyAveragesRow, error) {
rows, err := queries.reader.QueryContext(ctx, hourlyAveragesSQL, buoy)
if err != nil {
return nil, err
}
defer rows.Close()
var results []HourlyAveragesRow
for rows.Next() {
var row HourlyAveragesRow
if err := rows.Scan(&row.Bucket, &row.Mean); err != nil {
return nil, err
}
results = append(results, row)
}
if err := rows.Err(); err != nil {
return nil, err
}
return results, nil
}
The width is written INTERVAL '1 hour'. TimescaleDB also accepts a bare '1 hour', but Seagull does not yet match that untyped literal to time_bucket's interval overload: it warns with Q004 and types the bucket as any.
Filling the gaps
An hour with no readings has no row from time_bucket, which makes a chart jump. time_bucket_gapfill makes a row for every bucket between a start and a finish, with a NULL mean where an hour had no readings, and locf ("last observation carried forward") fills each NULL with the last value before it.
db/queries/readings.sql
-- sgl.query(GapfilledHourly, many)
-- $2 as sgl.param(start)
-- $3 as sgl.param(finish)
SELECT
time_bucket_gapfill(INTERVAL '1 hour', ts, $2::timestamptz, $3::timestamptz) AS bucket,
avg(temperature) AS mean,
locf(avg(temperature))::double precision AS carried
FROM readings
WHERE buoy = $1 AND ts >= $2 AND ts < $3
GROUP BY bucket
ORDER BY bucket;
The query names its bounds with sgl.param(start) and sgl.param(finish), and casts them to timestamptz where it passes them to time_bucket_gapfill, since PostgreSQL cannot tell their type from that call alone.
internal/store
type GapfilledHourlyParams struct {
Buoy string
Start time.Time
Finish time.Time
}
type GapfilledHourlyRow struct {
Bucket *time.Time `json:"bucket"`
Mean *float64 `json:"mean"`
Carried *float64 `json:"carried"`
}
func (queries *Queries) GapfilledHourly(ctx context.Context, params GapfilledHourlyParams) ([]GapfilledHourlyRow, error) {
rows, err := queries.reader.QueryContext(ctx, gapfilledHourlySQL, params.Buoy, params.Start, params.Finish)
if err != nil {
return nil, err
}
defer rows.Close()
var results []GapfilledHourlyRow
for rows.Next() {
var row GapfilledHourlyRow
if err := rows.Scan(&row.Bucket, &row.Mean, &row.Carried); err != nil {
return nil, err
}
results = append(results, row)
}
if err := rows.Err(); err != nil {
return nil, err
}
return results, nil
}
Seagull types the gap-filled bucket as a *time.Time, although it is never NULL, so the program checks it before formatting.
first and last
first(temperature, ts) is the temperature at the earliest ts in each group, and last the one at the latest, whatever order the rows arrive in.
db/queries/readings.sql
-- sgl.query(FirstAndLast, many)
SELECT
buoy,
first(temperature, ts)::double precision AS first_temperature,
last(temperature, ts)::double precision AS last_temperature
FROM readings
GROUP BY buoy
ORDER BY buoy;
first, last and locf are polymorphic: they return the type of the value they are given. Seagull types them as any for a double precision value, so the queries cast their results to double precision, which arrives as a *float64.
Using it
example.go
package main
import (
"context"
"database/sql"
"fmt"
"io"
"time"
"seagull.sh/seagull/examples/timescaledb/032_timescale_hypertables/internal/store"
)
// clock prints bucket times as hours and minutes in UTC.
const clock = "15:04"
// reading is one sea temperature sample from a buoy.
type reading struct {
// buoy names the buoy that took the sample.
buoy string
// minutes is how long after 22:00 UTC on 31 May the sample was taken.
minutes int
// temperature is the sea temperature in degrees Celsius.
temperature float64
}
// readings are two buoys' samples either side of midnight. Doom Bar sends nothing between
// 23:50 and 01:15.
var readings = []reading{
{buoy: "Doom Bar", minutes: 0, temperature: 14.0},
{buoy: "Doom Bar", minutes: 20, temperature: 14.4},
{buoy: "Doom Bar", minutes: 40, temperature: 14.2},
{buoy: "Doom Bar", minutes: 70, temperature: 13.8},
{buoy: "Doom Bar", minutes: 110, temperature: 13.6},
{buoy: "Doom Bar", minutes: 195, temperature: 13.1},
{buoy: "Doom Bar", minutes: 225, temperature: 13.3},
{buoy: "Stepper Point", minutes: 5, temperature: 15.0},
{buoy: "Stepper Point", minutes: 90, temperature: 14.6},
{buoy: "Stepper Point", minutes: 210, temperature: 14.0},
}
// run records the buoys' readings in the readings hypertable, then summarises them by the
// hour with time_bucket, fills the missing hour with time_bucket_gapfill and locf, and
// picks each buoy's first and last reading.
//
// The migration makes readings a hypertable with create_hypertable, which TimescaleDB
// splits into one chunk per day. To Seagull it is still the table the CREATE TABLE
// declared, so inserting and selecting look as they would on PostgreSQL; what the
// TimescaleDB dialect adds is the types of its functions.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the summaries.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
queries := store.New(database)
start := time.Date(2026, time.May, 31, 22, 0, 0, 0, time.UTC)
for _, sample := range readings {
err := queries.RecordReading(ctx, store.RecordReadingParams{
Ts: start.Add(time.Duration(sample.minutes) * time.Minute),
Buoy: sample.buoy,
Temperature: sample.temperature,
})
if err != nil {
return fmt.Errorf("recording a reading: %w", err)
}
}
// show_chunks lists the chunks behind the hypertable; the readings cross midnight, so
// they fill two.
chunks, err := queries.ListChunks(ctx)
if err != nil {
return fmt.Errorf("listing chunks: %w", err)
}
fmt.Fprintf(output, "recorded %d readings in %d chunks\n", len(readings), len(chunks))
if err := printHourly(ctx, queries, output); err != nil {
return err
}
if err := printGapfilled(ctx, queries, start, output); err != nil {
return err
}
return printFirstAndLast(ctx, queries, output)
}
// printHourly prints Doom Bar's hourly means. time_bucket rounds each reading's time down
// to the hour, and the query groups by that bucket, so an hour without readings has no
// row.
//
// The width is written INTERVAL '1 hour'. A bare '1 hour' works as well: Seagull reads
// the quoted width as an interval, as TimescaleDB does, and types the bucket the same.
//
// Takes queries (*store.Queries) which runs the query.
// Takes output (io.Writer) which receives the means.
//
// Returns error when the query fails.
func printHourly(ctx context.Context, queries *store.Queries, output io.Writer) error {
hours, err := queries.HourlyAverages(ctx, "Doom Bar")
if err != nil {
return fmt.Errorf("averaging by the hour: %w", err)
}
fmt.Fprintln(output, "Doom Bar by the hour (time_bucket):")
for _, hour := range hours {
fmt.Fprintf(output, " %s mean %s\n", hour.Bucket.UTC().Format(clock), celsius(hour.Mean))
}
return nil
}
// printGapfilled prints Doom Bar's hourly means from 22:00 to 02:00 with every hour
// present. time_bucket_gapfill makes a row for each bucket between start and finish,
// whose mean is NULL when the hour had no readings; locf carries the last mean forward
// into it.
//
// The query names its bounds with `$2 as sgl.param(start)` and `$3 as sgl.param(finish)`,
// and casts them to timestamptz where it passes them to time_bucket_gapfill, since
// PostgreSQL cannot tell their type from that call alone. Seagull types the gapfilled
// bucket as *time.Time, though it is never NULL.
//
// Takes queries (*store.Queries) which runs the query.
// Takes start (time.Time) which is the first hour.
// Takes output (io.Writer) which receives the means.
//
// Returns error when the query fails.
func printGapfilled(ctx context.Context, queries *store.Queries, start time.Time, output io.Writer) error {
hours, err := queries.GapfilledHourly(ctx, store.GapfilledHourlyParams{
Buoy: "Doom Bar",
Start: start,
Finish: start.Add(4 * time.Hour),
})
if err != nil {
return fmt.Errorf("filling the gaps: %w", err)
}
fmt.Fprintln(output, "Doom Bar by the hour, gaps filled (time_bucket_gapfill, locf):")
for _, hour := range hours {
bucket := "--:--"
if hour.Bucket != nil {
bucket = hour.Bucket.UTC().Format(clock)
}
fmt.Fprintf(output, " %s mean %s carried %s\n", bucket, celsius(hour.Mean), celsius(hour.Carried))
}
return nil
}
// printFirstAndLast prints each buoy's earliest and latest reading. first and last take
// the value at the smallest and largest time in each group, whatever order the rows
// arrive in.
//
// first, last and locf are polymorphic, returning the type of the value they are given.
// Seagull types first and last from their value, but leaves locf of an aggregate as any,
// so the queries cast their results to double precision, which arrives as *float64.
//
// Takes queries (*store.Queries) which runs the query.
// Takes output (io.Writer) which receives the readings.
//
// Returns error when the query fails.
func printFirstAndLast(ctx context.Context, queries *store.Queries, output io.Writer) error {
buoys, err := queries.FirstAndLast(ctx)
if err != nil {
return fmt.Errorf("finding first and last readings: %w", err)
}
fmt.Fprintln(output, "first and last reading (first, last):")
for _, buoy := range buoys {
fmt.Fprintf(output, " %-13s first %s last %s\n", buoy.Buoy, celsius(buoy.FirstTemperature), celsius(buoy.LastTemperature))
}
return nil
}
// celsius formats a temperature that may be NULL.
//
// Takes value (*float64) which is the temperature, or nil.
//
// Returns string which is the temperature to one decimal place, or a dash for nil.
func celsius(value *float64) string {
if value == nil {
return " -"
}
return fmt.Sprintf("%4.1f", *value)
}
What it prints
Doom Bar sent nothing between 23:50 and 01:15, so time_bucket has no midnight row. The gap-filled query has one, with no mean and 13.7 carried forward from 23:00.
recorded 10 readings in 2 chunks
Doom Bar by the hour (time_bucket):
22:00 mean 14.2
23:00 mean 13.7
01:00 mean 13.2
Doom Bar by the hour, gaps filled (time_bucket_gapfill, locf):
22:00 mean 14.2 carried 14.2
23:00 mean 13.7 carried 13.7
00:00 mean - carried 13.7
01:00 mean 13.2 carried 13.2
first and last reading (first, last):
Doom Bar first 14.0 last 13.3
Stepper Point first 15.0 last 14.0
Try it
Remove the ::double precision cast after first(temperature, ts) and run sgl generate: FirstTemperature becomes any. Then write the bucket width in HourlyAverages as a bare '1 hour' and run sgl vet to see the Q004 warning described above.
Run it locally
From the directory you cloned the Seagull repository into:
cd seagull/examples/timescaledb && go run ./032_timescale_hypertables
Its test runs it too, and checks what it prints against output.txt:
cd seagull/examples/timescaledb && SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./032_timescale_hypertables
Needs a running TimescaleDB server: set DATABASE_URL, or pass -dsn, to the address of an empty database.
The files
Every file of the example, as it is in the Seagull repository.
sgl.yaml
# Copyright 2026 PolitePixels Limited
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
# This project stands against fascism, authoritarianism, and all forms of
# oppression. We built this to empower people, not to enable those who would
# strip others of their rights and dignity.
version: 1
databases:
- name: buoys
dialect: timescaledb
migrations: db/migrations
queries: db/queries
go:
package: store
out: internal/store
db/migrations/0001_readings.up.sql
CREATE EXTENSION IF NOT EXISTS timescaledb;
CREATE TABLE readings (
ts TIMESTAMPTZ NOT NULL,
buoy TEXT NOT NULL,
temperature DOUBLE PRECISION NOT NULL
);
SELECT create_hypertable('readings', 'ts', chunk_time_interval => INTERVAL '1 day');
db/migrations/0001_readings.down.sql
DROP TABLE readings;
db/queries/readings.sql
-- sgl.query(RecordReading, exec)
INSERT INTO readings (ts, buoy, temperature) VALUES ($1, $2, $3);
-- sgl.query(ListChunks, many)
SELECT show_chunks('readings') AS chunk;
-- sgl.query(HourlyAverages, many)
SELECT time_bucket(INTERVAL '1 hour', ts) AS bucket, avg(temperature) AS mean
FROM readings
WHERE buoy = $1
GROUP BY bucket
ORDER BY bucket;
-- sgl.query(FirstAndLast, many)
SELECT
buoy,
first(temperature, ts)::double precision AS first_temperature,
last(temperature, ts)::double precision AS last_temperature
FROM readings
GROUP BY buoy
ORDER BY buoy;
-- sgl.query(GapfilledHourly, many)
-- $2 as sgl.param(start)
-- $3 as sgl.param(finish)
SELECT
time_bucket_gapfill(INTERVAL '1 hour', ts, $2::timestamptz, $3::timestamptz) AS bucket,
avg(temperature) AS mean,
locf(avg(temperature))::double precision AS carried
FROM readings
WHERE buoy = $1 AND ts >= $2 AND ts < $3
GROUP BY bucket
ORDER BY bucket;
example.go
package main
import (
"context"
"database/sql"
"fmt"
"io"
"time"
"seagull.sh/seagull/examples/timescaledb/032_timescale_hypertables/internal/store"
)
// clock prints bucket times as hours and minutes in UTC.
const clock = "15:04"
// reading is one sea temperature sample from a buoy.
type reading struct {
// buoy names the buoy that took the sample.
buoy string
// minutes is how long after 22:00 UTC on 31 May the sample was taken.
minutes int
// temperature is the sea temperature in degrees Celsius.
temperature float64
}
// readings are two buoys' samples either side of midnight. Doom Bar sends nothing between
// 23:50 and 01:15.
var readings = []reading{
{buoy: "Doom Bar", minutes: 0, temperature: 14.0},
{buoy: "Doom Bar", minutes: 20, temperature: 14.4},
{buoy: "Doom Bar", minutes: 40, temperature: 14.2},
{buoy: "Doom Bar", minutes: 70, temperature: 13.8},
{buoy: "Doom Bar", minutes: 110, temperature: 13.6},
{buoy: "Doom Bar", minutes: 195, temperature: 13.1},
{buoy: "Doom Bar", minutes: 225, temperature: 13.3},
{buoy: "Stepper Point", minutes: 5, temperature: 15.0},
{buoy: "Stepper Point", minutes: 90, temperature: 14.6},
{buoy: "Stepper Point", minutes: 210, temperature: 14.0},
}
// run records the buoys' readings in the readings hypertable, then summarises them by the
// hour with time_bucket, fills the missing hour with time_bucket_gapfill and locf, and
// picks each buoy's first and last reading.
//
// The migration makes readings a hypertable with create_hypertable, which TimescaleDB
// splits into one chunk per day. To Seagull it is still the table the CREATE TABLE
// declared, so inserting and selecting look as they would on PostgreSQL; what the
// TimescaleDB dialect adds is the types of its functions.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the summaries.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
queries := store.New(database)
start := time.Date(2026, time.May, 31, 22, 0, 0, 0, time.UTC)
for _, sample := range readings {
err := queries.RecordReading(ctx, store.RecordReadingParams{
Ts: start.Add(time.Duration(sample.minutes) * time.Minute),
Buoy: sample.buoy,
Temperature: sample.temperature,
})
if err != nil {
return fmt.Errorf("recording a reading: %w", err)
}
}
// show_chunks lists the chunks behind the hypertable; the readings cross midnight, so
// they fill two.
chunks, err := queries.ListChunks(ctx)
if err != nil {
return fmt.Errorf("listing chunks: %w", err)
}
fmt.Fprintf(output, "recorded %d readings in %d chunks\n", len(readings), len(chunks))
if err := printHourly(ctx, queries, output); err != nil {
return err
}
if err := printGapfilled(ctx, queries, start, output); err != nil {
return err
}
return printFirstAndLast(ctx, queries, output)
}
// printHourly prints Doom Bar's hourly means. time_bucket rounds each reading's time down
// to the hour, and the query groups by that bucket, so an hour without readings has no
// row.
//
// The width is written INTERVAL '1 hour'. A bare '1 hour' works as well: Seagull reads
// the quoted width as an interval, as TimescaleDB does, and types the bucket the same.
//
// Takes queries (*store.Queries) which runs the query.
// Takes output (io.Writer) which receives the means.
//
// Returns error when the query fails.
func printHourly(ctx context.Context, queries *store.Queries, output io.Writer) error {
hours, err := queries.HourlyAverages(ctx, "Doom Bar")
if err != nil {
return fmt.Errorf("averaging by the hour: %w", err)
}
fmt.Fprintln(output, "Doom Bar by the hour (time_bucket):")
for _, hour := range hours {
fmt.Fprintf(output, " %s mean %s\n", hour.Bucket.UTC().Format(clock), celsius(hour.Mean))
}
return nil
}
// printGapfilled prints Doom Bar's hourly means from 22:00 to 02:00 with every hour
// present. time_bucket_gapfill makes a row for each bucket between start and finish,
// whose mean is NULL when the hour had no readings; locf carries the last mean forward
// into it.
//
// The query names its bounds with `$2 as sgl.param(start)` and `$3 as sgl.param(finish)`,
// and casts them to timestamptz where it passes them to time_bucket_gapfill, since
// PostgreSQL cannot tell their type from that call alone. Seagull types the gapfilled
// bucket as *time.Time, though it is never NULL.
//
// Takes queries (*store.Queries) which runs the query.
// Takes start (time.Time) which is the first hour.
// Takes output (io.Writer) which receives the means.
//
// Returns error when the query fails.
func printGapfilled(ctx context.Context, queries *store.Queries, start time.Time, output io.Writer) error {
hours, err := queries.GapfilledHourly(ctx, store.GapfilledHourlyParams{
Buoy: "Doom Bar",
Start: start,
Finish: start.Add(4 * time.Hour),
})
if err != nil {
return fmt.Errorf("filling the gaps: %w", err)
}
fmt.Fprintln(output, "Doom Bar by the hour, gaps filled (time_bucket_gapfill, locf):")
for _, hour := range hours {
bucket := "--:--"
if hour.Bucket != nil {
bucket = hour.Bucket.UTC().Format(clock)
}
fmt.Fprintf(output, " %s mean %s carried %s\n", bucket, celsius(hour.Mean), celsius(hour.Carried))
}
return nil
}
// printFirstAndLast prints each buoy's earliest and latest reading. first and last take
// the value at the smallest and largest time in each group, whatever order the rows
// arrive in.
//
// first, last and locf are polymorphic, returning the type of the value they are given.
// Seagull types first and last from their value, but leaves locf of an aggregate as any,
// so the queries cast their results to double precision, which arrives as *float64.
//
// Takes queries (*store.Queries) which runs the query.
// Takes output (io.Writer) which receives the readings.
//
// Returns error when the query fails.
func printFirstAndLast(ctx context.Context, queries *store.Queries, output io.Writer) error {
buoys, err := queries.FirstAndLast(ctx)
if err != nil {
return fmt.Errorf("finding first and last readings: %w", err)
}
fmt.Fprintln(output, "first and last reading (first, last):")
for _, buoy := range buoys {
fmt.Fprintf(output, " %-13s first %s last %s\n", buoy.Buoy, celsius(buoy.FirstTemperature), celsius(buoy.LastTemperature))
}
return nil
}
// celsius formats a temperature that may be NULL.
//
// Takes value (*float64) which is the temperature, or nil.
//
// Returns string which is the temperature to one decimal place, or a dash for nil.
func celsius(value *float64) string {
if value == nil {
return " -"
}
return fmt.Sprintf("%4.1f", *value)
}
main.go
// Command 032_timescale_hypertables applies its migrations to a TimescaleDB database,
// making readings a hypertable, then runs the example in example.go: two buoys' sea
// temperatures are recorded and summarised by the hour, with the gaps filled.
//
// Run it with `go run .` against a local server, which `docker run -d -p 5432:5432 -e
// POSTGRES_PASSWORD=postgres timescale/timescaledb-ha:pg17` starts, or point it elsewhere
// with `go run . -dsn <connection string>` or DATABASE_URL. Use an empty database: the
// example's summaries assume only its own readings.
package main
import (
"cmp"
"context"
"database/sql"
"embed"
"flag"
"fmt"
"log"
"os"
_ "github.com/jackc/pgx/v5/stdlib"
"seagull.sh/seagull/sdk/migrate"
"seagull.sh/seagull/sdk/migrate/migrate_backend_postgres"
)
// localDSN reaches the server the docker command above starts, with the password that
// command gives it.
const localDSN = "postgresql://postgres:postgres@localhost:5432/postgres?sslmode=disable" //nolint:gosec // G101: the docker command's throwaway password, not a secret
// migrations holds the migration files, compiled into the binary.
//
//go:embed db/migrations
var migrations embed.FS
func main() {
dsn := flag.String("dsn", cmp.Or(os.Getenv("DATABASE_URL"), localDSN), "TimescaleDB connection string")
flag.Parse()
if err := execute(context.Background(), *dsn); err != nil {
log.Fatal(err)
}
}
// execute connects to the database at dsn, migrates it with the PostgreSQL backend, which
// TimescaleDB uses, and runs the example against it.
//
// Takes dsn (string) which is the connection string.
//
// Returns error when the database cannot be prepared or the example fails.
func execute(ctx context.Context, dsn string) error {
database, err := sql.Open("pgx", dsn)
if err != nil {
return err
}
defer func() { _ = database.Close() }()
migrator, err := migrate.NewMigrator(database, migrate_backend_postgres.New(), migrations, "db/migrations")
if err != nil {
return err
}
if _, err := migrator.Up(ctx); err != nil {
return fmt.Errorf("migrating: %w", err)
}
return run(ctx, database, os.Stdout)
}
internal/store/models.go
// Code generated by sgl. DO NOT EDIT.
package store
import "time"
type Readings struct {
Ts time.Time `json:"ts"`
Buoy string `json:"buoy"`
Temperature float64 `json:"temperature"`
}
internal/store/querier.go
// Code generated by sgl. DO NOT EDIT.
package store
import (
"context"
"database/sql"
)
type DBTX interface {
ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}
type dbtxWrapper interface {
WrapDBTX(inner any) any
}
type Queries struct {
reader DBTX
writer DBTX
wrapper dbtxWrapper
}
func New(db DBTX) *Queries {
wrapper, _ := db.(dbtxWrapper)
return &Queries{reader: db, writer: db, wrapper: wrapper}
}
func NewWithReplica(writer DBTX, reader DBTX) *Queries {
wrapper, _ := writer.(dbtxWrapper)
return &Queries{reader: reader, writer: writer, wrapper: wrapper}
}
func (queries *Queries) WithTx(transaction *sql.Tx) *Queries {
if queries == nil {
return &Queries{reader: transaction, writer: transaction, wrapper: nil}
}
db := queries.wrapDBTX(transaction)
return &Queries{reader: db, writer: db, wrapper: queries.wrapper}
}
func (queries *Queries) wrapDBTX(db DBTX) DBTX {
if queries.wrapper == nil {
return db
}
wrapped, ok := queries.wrapper.WrapDBTX(db).(DBTX)
if !ok {
return db
}
return wrapped
}
func (queries *Queries) RunInTx(ctx context.Context, db *sql.DB, fn func(*Queries) error) error {
transaction, err := db.BeginTx(ctx, nil)
if err != nil {
return err
}
defer transaction.Rollback()
if err = fn(queries.WithTx(transaction)); err != nil {
return err
}
return transaction.Commit()
}
internal/store/readings.sql.go
// Code generated by sgl. DO NOT EDIT.
package store
import (
"context"
"time"
)
const firstAndLastSQL = `SELECT
buoy,
first(temperature, ts)::double precision AS first_temperature,
last(temperature, ts)::double precision AS last_temperature
FROM readings
GROUP BY buoy
ORDER BY buoy;`
type FirstAndLastRow struct {
Buoy string `json:"buoy"`
FirstTemperature *float64 `json:"first_temperature"`
LastTemperature *float64 `json:"last_temperature"`
}
func (queries *Queries) FirstAndLast(ctx context.Context) ([]FirstAndLastRow, error) {
rows, err := queries.reader.QueryContext(ctx, firstAndLastSQL)
if err != nil {
return nil, err
}
defer rows.Close()
var results []FirstAndLastRow
for rows.Next() {
var row FirstAndLastRow
if err := rows.Scan(&row.Buoy, &row.FirstTemperature, &row.LastTemperature); err != nil {
return nil, err
}
results = append(results, row)
}
if err := rows.Err(); err != nil {
return nil, err
}
return results, nil
}
const gapfilledHourlySQL = `SELECT
time_bucket_gapfill(INTERVAL '1 hour', ts, $2::timestamptz, $3::timestamptz) AS bucket,
avg(temperature) AS mean,
locf(avg(temperature))::double precision AS carried
FROM readings
WHERE buoy = $1 AND ts >= $2 AND ts < $3
GROUP BY bucket
ORDER BY bucket;`
type GapfilledHourlyParams struct {
Buoy string
Start time.Time
Finish time.Time
}
type GapfilledHourlyRow struct {
Bucket *time.Time `json:"bucket"`
Mean *float64 `json:"mean"`
Carried *float64 `json:"carried"`
}
func (queries *Queries) GapfilledHourly(ctx context.Context, params GapfilledHourlyParams) ([]GapfilledHourlyRow, error) {
rows, err := queries.reader.QueryContext(ctx, gapfilledHourlySQL, params.Buoy, params.Start, params.Finish)
if err != nil {
return nil, err
}
defer rows.Close()
var results []GapfilledHourlyRow
for rows.Next() {
var row GapfilledHourlyRow
if err := rows.Scan(&row.Bucket, &row.Mean, &row.Carried); err != nil {
return nil, err
}
results = append(results, row)
}
if err := rows.Err(); err != nil {
return nil, err
}
return results, nil
}
const hourlyAveragesSQL = `SELECT time_bucket(INTERVAL '1 hour', ts) AS bucket, avg(temperature) AS mean
FROM readings
WHERE buoy = $1
GROUP BY bucket
ORDER BY bucket;`
type HourlyAveragesRow struct {
Bucket time.Time `json:"bucket"`
Mean *float64 `json:"mean"`
}
func (queries *Queries) HourlyAverages(ctx context.Context, buoy string) ([]HourlyAveragesRow, error) {
rows, err := queries.reader.QueryContext(ctx, hourlyAveragesSQL, buoy)
if err != nil {
return nil, err
}
defer rows.Close()
var results []HourlyAveragesRow
for rows.Next() {
var row HourlyAveragesRow
if err := rows.Scan(&row.Bucket, &row.Mean); err != nil {
return nil, err
}
results = append(results, row)
}
if err := rows.Err(); err != nil {
return nil, err
}
return results, nil
}
const listChunksSQL = `SELECT show_chunks('readings') AS chunk;`
type ListChunksRow struct {
Chunk string `json:"chunk"`
}
func (queries *Queries) ListChunks(ctx context.Context) ([]ListChunksRow, error) {
rows, err := queries.reader.QueryContext(ctx, listChunksSQL)
if err != nil {
return nil, err
}
defer rows.Close()
var results []ListChunksRow
if rows.Next() {
var row ListChunksRow
scanArgs := []any{&row.Chunk}
for more := true; more; more = rows.Next() {
row = ListChunksRow{}
if err := rows.Scan(scanArgs...); err != nil {
return nil, err
}
results = append(results, row)
}
}
if err := rows.Err(); err != nil {
return nil, err
}
return results, nil
}
const recordReadingSQL = `INSERT INTO readings (ts, buoy, temperature) VALUES ($1, $2, $3);`
type RecordReadingParams struct {
Ts time.Time
Buoy string
Temperature float64
}
func (queries *Queries) RecordReading(ctx context.Context, params RecordReadingParams) error {
_, err := queries.writer.ExecContext(ctx, recordReadingSQL, params.Ts, params.Buoy, params.Temperature)
return err
}
output.txt
recorded 10 readings in 2 chunks
Doom Bar by the hour (time_bucket):
22:00 mean 14.2
23:00 mean 13.7
01:00 mean 13.2
Doom Bar by the hour, gaps filled (time_bucket_gapfill, locf):
22:00 mean 14.2 carried 14.2
23:00 mean 13.7 carried 13.7
00:00 mean - carried 13.7
01:00 mean 13.2 carried 13.2
first and last reading (first, last):
Doom Bar first 14.0 last 13.3
Stepper Point first 15.0 last 14.0