SQLite Dates

SQLite columns are flexibly-typed, you can put broadly anything in any column regardless of the declared type of that column. There also isn’t a specific type for dates or times. This means that when it comes to storing dates or times, we’ve got choices to make. I’ll go through the main options in this post.

To start with, we need to provide some context:

  • SQLite lets you put values of any type you want in a column1
  • SQLite doesn’t have date- or time-specific column types
  • The date and time functions in SQLite work with TEXT, REAL and INTEGER values2

These three points might seem contradictory, since we’re saying on one hand that SQLite doesn’t care about types, but also that the date functions work only on certain types. That’s because the date functions have a few specific formats ("time values") that they can handle, so it’s less about the column types and more about the value formats.

The formats

ISO8601 (TEXT) Julian day number (REAL) Unix timestamp (INTEGER)
- - -
Dates as TEXT

If you want to handle dates as text, then you’ll need to format dates as ISO 8601 strings. The ISO standard describes quite a lot of options for formatting dates, but SQLite uses only a few of them. Thankfully it’s the ones I expect most people think of when ISO 8601 gets mentioned.

The full list of accepted formats is available on the SQLite website, but a few examples are:

  • Date and time: 2025-11-01T18:30:00.000
  • Date only: 2025-11-01
  • Time only: 18:30:00.000

These formats can also include a timezone specifier. Internally SQLite works with UTC, so when you use the date functions any dates with a timezone specified will be converted to UTC:

SELECT datetime('2025-11-01 18:30:00.000+09:00') AS utcValue;
-- Returns: '2025-11-01 09:30:00'
Dates as REAL

This one might sound odd to start with, but bear with it. You can store dates as Julian day numbers.

The Julian day number is the number of days since -4713-11-24 12:00:003 and can include decimal places to cover units of less than a day. So it’s really just a timestamp, with the reference date set a long way in the past.

A few examples of Julian dates:

  • 1st January 2000 at 00:00 UTC: 2451544.5
  • 1st November 2025 at 18:30 UTC: 2460981.2708333335
  • The unix epoch date: 2440587.5
Dates as INTEGER

This one’s a bit more common: dates stored as unix time; the number of seconds since 1970-01-01 00:00:00 UTC.

SQLite can’t tell just from looking at a number whether it’s a Julian day number or unix timestamp and by default it assumes numbers are Julian days4. If you use unix time with the date functions you’ll need to tell the function by passing in an extra parameter:

SELECT datetime(1762021800, 'unixepoch');
-- Returns '2025-11-01 18:30:00'
Other formats

You can also store Julian day numbers as INTEGER values, or unix timestamps as decimals if you want. If you only need to store a specific day, not a time, an integer day number works just fine. If you want fractional seconds for a unix timestamp, you can store it as a REAL.

It’s also worth pointing out that you’re not limited to these formats for storage. You can represent dates and times in any way you want. You could choose to use the email date format (Tue, 01 Nov 2025 18:30:00 UTC) or perhaps Cocoa’s TimeInterval (the number of seconds since 1st January 2001 as a decimal). These - and any others you might find - can be saved perfectly well in your tables.

If you do choose a different format though, the built-in date and time functions in SQLite won’t work with your data. This may or may not be a problem for you depending on how you use the database, but it’s important to consider when choosing a format.

Which to choose?

As usual, it depends.

You’ll want to consider things like:

  • How am I interacting with the database? Do I need people to be able to read dates by eye?
  • What systems will be using the database? What is their preferred serialisation format?
  • What precision do I need?
  • Do I care about storage space?

Generally my preference is to store dates as TEXT unless there is a good reason not to. I choose this because:

  • They’re human readable
  • They’re widely compatible with other tools
  • There’s little to no risk of getting them confused with other formats

There are disadvantages though:

  • Converting to/from strings may have performance costs
  • You use more storage5

Most of the time these drawbacks are not very significant to me, so I don’t worry about them.

  1. Unless you’ve chosen to declare a table to be strictly typed ↩︎

  2. See the SQLite documentation on data types ↩︎

  3. You might notice an inconsistency here. The SQLite documentation says that the reference date is “noon in Greenwich on November 24, 4714 B.C.”, but datetime(0) returns -4713-11-24 12:00:00, which has a different year number. That’s because the proleptic Gregorian calendar doesn’t have a year zero, it jumps from 1BC to 1AD, while ISO8601 uses astronomical year numbering which does have a year zero. ↩︎

  4. There is an auto modifier to date/time functions which will let SQLite try to auto-detect the date representation from an integer, based on its size. ↩︎

  5. Sometimes quite a lot more. For the same date: 2025-11-01 18:30:00 is 19 bytes; 2460981.2708333335 is 8 bytes; 1762021800 is 4 bytes ↩︎

← Back to Blog