Can't `Scan` timestamps written without locations

[lukasschwab]

Hello! Had a great time working with this driver today. Ran into one issue with timestamps.

Timestamps without zone names

Expected behavior

A timestamp without a zone name — but with a valid offset — should round-trip to libsql without error and without adding an inaccurate zone name.

Reproduce

Context

Apologies — this is definitely not minimal, but I hope they're clear enough.

I'm migrating a bunch of records (texts) from a JSON file into a libsql database. Each text has a field text.Timestamp. There isn't a ton of data, so I parse the whole JSON file into memory, then upsert row-by-row into a local .db file.

These timestamps are formatted consistently in JSON, with the same timezone offset information — none explicitly specify a location:

{ "timestamp": "2023-06-07T14:04:17.898834-07:00" }
{ "timestamp": "2023-05-13T14:55:39.87727-04:00" }

For some reason, the default Go JSON parsing yields TZ location information for the -07:00 timestamps but not for the -04:00 timestamps. That's odd, but I don't think it should be significant: both time zones have valid offsets.

name, off := text.Timestamp.Zone()
log.Printf("%v in '%s' (%s, %d)", text.Timestamp, text.Timestamp.Location(), name, off)
// 2023-06-07 14:04:17.898834 -0700 PDT in 'Local' (PDT, -25200)
// 2023-05-13 14:55:39.87727 -0400 -0400 in '' (, -14400)

Write/read

I write these rows and capture the resulting record in a single REPLACE INTO query:

REPLACE INTO texts (id, timestamp) 
VALUES (:id, :timestamp) 
RETURNING id, timestamp

...providing the :id and :timestamp as named arguments (code paraphrased):

var id string
var t time.Time
if err := s.QueryRowContext(ctx, upsertQuery, asArgs(t)...).Scan(&id, &t) {
    // Handle err...
}

That yields the following error, but only for the -0400 timestamps for which Go didn't parse location information:

sql: Scan error on column index 5, name "timestamp": unsupported Scan, storing driver.Value type string into type *time.Time

The writes succeeded; only the scan failed. If I access the DB directly with sqlite3, I note different timestamp representations for timestamps with/without location info:

sqlite> SELECT id, timestamp FROM texts; 
0d17a2cf|2023-09-30 16:27:40.427892 -0700 PDT
cd59903d|2023-09-28 20:51:43.322914 -0700 PDT
4add4c74|2023-09-28 20:23:59.224713 -0700 PDT
54a08df6|2023-09-21 17:26:35.747502 -0700 PDT
66dfb8af|2023-07-18 13:25:24.156386 -0700 PDT
6797e665|2023-07-18 11:33:13.821098 -0700 PDT
0f8214ac|2023-06-07 16:05:31.717066 -0700 PDT
e1ee3820|2023-06-07 15:09:54.351432 -0700 PDT
9a2fe90d|2023-06-07 14:04:17.898834 -0700 PDT
fca2dc47|2023-05-13 14:55:39.87727 -0400 -0400
ab0c7aa5|2023-05-13 14:00:29.465634 -0400 -0400
c4645ee4|2023-04-22 07:12:25.588384 -0700 PDT

Mitigation

For the purposes of my migration, I can make this work by coercing the bad timestamps into a named zone:

text.Timestamp = text.Timestamp.UTC()

Arguably I should really normalize this DB data so that everything is in UTC... but this is a personal project where having a little bit of location info in the TZ is nice.

There might also be a solution with a custom sql.Scanner.

[haaawk]

Thanks for bringing this up @lukasschwab. Could you please share the exact CREATE TABLE texts SQL statement? A snippet of code that's executable would me greatly appreciated too :).

[lukasschwab]

@haaawk here's a simplified self-contained demo of the issue! Note that you'll have to supply a valid .db file to sql.Open.

package main

import (
    "database/sql"
    "encoding/json"
    "log"
    "time"

    _ "github.com/libsql/libsql-client-go/libsql"
    _ "modernc.org/sqlite"
)

const (
    createTableQuery = `CREATE TABLE IF NOT EXISTS texts (
        id varchar(8) NOT NULL UNIQUE,
        timestamp DATETIME NOT NULL
    );`
    upsertTextQuery = `
    REPLACE INTO texts (id, timestamp) 
    VALUES (:id, :timestamp) 
    RETURNING id, timestamp;
    `
)

func main() {
    // Initialize timestamps.
    var tWithLocation time.Time
    t0json := []byte(`"2023-06-07T14:04:17.898834-07:00"`)
    if err := json.Unmarshal([]byte(t0json), &tWithLocation); err != nil {
        log.Fatalf("failed unmarshaling: %v", err)
    }

    var tWithoutLocation time.Time
    t1json := []byte(`"2023-05-13T14:55:39.87727-04:00"`)
    if err := json.Unmarshal([]byte(t1json), &tWithoutLocation); err != nil {
        log.Fatalf("failed unmarshaling: %v", err)
    }

    log.Printf("Unmarshaled timestamps %v and %v", tWithLocation, tWithoutLocation)

    // Initialize DB.
    db, err := sql.Open("libsql", "file:///Users/lukas/Programming/tiir/tempstore.db")
    if err != nil {
        log.Fatalf("error opening DB connection: %v", err)
    }

    if _, err := db.Exec(createTableQuery); err != nil {
        log.Fatalf("failed creating table: %v", err)
    }

    // Upserts.
    var discardId = ""
    if err := db.
        QueryRow(upsertTextQuery, sql.Named("id", "aaaaaaaa"), sql.Named("timestamp", tWithLocation)).
        Scan(&discardId, &time.Time{}); err != nil {
        log.Fatalf("failed parsing timestamp *with* location: %v", err)
    }

    if err := db.
        QueryRow(upsertTextQuery, sql.Named("id", "bbbbbbbb"), sql.Named("timestamp", tWithoutLocation)).
        Scan(&discardId, &time.Time{}); err != nil {
        log.Fatalf("failed parsing timestamp *without* location: %v", err)
    }
}

Running it yields:

$ : > tempstore.db; go run ./cmd/issue
2023/10/02 09:23:31 Unmarshaled timestamps 2023-06-07 14:04:17.898834 -0700 PDT and 2023-05-13 14:55:39.87727 -0400 -0400
2023/10/02 09:23:31 failed parsing timestamp *without* location: sql: Scan error on column index 1, name "timestamp": unsupported Scan, storing driver.Value type string into type *time.Time
exit status 1

Expected behavior: program shouldn't fail.

[haaawk]

This is an issue of "modernc.org/sqlite" driver. When I used "github.com/mattn/go-sqlite3" The example worked. Closing as this is not libsql related. Just an issue with one of the external sqlite drivers.