SQLite stores date and time values using one of three formats: TEXT, INTEGER, or REAL.
Here’s how you might see that in action. Let’s say we have a table events with a timestamp column.
CREATE TABLE events (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
timestamp TEXT -- Or INTEGER, Or REAL
);
INSERT INTO events (name, timestamp) VALUES
('Meeting Start', '2023-10-27 10:00:00'),
('Meeting Start', 1698374400), -- Unix epoch time for 2023-10-27 10:00:00 UTC
('Meeting Start', 2459875.5); -- Julian day number for 2023-10-27 10:00:00 UTC
In this example, we’ve inserted the same point in time into the timestamp column three different ways, demonstrating SQLite’s flexibility. The YYYY-MM-DD HH:MM:SS format is a TEXT string, the 1698374400 is an INTEGER representing Unix epoch time (seconds since 1970-01-01 00:00:00 UTC), and 2459875.5 is a REAL number representing the Julian day (days since noon Universal Time on January 1, 4713 BC).
SQLite doesn’t have a dedicated DATE or DATETIME data type. Instead, it accepts these three formats and provides a rich set of built-in date and time functions that can operate on them, regardless of how the data was stored. This means you can query your data using functions like strftime, date, time, datetime, julianday, and unixepoch without needing to know the underlying storage format of the column.
For instance, to get the date part of all entries:
SELECT name, date(timestamp) FROM events;
Or to find events within a specific hour:
SELECT name, timestamp FROM events
WHERE time(timestamp) BETWEEN '10:00:00' AND '11:00:00';
The real power here is that SQLite’s date/time functions are designed to interpret all three formats intelligently. When you pass a TEXT value like '2023-10-27 10:00:00', it recognizes the ISO8601 format. When you pass an INTEGER, it assumes it’s Unix epoch time. And a REAL number is treated as a Julian day. This unified handling simplifies data management significantly.
The Julian day number is a continuous count of days and fractions of days since the beginning of recorded history in the Julian calendar. It’s a very precise way to represent time, and SQLite’s julianday() function can convert any of its recognized date/time formats into this number. This is useful for calculating time differences accurately, as you’re essentially dealing with floating-point arithmetic.
Consider calculating the difference in days between two events stored in different formats:
-- Assuming another event with id 2 exists
SELECT
julianday(timestamp) - julianday((SELECT timestamp FROM events WHERE id = 1)) AS days_difference
FROM events
WHERE id = 2;
This query works seamlessly whether the timestamp column for both events is TEXT, INTEGER, or REAL, because julianday() can parse them all.
The most commonly used formats are TEXT in ISO8601 (YYYY-MM-DD HH:MM:SS) or INTEGER as Unix epoch time. TEXT is human-readable and easily sortable lexicographically if formatted correctly, while INTEGER (Unix epoch) is compact and efficient for calculations. REAL (Julian day) is less common for direct storage but is the underlying representation used by many of SQLite’s date/time functions for internal calculations due to its precision.
When you use strftime, you can format dates into any string representation you desire. For example, to get the date in MM/DD/YYYY format:
SELECT strftime('%m/%d/%Y', timestamp) FROM events WHERE id = 1;
-- Output might be: 10/27/2023
The underlying storage format you choose can impact performance and storage size, but the flexibility of the date/time functions means you can often switch formats later or query across mixed formats without much pain. However, for consistency and predictability in your application logic, it’s generally best to pick one format and stick with it for all your date and time data.
If you’re performing a lot of date-based arithmetic, storing timestamps as Unix epoch integers or Julian day numbers (REAL) might offer a slight performance advantage over TEXT, as conversion from ISO8601 strings can involve more processing. However, for many applications, the human readability and ease of insertion of TEXT format often outweigh these minor performance differences.
The datetime() function is particularly versatile. It can take a date string, a Julian day number, or a Unix epoch time and convert it into the standard YYYY-MM-DD HH:MM:SS TEXT format. If the input is a Julian day number, it assumes it represents UTC time. If it’s a Unix epoch time, it also assumes UTC. If it’s a TEXT string, it attempts to parse it, and if no timezone information is present, it assumes local time.
You can also perform date/time arithmetic directly. For example, to find the date one week from today:
SELECT date('now', '+7 days');
This capability, along with the ability to handle various storage formats transparently, makes SQLite a surprisingly powerful tool for managing temporal data, even without dedicated date/time types.
The next step is often dealing with timezones, which SQLite’s built-in functions handle in a very specific, often UTC-centric, manner.