Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 33 ยท Types and Tables

Dates, Times and Booleans

SQLite has no date or boolean type. Learn the three ways to store time, the date and time functions, modifiers like start of month, and how 0 and 1 act as booleans.

If you come from PostgreSQL or MySQL, you may go looking for a DATE or BOOLEAN column type in SQLite and be surprised: there is none. SQLite stores only the five storage classes you met earlier. A "date" is just a piece of text or a number that follows a convention, and a "boolean" is the integer 0 or 1.

That sounds like a weakness, but it works well in practice because SQLite ships a small set of date and time functions that read those conventions, plus TRUE and FALSE keywords. In this lesson you will pick a storage format for timestamps, format and compute dates with date(), datetime(), strftime() and modifiers, convert to and from Unix time, and handle booleans without surprises.

Three ways to store a moment

SQLite's documentation blesses three formats. All of them work with every date function.

Format Storage class Example Strength
ISO-8601 text TEXT '2025-03-15 14:30:45' Readable, sorts correctly as text
Unix time INTEGER 1742049045 Compact, easy arithmetic in seconds
Julian day REAL 2460750.1046 Days as a fraction, easy day math

For most applications, use ISO-8601 text. When you look at a row in a database browser you can read it, and because the year comes first and every field has a fixed width, ordinary text comparison puts timestamps in chronological order. Unix integers are a good second choice when you do a lot of arithmetic or need to exchange data with systems that expect epoch seconds. Julian day numbers are mostly useful for differences between dates.

Pick one format, stay with it

ISO text Readable and sortable. The default choice.
Unix integer Seconds since 1970. Compact.
Julian day A real number of days. Good for gaps.

Mixing formats in one column is the real danger.

1 / 2

Here is a table that stores the same event in two formats, and a query that sorts by each:

SQL The same moment, two formats

Press Run (or Ctrl + Enter) to see the result here.

The text column sorts correctly without any date function, which is the main reason ISO text is such a good default.

Always write the full ISO form with zero padding: 2025-03-05, not 2025-3-5. A text such as '3/5/2025' is not understood by any date function and will not sort in time order. Functions return NULL for text they cannot parse.

The five date and time functions

SQLite has five main functions. They all take a time value (one of the three formats, or the word 'now') followed by any number of modifiers.

Function Returns Example result
date(t, ...) YYYY-MM-DD 2025-03-15
time(t, ...) HH:MM:SS 14:30:45
datetime(t, ...) YYYY-MM-DD HH:MM:SS 2025-03-15 14:30:45
julianday(t, ...) Julian day number (REAL) 2460750.10469
unixepoch(t, ...) Seconds since 1970 (INTEGER) 1742049045

strftime(format, t, ...) is the general one: it formats a time value with % codes. The most useful codes are %Y (year), %m (month), %d (day), %H, %M, %S, %w (weekday, 0 = Sunday), %j (day of year), %W (week of year) and %s (Unix seconds).

'now' gives the current moment in UTC. It is evaluated once per statement, so every call to 'now' in one query returns the same instant. (We avoid 'now' in the live examples because their output changes every run.)

SQL Formatting a timestamp

Press Run (or Ctrl + Enter) to see the result here.

Notice the last column: a date that does not exist (month 13) produces NULL, not an error. When a computed date unexpectedly disappears, check for a malformed input first.

When a number is given as the time value, SQLite treats it as a Julian day unless you add the 'unixepoch' modifier, which is why datetime(1742049045, 'unixepoch') needs it.

Modifiers: date arithmetic

Modifiers follow the time value and are applied left to right. They add or subtract, jump to the start of a period, or move to a weekday.

  • '+90 minutes', '-3 days', '+1 month', '+2 years': add or subtract an amount (units may be singular or plural).
  • 'start of month', 'start of year', 'start of day': truncate.
  • 'weekday N': move forward to the next day with weekday number N (0 = Sunday), staying put if it already matches.
  • 'unixepoch': interpret a number as Unix seconds.
  • 'localtime' and 'utc': convert between UTC and the machine's local time zone.

Because modifiers chain, you can build the classic "last day of the month" with a three-step recipe: go to the start of the month, add one month, subtract one day.

SQL Chaining modifiers

Press Run (or Ctrl + Enter) to see the result here.

The result 2025-03-31 is the last day of March, and days_between is 73: the number of days from 1 January to 15 March.

Month arithmetic overflows

Adding months to a date that does not exist in the target month rolls forward rather than clamping. date('2025-01-31', '+1 month') is 2025-03-03, because 31 February "overflows" into March. SQLite 3.46 and later give you two modifiers to choose a behavior explicitly: 'floor' clamps to the last valid day, and 'ceiling' is the explicit name for the default rollover.

SQL Month overflow, floor and ceiling

Press Run (or Ctrl + Enter) to see the result here.

The default gives 2025-03-03, floor gives 2025-02-28 and ceiling gives 2025-03-03 again. Billing and subscription logic usually wants the clamped version.

Measuring the gap: timediff()

Since SQLite 3.43, timediff(a, b) returns the amount of time you must add to b to reach a, written as a sign followed by YYYY-MM-DD HH:MM:SS.SSS. The year, month and day parts are calendar differences, not a day count, so for "how many days" the julianday() subtraction shown above is still the better tool.

SQL timediff() and julianday()

Press Run (or Ctrl + Enter) to see the result here.

The results are +0000-00-01 02:30:00.000, +0000-00-00 00:00:00.250, -0000-02-14 14:30:00.000 and 73. A negative sign means the first argument is earlier than the second. If your SQLite is older than 3.43, the function does not exist, and you can compute the same gap yourself with julianday() or unixepoch() differences.

Time zones, in one paragraph

SQLite does not know about time zone names. It only knows UTC and "the local time of the machine running the code", reached with 'localtime'. A sensible rule for real applications: store UTC, convert for display, and do the conversion in your application code where a proper time zone database is available. Do not store local times without an offset, because you cannot recover the instant later.

Querying by date

With ISO text, range queries are simple string comparisons, and a half-open range avoids off-by-one mistakes with the time part:

SQL Orders in one month ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

strftime('%Y-%m', ...) is the idiom for grouping by month. When the column is indexed, prefer the plain range form in WHERE, because wrapping the column in a function prevents the index from being used (indexes are covered later in this tutorial).

If you have already seen date handling in the SQL tutorial, the same ideas apply, but the function names and modifiers here are SQLite's own.

Booleans are integers

SQLite has no boolean storage class. Comparisons such as 1 = 1 return the integers 1 and 0. Since SQLite 3.23 the keywords TRUE and FALSE are simply aliases for 1 and 0, so they work when you write portable SQL.

  • Declare a boolean column as INTEGER (or BOOLEAN, which just gives it numeric affinity) and add a CHECK (col IN (0, 1)) so nothing else gets in.
  • NOT turns zero into 1 and any other non-zero number into 0, and NULL stays NULL.
  • Use IS TRUE, IS NOT TRUE and so on to treat NULL as "not true" instead of unknown.
SQL A boolean column with a CHECK

Press Run (or Ctrl + Enter) to see the result here.

The first query returns the two unfinished tasks, and stored_as shows integer: the keywords left no trace of a separate type. In the second query, NULL = 1 is NULL (displayed empty), but NULL IS NOT TRUE is 1.

A common mistake is inserting 'true' or 'yes' as text. Without a CHECK (or a STRICT table) SQLite happily stores it, and your later WHERE done = 1 silently misses those rows.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which column type should you declare to store a date in SQLite?

    SQLite has no date storage class. Names like DATE only affect affinity; the actual value is text, an integer or a real number, and the date functions interpret it.
  2. 2. What does date('2025-03-15', 'start of month', '+1 month', '-1 day') return?

    Modifiers apply left to right. Start of month gives 2025-03-01, plus one month gives 2025-04-01, minus one day gives 2025-03-31.
  3. 3. Why does datetime(1742049045) not give the expected calendar date?

    A bare number is a Julian day. Adding 'unixepoch' tells SQLite the number is seconds since 1970.
  4. 4. What is stored when you insert TRUE into an INTEGER column?

    TRUE and FALSE are keywords for 1 and 0. There is no boolean storage class.

Summary

  • SQLite has no date or boolean type. Dates are TEXT (ISO-8601), INTEGER (Unix seconds) or REAL (Julian day); booleans are 0 and 1.
  • Prefer ISO-8601 text with full zero padding: it is readable and sorts chronologically.
  • date(), time(), datetime(), julianday(), unixepoch() and strftime() all accept a time value plus chained modifiers.
  • Invalid dates return NULL; 'now' is UTC and fixed within one statement.
  • Month arithmetic can overflow; use 'floor' or 'ceiling' (SQLite 3.46+) to be explicit.
  • Store UTC and convert for display. Add CHECK (col IN (0, 1)) to boolean columns.

Next: Querying in SQLite, a tour of SELECT with the details that are specific to SQLite.