Scanning of timestamps not working when using "with time zone"

[coma64]

With a column of type "timestamp with time zone", like in the following table, sqlite3 doesn't convert it into a time.Time and scanning such a row into a time.Time field therefore fails.

create table snippets(
    id binary(16) primary key,
    content text not null,
    created_at timestamp with time zone not null default CURRENT_TIMESTAMP
);

I traced this to SQLiteRows.nextSyncLocked() where you check whether rc.decltype[i] is any of date, datetime or timestamp but in my case it's timestamp with time zone causing the value to not be converted from a string.

go version: go version go1.21.3 darwin/arm64

Code

type Snippet struct {
	Id        string
	Content   string
	CreatedAt time.Time `db:"created_at"`
}

snippet := Snippet{
  Id:      id.String(),
  Content: content,
}

if err = s.db.GetContext(ctx, &snippet, "insert into snippets(id, content) values ($1, $2) returning created_at", binaryId, content); err != nil {
  return nil, fmt.Errorf("inserting snippet: %w", err)
}

[ljluestc]

package main

import (
	"context"
	"database/sql"
	"fmt"
	"time"

	"github.com/google/uuid"
	_ "github.com/mattn/go-sqlite3"
	"github.com/pkg/errors"
)

// TimeWithZone is a custom type to handle SQLite's TIMESTAMP WITH TIME ZONE
type TimeWithZone struct {
	Time time.Time
}

// Scan implements the sql.Scanner interface to convert SQLite's TIMESTAMP WITH TIME ZONE string to time.Time
func (t *TimeWithZone) Scan(value interface{}) error {
	if value == nil {
		t.Time = time.Time{}
		return nil
	}

	// Expect value to be a string from SQLite
	str, ok := value.(string)
	if !ok {
		return fmt.Errorf("cannot scan %T into TimeWithZone", value)
	}

	// Parse the string in RFC3339 format or other expected format
	// SQLite typically stores TIMESTAMP WITH TIME ZONE as ISO8601 (e.g., "2025-06-22T12:09:00Z")
	parsed, err := time.Parse(time.RFC3339, str)
	if err != nil {
		// Try an alternative format if needed, e.g., "2006-01-02 15:04:05-07:00"
		parsed, err = time.Parse("2006-01-02 15:04:05-07:00", str)
		if err != nil {
			return fmt.Errorf("parsing timestamp with time zone: %w", err)
		}
	}

	t.Time = parsed
	return nil
}

// Value implements the driver.Valuer interface to store time.Time as a string
func (t TimeWithZone) Value() (driver.Value, error) {
	return t.Time.Format(time.RFC3339), nil
}

// Snippet represents the snippets table
type Snippet struct {
	ID        string       `db:"id"`
	Content   string       `db:"content"`
	CreatedAt TimeWithZone `db:"created_at"`
}

// Storage handles database operations
type Storage struct {
	db *sql.DB
}

// NewStorage initializes a new SQLite database
func NewStorage(dsn string) (*Storage, error) {
	db, err := sql.Open("sqlite3", dsn)
	if err != nil {
		return nil, fmt.Errorf("opening database: %w", err)
	}

	// Create the snippets table
	_, err = db.Exec(`
		CREATE TABLE IF NOT EXISTS snippets (
			id BLOB PRIMARY KEY,
			content TEXT NOT NULL,
			created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
		)
	`)
	if err != nil {
		return nil, fmt.Errorf("creating table: %w", err)
	}

	return &Storage{db: db}, nil
}

// InsertSnippet inserts a new snippet and returns the created snippet
func (s *Storage) InsertSnippet(ctx context.Context, content string) (*Snippet, error) {
	// Generate a new UUID for the ID
	id, err := uuid.NewRandom()
	if err != nil {
		return nil, fmt.Errorf("generating UUID: %w", err)
	}

	// Convert UUID to binary for storage
	binaryID := id[:]

	// Prepare the snippet struct for scanning
	snippet := &Snippet{
		ID:      id.String(),
		Content: content,
	}

	// Insert and retrieve the created_at timestamp
	err = s.db.QueryRowContext(
		ctx,
		"INSERT INTO snippets (id, content) VALUES (?, ?) RETURNING id, content, created_at",
		binaryID, content,
	).Scan(&snippet.ID, &snippet.Content, &snippet.CreatedAt)
	if err != nil {
		return nil, fmt.Errorf("inserting snippet: %w", err)
	}

	return snippet, nil
}

func main() {
	// Initialize the database (use ":memory:" for in-memory SQLite or a file path)
	storage, err := NewStorage(":memory:")
	if err != nil {
		fmt.Printf("Error initializing storage: %v\n", err)
		return
	}
	defer storage.db.Close()

	// Insert a snippet
	ctx := context.Background()
	snippet, err := storage.InsertSnippet(ctx, "Hello, world!")
	if err != nil {
		fmt.Printf("Error inserting snippet: %v\n", err)
		return
	}

	// Print the inserted snippet
	fmt.Printf("Inserted snippet: ID=%s, Content=%s, CreatedAt=%s\n",
		snippet.ID, snippet.Content, snippet.CreatedAt.Time.Format(time.RFC3339))
}