Don't Trust SQLite Timestamps

August 1, 2020 | SQLite

SQLite default timestamps produce a rather unusual output which is not standards-compliant and there are no easy ways to fix that. Let’s say we have an SQLite database which uses the built-in timestamp() function to calculate the default values for a certain column:

CREATE TABLE guest_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL, 
    time TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO guest_log (name) VALUES ('Bogeyman');

Optionally, you can prettify your output by changing a few default options:

.headers on
.mode column

Let’s inspect the data:

SELECT * FROM guest_log;

name        time               
----------  -------------------
Bogeyman    2020-08-02 05:50:44

Looks fine, until we try to parse it with anything that expects a standard timestamp format. Here is an example in Kotlin:

import java.time.LocalDateTime

fun main() {
    val date = LocalDateTime.parse("2020-08-02 05:50:44")
    println(date)
}

Oops, we’ve got an exception:

Exception in thread "main" java.time.format.DateTimeParseException: 
Text '2020-08-02 05:50:44' could not be parsed at index 10

Index 10? That’s a space character. Let’s check the documentation for the parse function:

Obtains an instance of LocalDateTime from a text string such as 2007-12-03T10:15:30. ― Java 11 documentation

It looks like the text string this method expects should consist of:

The ISO_LOCAL_DATE
The letter T (or t, parser is case-insensitive)
The ISO_LOCAL_TIME

In fact, the only date format LocalDateTime supports is ISO 8601. Let’s see if this standard allows spaces:

A single point in time can be represented by concatenating a complete date expression, the letter ‘T’ as a delimiter, and a valid time expression. For example, ‘2007-04-05T14:30’. It is permitted to omit the ‘T’ character by mutual agreement as in ‘200704051430’. Separating date and time parts with other characters such as space is not allowed in ISO 8601. ― Wikipedia

There is no doubt that ISO 8601 doesn’t allow spaces in date strings and Java date parser rightfully fails when it stumbles upon illegal characters. That’s odd, I thought SQLite uses the standard date and time formats. Here is what SQLite documentation says about that:

The date and time functions use a subset of IS0-8601 date and time formats. ― SQLite documentation

That’s very confusing. It seems like Java interpretation is correct and SQLite breaks IS0 8601 by using spaces instead of T. Well, they won’t fix this thing any time soon, that’s for sure. So, what can we do about that?

Let’s reproduce our problem:

select datetime();

datetime() 
-------------------
2020-08-02 06:38:06

So, how can we generate a valid date with SQLite? There are two obvious options:

SELECT strftime('%Y-%m-%dT%H:%M:%SZ'), replace(datetime(), ' ', 'T');

strftime('%Y-%m-%dT%H:%M:%SZ')  replace(datetime(), ' ', 'T')
-----------------------------   -----------------------------
2020-08-02T06:38:06Z            2020-08-02T06:38:06

The replace() option is less heavy-handed because it doesn’t override default date format. It just adds the missing T, but there is no way to add a time zone designator without adding more functions which would make things even more hacky.

Adding Z to the end of a date string is pretty important if we want our dates to be parsed correctly. Missing Z instructs timezone-aware parsers to assume local timezone instead of UTC. The output of date functions in SQLite always assumes UTC by default so it’s generally a good idea to append Z to all date stings generated by SQLite.

Note that LocalDateTime is not timezone-aware, so you should use ZonedDateTime or OffsetDateTime if you want to support ISO 8601 timezones.

So, let’s try put it all together:

CREATE TABLE guest_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL, 
    time TEXT NOT NULL DEFAULT ( strftime('%Y-%m-%dT%H:%M:%SZ') )
);

INSERT INTO guest_log (name) VALUES ('Bogeyman');

SELECT * FROM guest_log;

id  name      time                
--  --------  --------------------
1   Bogeyman  2022-04-28T05:08:52Z

A bit hacky, isn’t it? Databases are fun.

Conclusion

Databases are full of quirks and this particular problem is just a tip of the iceberg. Moving some business logic into a database might seem like a good idea but in many cases it’s not. It’s hard to avoid certain friction between your database and your code, which partly justifies the idea of shrinking the contact surface by keeping your databases as “dumb” as possible.

Another argument for “dumb” databases is rather pragmatic. Most developers don’t know their databases as well as their programming languages of choice. Offloading some business logic into a database shrinks the number of people who could understand the whole system and make informed decisions when it comes to the system development and maintenance.

That said, I really like to push default values and data validation to the database layer, and I often do it in my own personal projects. There is nothing wrong with it, if you know what you’re doing.

External links

Tips and tricks on SQLite