How to Use and Store Dates in SQLite

Posted on by Ian

SQLite is a lightweight, self-contained relational database management system that is widely used due to its simplicity and portability. However, one notable aspect of SQLite is its lack of a dedicated DATE or DATETIME storage class. Instead, SQLite stores date and time values as integers, real numbers, or text, depending on how the developer chooses to manage these values.

In this article, we’ll explore how to effectively use and store dates in SQLite, and provide examples that may help you implement date management in your projects.

Understanding How SQLite Handles Dates

SQLite does not have a specific storage class for dates or times. Instead, it allows dates and times to be stored in any of the following formats:

  • TEXT: ISO 8601 format (YYYY-MM-DD HH:MM:SS.SSS), which is human-readable and easy to work with.
  • REAL: A Julian day number, represented as a floating-point value. This format is useful for performing date arithmetic. The Julian day is the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
  • INTEGER: The Unix epoch time, stored as the number of seconds since 1970-01-01 00:00:00 UTC. This is compact and efficient for storage and computation.

SQLite provides built-in date and time functions to manipulate and convert between these formats, ensuring flexibility when working with dates.

Choosing a Format for Date Storage

The choice of format for storing dates depends on your specific use case:

  • TEXT: Use this format if you need human-readable dates or require easy compatibility with ISO standards.
  • REAL: Use this format for calculations involving date and time differences.
  • INTEGER: Use this format if storage space is critical, or if you need fast comparison and arithmetic operations.

In most cases, the TEXT format (ISO 8601) is suggested for simplicity, readability, and compatibility across systems.

SQLite Date and Time Functions

SQLite provides several built-in functions to work with dates and times. These include:

  • DATE(): Returns the date in YYYY-MM-DD format.
  • TIME(): Returns the time in HH:MM:SS format.
  • DATETIME(): Returns the date and time in YYYY-MM-DD HH:MM:SS format.
  • JULIANDAY(): Returns the Julian Day number as a floating-point value.
  • UNIXEPOCH(): Returns the number of seconds since 1970-01-01 00:00:00 UTC.
  • STRFTIME(): Allows custom formatting of date and time values.

These functions can take different inputs, including ISO 8601 strings, Unix epoch times, and Julian day numbers.

Examples of Storing and Using Dates in SQLite

Let’s explore practical examples for working with dates in SQLite.

Example 1: Storing Dates as TEXT

To store and retrieve dates in TEXT format, you can use the ISO 8601 standard.

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_name TEXT NOT NULL,
    event_date TEXT NOT NULL
);

INSERT INTO events (event_name, event_date)
VALUES ('Meeting', '2024-06-01 14:00:00'),
       ('Conference', '2024-06-15 09:30:00');

SELECT * FROM events;

Output:

id  event_name  event_date         
--  ----------  -------------------
1   Meeting     2024-06-01 14:00:00
2   Conference  2024-06-15 09:30:00

In this example, event_date is stored as a TEXT field using the YYYY-MM-DD HH:MM:SS format. This format ensures easy sorting and filtering based on date.

Example 2: Storing Dates as INTEGER (Unix Epoch Time)

To store dates as Unix epoch time, convert the date to an integer representing the number of seconds since 1970-01-01 00:00:00 UTC.

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_name TEXT NOT NULL,
    event_timestamp INTEGER NOT NULL
);

INSERT INTO events (event_name, event_timestamp)
VALUES ('Meeting', strftime('%s', '2024-06-01 14:00:00')),
       ('Conference', strftime('%s', '2024-06-15 09:30:00'));

SELECT * FROM events;

In this example, STRFTIME('%s', 'date') converts a date string into Unix epoch time.

Here’s the output of the SELECT statement:

id  event_name  event_timestamp
--  ----------  ---------------
1   Meeting     1717250400     
2   Conference  1718443800

We can use the DATETIME() function to convert the Unix epoch time back to a human-readable format:

SELECT 
    event_name, 
    datetime(event_timestamp, 'unixepoch') AS event_date 
FROM events;

Output:

event_name  event_date         
----------  -------------------
Meeting     2024-06-01 14:00:00
Conference  2024-06-15 09:30:00

Example 3: Performing Date Calculations

SQLite allows you to perform date arithmetic using the DATE(), DATETIME(), and JULIANDAY() functions. For example:

SELECT date('2024-06-01', '+1 day') AS next_day,
       date('2024-06-01', '+1 month') AS next_month,
       date('2024-06-01', '-1 month') AS last_month;

Output:

next_day    next_month  last_month
----------  ----------  ----------
2024-06-02  2024-07-01  2024-05-01

Example 4: Calculating the Difference Between Dates

You can calculate the difference between two dates using the JULIANDAY() function.

SELECT (julianday('2024-06-15') - julianday('2024-06-01')) AS days_difference;

Output:

days_difference
---------------
14.0

This query returns the number of days between 2024-06-01 and 2024-06-15.

Example 5: Filtering Rows Based on Date Conditions

You can filter rows using the WHERE clause with date comparisons.

SELECT * FROM events
WHERE event_date >= '2024-06-01' AND event_date < '2024-07-01';

This query retrieves all events happening in June 2024. This approach works seamlessly when dates are stored as TEXT in ISO 8601 format.

Converting Between Formats

SQLite makes it easy to convert between date formats using its functions. Here is an example:

SELECT datetime(1717231200, 'unixepoch') AS human_readable_date,
       strftime('%s', '2024-06-01 14:00:00') AS unix_epoch;

Output:

human_readable_date  unix_epoch
-------------------  ----------
2024-06-01 08:40:00  1717250400

This query converts Unix epoch time (1717231200) to a human-readable format and converts an ISO 8601 date string to Unix epoch time.

Conclusion

While SQLite does not have a dedicated DATE type, it does provide tools to store, retrieve, and manipulate dates using TEXT, REAL, or INTEGER formats. The ISO 8601 TEXT format is often the most versatile and human-readable option, but Unix epoch time offers efficiency for storage and calculations.