Encoding rules for SQLite implementation

[pepijnve]

When encoding UUID and TIMESTAMP in a SQLite file there are a number of encoding options. A UUID could be a string or a blob. Timestamps could be ISO-8601, Julian day, UNIX timestamps, etc.

It would be useful to specify which encoding strategy should be used to ensure cross-implementation compatibility.

[Tishj]

You're talking about the data inlining feature, or are you talking about parquet writes?
Or even about serializing partition value information? That already has an issue #43

[pepijnve]

Neither actually, I'm talking about the main catalog tables. Any table that uses uuid or timestamp as column type such as https://ducklake.select/docs/stable/specification/tables/ducklake_schema.html.

Since SQLite does not have builtin support for UUID or timestamp you need to emulate those using either a string or a binary encoding. If I’m writing a catalog, do I just choose whatever I like or should I stick to some specified convention?

edit: clarified answer

[pepijnve]

Scanning through the implementation it looks like UUIDs are passed as strings. For timestamps I found a usage of now() at https://github.com/duckdb/ducklake/blob/a082d0c17f56b2249076208145949f1e39521459/src/storage/ducklake_metadata_manager.cpp#L1622 but that doesn't exist in vanilla SQLite, so no clear answer on that one yet.

[pepijnve]

I did a quick experiment.

Per https://github.com/duckdb/ducklake/blob/main/src/storage/ducklake_metadata_manager.cpp#L53 the timestamp column is actually of type TIMESTAMPTZ, not TIMESTAMP as shown in https://ducklake.select/docs/stable/specification/tables/ducklake_snapshot.

Inserting now() into a TIMESTAMPTZ column in a sqlite database using duckdb gets me the following result. Looks like ISO-8601 string serialization. If that's correct, then I think duckdb/ducklake#130 might be broken since it's performing timestamp comparisons with timezone suffixes using string comparison. I might be mistaken, but I don't think that gives you the correct answer in the general case when the timezone offsets differ between the compared values.

pepijn@Flandrien ~ % duckdb
DuckDB v1.3.2 (Ossivalis) 0b83e5d2f6
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D ATTACH 'new_sqlite_database.db' AS sqlite_db (TYPE sqlite);
D CREATE TABLE sqlite_db.tbl (id INTEGER, value TIMESTAMPTZ);
D INSERT INTO sqlite_db.tbl VALUES (0, NOW());
D .quit
pepijn@Flandrien ~ % sqlite3 new_sqlite_database.db
SQLite version 3.43.2 2023-10-10 13:08:14
Enter ".help" for usage hints.
sqlite> select * from tbl ;
0|2025-07-09 09:11:18.98+02
sqlite> select value, typeof(value) from tbl;
2025-07-09 09:11:18.98+02|text

[Mytherin]

All types that are unsupported should be stored as text using a "standard" serialization (i.e. UUIDs get serialized as the standard text serialization, dates/timestamps using ISO-8601, etc). We should standardize this text serialization as part of the DuckLake specification - I think that makes sense and is also required elsewhere (for e.g. min/max statistics, partitioning information and in the future for data inlining).

Inserting now() into a TIMESTAMPTZ column in a sqlite database using duckdb gets me the following result. Looks like ISO-8601 string serialization. If that's correct, then I think duckdb/ducklake#130 might be broken since it's performing timestamp comparisons with timezone suffixes using string comparison. I might be mistaken, but I don't think that gives you the correct answer in the general case when the timezone offsets differ between the compared values.

That makes sense - that only works if the users' time zone never changes. We should do a cast to timestamp and do the comparison there instead.

Scanning through the implementation it looks like UUIDs are passed as strings. For timestamps I found a usage of now() at https://github.com/duckdb/ducklake/blob/a082d0c17f56b2249076208145949f1e39521459/src/storage/ducklake_metadata_manager.cpp#L1622 but that doesn't exist in vanilla SQLite, so no clear answer on that one yet.

SQLite seems to support CURRENT_TIMESTAMP as an equivalent.

[pepijnve]

SQLite seems to support CURRENT_TIMESTAMP as an equivalent.

If I'm reading the documentation correctly (and based on a quick test) CURRENT_TIMESTAMP only works as default value as part of a table definition. In queries you could use strftime('%Y-%m-%dT%H:%M:%f') to get an ISO-8601 timestamp (i.e. UTC) value.

[Tishj]

SQLite seems to support CURRENT_TIMESTAMP as an equivalent.

If I'm reading the documentation correctly (and based on a quick test) CURRENT_TIMESTAMP only works as default value as part of a table definition. In queries you could use strftime('%Y-%m-%dT%H:%M:%f') to get an ISO-8601 timestamp (i.e. UTC) value.

sqlite> select current_timestamp;
2025-07-12 14:53:53
sqlite> SELECT datetime('now');
2025-07-12 14:56:03

or

sqlite> SELECT datetime('now', 'localtime');
2025-07-12 17:06:20

[pepijnve]

Huh, I had tried select current_timestamp and got an error, but I retested and it does work. Must have made a typo. That works as well then indeed if seconds is sufficient. The strftime variant gets you fractional seconds which is closer to what duckdb writes into sqlite files when using now().