db.pg: ORM cannot parse TIMESTAMPTZ columns into time.Time

[fleximus]

Describe the bug

db.pg ORM returns TIMESTAMPTZ values as text but parses time.Time fields using time.parse(). The parser only accepts YYYY-MM-DD HH:mm:ss and rejects PostgreSQL timestamps containing fractional seconds or timezone offsets.

Reproduction Steps

module main

import db.pg
import time

@[table: 'orm_timestamptz_repro']
struct Event {
	id          int @[primary; sql: serial]
	occurred_at time.Time
}

fn main() {
	mut db := pg.connect(pg.Config{
		host:     'localhost'
		user:     'postgres'
		password: 'postgres_password'
		dbname:   'playground'
	})!

	mut conn := db.conn()!
	defer {
		conn.exec('DROP TABLE IF EXISTS orm_timestamptz_repro') or {}
		conn.close() or {}
		db.close() or {}
	}

	conn.exec('DROP TABLE IF EXISTS orm_timestamptz_repro')!
	conn.exec('
  		CREATE TABLE orm_timestamptz_repro (
  			id SERIAL PRIMARY KEY,
  			occurred_at TIMESTAMPTZ NOT NULL
  		)
  	')!
	conn.exec("SET TIME ZONE 'UTC'")!
	conn.exec("
  		INSERT INTO orm_timestamptz_repro (occurred_at)
  		VALUES ('2024-01-15 14:00:00.123456+01:00')
  	")!

	events := sql conn {
		select from Event
	} or {
		eprintln(err.msg())
		return
	}

	println(events)
}

Expected Behavior

ORM should parse the value into a time.Time, preserving PostgreSQL’s microsecond precision and normalizing the instant to UTC: 2024-01-15 13:00:00.123456 UTC

Current Behavior

The query fails while decoding the TIMESTAMPTZ value:
Invalid time format code: 0, error: invalid second format: 00.123456+00

PostgreSQL ORM requests text results and passes the returned string to time.parse(). The parser does not accept fractional seconds or timezone offsets.

Possible Solution

Add a PostgreSQL-specific timestamp decoder that accepts:
YYYY-MM-DD HH:mm:ss[.fraction][Z|±HH[:MM]]

For TIMESTAMPTZ, it should:

• Parse PostgreSQL offsets such as +00, +02, and +02:30.
• Preserve up to microsecond precision.
• Normalize the result to UTC.
• Handle ?time.Time fields.
• Return a clear error for unsupported PostgreSQL values such as infinity.

The decoder should replace the current time.parse(str) call for PostgreSQL ORM time values.

For complete read/write support, serialization should also use a timezone-aware, microsecond-preserving representation instead of time.Time.format_ss().

Tests should cover positive and negative offsets, UTC, fractional seconds, and option types

Additional Information/Context

• time.Time defaults to PostgreSQL TIMESTAMP, not TIMESTAMPTZ.
• Generated schemas can request the correct type explicitly:

occurred_at time.Time @[sql_type: 'TIMESTAMPTZ']

• The sql_type override affects DDL but does not change result decoding.
• PostgreSQL’s t_timestamptz OID is already defined, but ORM decoding is based only on the V field type and does not inspect the result-column OID.
• Mapping the field as string and parsing it in application code is a temporary workaround.

V version

V 0.5.1 31b6787

Environment details (OS name and version, etc.)

V full version: V 0.5.1 6cafb40142fca520cc21eb213dab2bc08b545e8d.31b6787
OS: Linux
Architecture: 64-bit, little-endian, x86_64

Note

You can use the 👍 reaction to increase the issue's priority for developers.

Please note that only the 👍 reaction to the issue itself counts as a vote.
Other reactions and those to comments will not be taken into account.