About Blog Notes Photos Portfolio

Don't Trust SQLite Default Timestamps

Programming · SQLite · Aug 1, 2020

There is nothing more satisfying than working with dates. 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
);
.headers on
.mode column
INSERT INTO guest_log (name) VALUES ('Bogeyman');
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 LocalDateTime documentation:

“Obtains an instance of LocalDateTime from a text string such as 2007-12-03T10:15:30.”

As we now know, the format this method expects should consist of:

  • The ISO_LOCAL_DATE
  • The letter T (parsing is case insensitive)
  • The ISO_LOCAL_TIME

So, Java’s LocalDateTime parser expects ISO-8601 formatted timestamps by default. Let’s see what this standard says about 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”

It’s clear now that ISO 8601 does not allow spaces and Java parser rightfully fails on such an input.

Strange. I thought SQLite uses 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.”

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?

There are two obvious options:

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

In my opinion, strftime option seems less hacky but you won’t be able to use any of those options inside the table definitions. SQLite does not allow us to use functions to calculate the default values. The only way to solve this problem is to stop using default values and use triggers instead. Something like that would work:

CREATE TRIGGER guest_log_after_insert
  AFTER INSERT ON guest_log
BEGIN
  UPDATE guest_log
  SET time = strftime('%Y-%m-%dT%H:%M:%S')
  WHERE id = new.id;
END;

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

Conclusion

It’s a common knowledge that 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. In many cases, it’s preferable to let your programming language and its tooling to generate the default values and to do most of the validations.

Another argument for “dumb” databases is rather pragmatic. There are less developers who know both a language X and a database Y well enough than the developers who know only the language X. Offloading some business logic into a database shrinks the number of people who could understand the whole system and make the informed decisions when it comes to its development and maintenance.

Programming   SQLite   Kotlin

This page doesn't show ads and the reasons are simple:

  • Most people don't want to see ads (what a surprise)
  • Ads can track you and violate your privacy
  • Ads is the main reason why many websites are so slow

If you find this content valuable or you want to see more content like this, you can leave a tip with bitcoin:

34CXtg7c4Vbw8DZjAwFQVsrbu9eDEbTzbA
bitcoin tips QR