SORT BY created_at, updated_at which are of type TEXT

[menzhessarov]

Hello! First of all, thank you for such opportunity to learn how to design better application for better developer experience.

Now straight to the point: you store time.Time in database as TEXT, precisely as RFC3339 formatted TEXT. How does the database understand how to sort RFC3339 TEXTs?

[benbjohnson]

Hello! Good question. SQLite doesn't have a TIMESTAMP data type but fortunately RFC3339/ISO8601 is naturally lexicographically sortable:

"Date and time values are ordered from the largest to smallest unit of time: year, month (or week), day, hour, minute, second, and fraction of second. The lexicographical order of the representation thus corresponds to chronological order, except for date representations involving negative years or time offset. This allows dates to be naturally sorted by, for example, file systems."

–ISO 8601, Wikipedia

If you're using Postgres or MySQL then you can use their TIMESTAMP data type directly. Although, if you're using SQLite, it does provide helper functions for ISO 8601 date/times: https://sqlite.org/lang_datefunc.html

[menzhessarov]

Is there an advantage of using TIMESTAMP data type?

P.S. Thank you for such swift response!

[benbjohnson]

It's more efficient from a storage and CPU standpoint. Postgres implements TIMESTAMP with 8 bytes whereas an ISO 8601 timestamp would be 20 bytes of text (e.g. 2000-00-00T00:00:00Z).

That being said, it might not actually matter for your application unless you're doing a ton of timestamp data.

[menzhessarov]

I have another question:

type NullTime time.Time

func (n *NullTime) Scan(value interface{}) error {
    if value == nil {
        *(*time.Time)(n) = time.Time{}
        return nil
    } else if value, ok := value.(string); ok {
        *(*time.Time)(n), _ = time.Parse(time.RFC3339, value)
        return nil
    }
    return fmt.Errorf("NullTime: cannot scan to time.Time: %T", value)
}

func (n *NullTime) Value() (driver.Value, error) {
    if n == nil || (*time.Time)(n).IsZero() {
        return nil, nil
    }
    return (*time.Time)(n).UTC().Format(time.RFC3339), nil
}

Why there should be a NULL database value check in Scan() if the timestamp columns are NOT NULL? Why do you insert a NULL value in Value() method?

[benbjohnson]

This code was actually refactored out of another project where I was using Postgres and had NULL timestamp fields. Honestly, I'll probably remove the NullTime type for wtf and just use string to scan into and then parse that. It's a little repetitive but it's more clear in the scanning code then calling out to a separate type.