Simplifying TIMESTAMP Queries with `?_loc=UTC`

[wjkoh]

First and foremost, thank you for the excellent go-sqlite3 package! I use it frequently in my projects and truly appreciate the effort you've put into it. I have a quick suggestion I'd like to propose and would be interested to know if it makes sense.

As we know, storing and querying time.Time values in SQLite3 can be challenging. To make this easier, I've adopted a two-step approach. First, I enforce that every TIMESTAMP column stores only UTC timestamps.

I accomplish this using a CHECK constraint:

CREATE TABLE users(created TIMESTAMP CHECK(created LIKE concat(datetime(created), '%')));

This constraint ensures that only valid UTC timestamps are stored, as shown below:

sqlite> INSERT INTO users(created) VALUES('2025-07-27 03:37:44.556599+09:00');
Runtime error: CHECK constraint failed: created LIKE concat(datetime(created), '%') (19)
sqlite> INSERT INTO users(created) VALUES('2025-07-26 18:54:54.318724+00:00');
sqlite>

With this in place, we can safely assume that every TIMESTAMP field in our database is in the UTC timezone.

Second, when I query with time.Time values, I also have to ensure that they are in UTC. Currently, I have two options:

var start, end time.Time
// Option 1:
db.QueryContext(ctx, `SELECT * FROM users WHERE created >= datetime(?) AND created < datetime(?)`, start, end)
// Option 2:
db.QueryContext(ctx, `SELECT * FROM users WHERE created >= ? AND created < ?`, start.UTC(), end.UTC())

Unfortunately, neither of these options is ideal because it's easy to forget to call datetime() or .UTC(). I am hoping for a more automatic solution.

I noticed the ?_loc=UTC parameter for the Go sqlite3 driver, which at first glance seemed promising. As you know, however, it is currently only used for retrieving timestamp values from the database. I would like to propose extending this parameter to also automatically convert time.Time values to UTC when they are used in a query with created >= ?.

This change would significantly simplify the process and reduce the potential for errors. What are your thoughts on this suggestion?

[rittneje]

First, please note that your constraint doesn't work as desired.

• Any arbitrary (non-timestamp) string will be allowed for Created. For example, INSERT INTO Users (Created) VALUES ('ABC'); works.
• There are two ways of expressing the UTC timezone in the string: "+00:00" or "Z". But mixing the two will not work in your comparisons, unless you call datetime as in your option 1.

For these reasons, you likely want your CHECK constraint to directly verify it looks like a UTC timestamp, instead of calling datetime. Alternatively, store the time as a Unix timestamp (seconds since the epoch) instead of a string.

That aside, this proposal could constitute a breaking change, as existing users may be relying on the timezone being preserved on the way in.

[wjkoh]

Thanks for pointing out the issue with the CHECK constraint!

I've made the CHECK constraint stricter as shown below, and it successfully invalidates the 'ABC' and '...Z' cases. I believe there might be a more elegant solution, but this one appears to work for now. The CHECK constraint makes comparing two TIMESTAMP values accurate up to seconds. Subseconds are tricky because go-sqlite3 formats them with variable length, ranging from zero (no subseconds) to six (microseconds) or nine (nanoseconds).

-- ... LIKE strftime('%F %T%%+00:00', created) also works for the latest SQLite3.
CREATE TABLE users(created TIMESTAMP CHECK (datetime(created) IS NOT NULL AND created LIKE strftime('%Y-%m-%d %H:%M:%S%%+00:00', created)));

sqlite> CREATE TABLE users(created TIMESTAMP CHECK (datetime(created) IS NOT NULL AND created LIKE strftime('%Y-%m-%d %H:%M:%S%%+00:00', created)));

sqlite> INSERT INTO users(created) VALUES('2025-07-26 18:54:54.318724+00:00');
sqlite>

sqlite> INSERT INTO users(created) VALUES('ABC');
Runtime error: CHECK constraint failed: datetime(created) IS NOT NULL AND created LIKE strftime('%Y-%m-%d %H:%M:%S.%%+00:00', created) (19)

sqlite> INSERT INTO users(created) VALUES('2025-07-26 18:54:54.318724Z');
Runtime error: CHECK constraint failed: datetime(created) IS NOT NULL AND created LIKE strftime('%Y-%m-%d %H:%M:%S.%%+00:00', created) (19)

sqlite> INSERT INTO users(created) VALUES('2025-07-27 03:37:44.556599+09:00');
Runtime error: CHECK constraint failed: datetime(created) IS NOT NULL AND created LIKE strftime('%Y-%m-%d %H:%M:%S.%%+00:00', created) (19)

Given this capability, is there a way to automatically enforce the query value to the UTC format or at least validate it with the help of go-sqlite3, such as through hooks, extensions, or something? It doesn't necessarily have to be via ?_loc=UTC, as you mentioned this would be a breaking change. I'm open to any other methods that would simplify TIMESTAMP queries.