使用Gorm操作SQLite时time字段扫描报错的解决方案咨询

Let's break down why this error pops up and how to fix it properly:
SQLite doesn't have a native time/datetime type—you're storing your timestamp as a text field with a formatted string (2018-03-17 10:50:59.548+01:00), but Gorm can't automatically convert the raw byte array ([]uint8) returned by SQLite into a *time.Time value out of the box. The gorm:"time" tag you tried won't work here because that's designed for SQL-standard TIME type fields, not SQLite's TEXT-stored timestamps.

Here are the most reliable solutions:

1. Implement Scanner and Valuer Interfaces (Recommended) #

This is the most flexible approach—define a custom time type that handles parsing your exact timestamp format during reads, and formatting it correctly during writes.

First, create a custom type wrapping *time.Time:

import (
    "database/sql/driver"
    "fmt"
    "time"
)

type CustomTime struct {
    *time.Time
}

// Scan parses the raw database value into our CustomTime
func (ct *CustomTime) Scan(value interface{}) error {
    if value == nil {
        ct.Time = nil
        return nil
    }

    byteVal, ok := value.([]uint8)
    if !ok {
        return fmt.Errorf("expected []uint8 for timestamp, got %T", value)
    }

    timeStr := string(byteVal)
    // Match your exact timestamp format: YYYY-MM-DD HH:MM:SS.sss±ZZZZ
    parsedTime, err := time.Parse("2006-01-02 15:04:05.000-07:00", timeStr)
    if err != nil {
        return fmt.Errorf("failed to parse timestamp: %w", err)
    }

    ct.Time = &parsedTime
    return nil
}

// Value converts our CustomTime back to a string for storage
func (ct CustomTime) Value() (driver.Value, error) {
    if ct.Time == nil {
        return nil, nil
    }
    // Use the same format for consistent storage
    return ct.Time.Format("2006-01-02 15:04:05.000-07:00"), nil
}

Then update your struct to use this custom type:

type Timeline struct {
    ID        string     `json:"id"`
    Timestamp CustomTime `json:"timestamp" gorm:"type:text"`
}

This will handle both reading from and writing to the text timestamp field seamlessly.

2. Use Gorm Callbacks (Alternative) #

If you don't want to create a custom type, you can register a Gorm callback to parse the timestamp after querying:

func init() {
    // Register a post-query callback for the timelines table
    gorm.DefaultCallback.Query().After("gorm:query").Register("parse_timeline_timestamp", func(db *gorm.DB) {
        if db.Error != nil {
            return
        }

        var results []Timeline
        if err := db.Result().Scan(&results); err != nil {
            db.Error = err
            return
        }

        // Parse each timestamp string into *time.Time
        for i := range results {
            if results[i].Timestamp == nil {
                // Extract the timestamp string from the row (adjust based on your query)
                var timeStr string
                if err := db.Row().Scan(&results[i].ID, &timeStr); err == nil {
                    parsedTime, err := time.Parse("2006-01-02 15:04:05.000-07:00", timeStr)
                    if err == nil {
                        results[i].Timestamp = &parsedTime
                    }
                }
            }
        }

        db.Result().SetRows(results)
    })
}

Note: This approach is more cumbersome and less maintainable than using a custom type, so it's only recommended if you can't modify the struct type.

3. Adjust the Database Field Type (If Feasible) #

While SQLite doesn't have a native datetime type, you can alter the field to use DATETIME (SQLite will still store it as text under the hood, but Gorm will recognize it and attempt automatic parsing):

ALTER TABLE timelines ALTER COLUMN timestamp TYPE DATETIME;

Then update your struct with the appropriate Gorm tag:

type Timeline struct {
    ID        string     `json:"id"`
    Timestamp *time.Time `json:"timestamp" gorm:"type:datetime"`
}

This works for most standard datetime formats, but it's less reliable than the custom type approach if you have a non-standard timestamp format.

内容的提问来源于stack exchange，提问作者tiagodll