If there is one topic in SQLite that trips up beginners more than almost anything else, it is date and time handling. Unlike some database systems that have a dedicated, strictly enforced DATETIME type, SQLite takes a much more flexible approach, and that flexibility can feel confusing at first. Once you understand how SQLite actually stores and processes dates, though, working with them becomes straightforward and genuinely pleasant. In this article, I am going to explain exactly how SQLite handles date and time values, walk through its built-in date functions, and show plenty of practical examples along the way.
SQLite Has No Dedicated Date Type
The first thing to understand is that SQLite does not have a distinct storage class for dates or times. If you look at SQLite’s type system, you will find only five storage classes: NULL, INTEGER, REAL, TEXT, and BLOB. There is no DATE or DATETIME storage class at all. Instead, SQLite lets you store date and time information using any of three different representations, and it is entirely up to you as the developer to pick one and use it consistently:
Text format, following the ISO 8601 standard, like '2024-06-15' or '2024-06-15 14:30:00'. This is the most common and most human-readable approach.
Real number format, storing the date as a Julian day number, which is the number of days since noon on November 24, 4714 BC in the proleptic Gregorian calendar. This is a floating point number, so it can represent fractional days, which means it can also encode time-of-day information.
Integer format, storing the date as Unix time, meaning the number of seconds since 1970-01-01 00:00:00 UTC. This is a familiar format for anyone who has worked with timestamps in other programming contexts.
None of these is inherently “correct.” Each has trade-offs, and SQLite’s built-in date and time functions can work with all three interchangeably.
Choosing a Storage Format
In my own projects, I default to the ISO 8601 text format for most use cases, because it is human-readable when you inspect the database directly, sorts correctly using ordinary string comparison (since the format goes from largest to smallest unit), and works seamlessly with all of SQLite’s date functions.
CREATE TABLE events (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
event_date TEXT NOT NULL -- stored as '2024-06-15' or '2024-06-15 14:30:00'
);
I reach for Unix timestamps (stored as INTEGER) instead when I care most about compact storage, fast numeric comparisons, or interoperability with systems and languages that already work natively with Unix time, like JavaScript’s Date.now() divided by 1000, or Python’s time.time().
Julian day numbers, stored as REAL, are less commonly used directly by application developers, but they are what SQLite uses internally when performing date arithmetic, so understanding they exist helps make sense of how the date functions work behind the scenes.
The Built-In Date and Time Functions
SQLite gives you five core functions for working with dates and times: DATE(), TIME(), DATETIME(), JULIANDAY(), and STRFTIME(). All five accept a time value followed by zero or more modifiers, which let you adjust the resulting date or time.
DATE()
Returns the date portion in YYYY-MM-DD format:
SELECT DATE('now'); -- current UTC date
SELECT DATE('2024-06-15 14:30:00'); -- '2024-06-15'
SELECT DATE('now', 'localtime'); -- current date in local timezone
TIME()
Returns the time portion in HH:MM:SS format:
SELECT TIME('now'); -- current UTC time
SELECT TIME('2024-06-15 14:30:00'); -- '14:30:00'
DATETIME()
Returns both the date and time together, in YYYY-MM-DD HH:MM:SS format:
SELECT DATETIME('now');
SELECT DATETIME('now', 'localtime');
JULIANDAY()
Returns the Julian day number as a floating point value, which is especially useful for calculating the difference between two dates:
SELECT JULIANDAY('2024-06-15') - JULIANDAY('2024-06-01'); -- 14.0 (days between)
STRFTIME()
This is by far the most powerful and flexible of the group, letting you format a date or time using custom format strings similar to the C standard library’s strftime():
SELECT STRFTIME('%Y-%m-%d', 'now'); -- '2024-06-15'
SELECT STRFTIME('%H:%M', 'now'); -- '14:30'
SELECT STRFTIME('%Y', birth_date) FROM people; -- extract just the year
SELECT STRFTIME('%w', 'now'); -- day of week (0 = Sunday)
SELECT STRFTIME('%s', 'now'); -- Unix timestamp as text
Common format specifiers include %Y for a four-digit year, %m for a two-digit month, %d for a two-digit day, %H, %M, %S for hours, minutes, and seconds, %w for the day of the week as a number, and %j for the day of the year.
Using Modifiers
All five date/time functions accept optional modifiers after the initial time value, letting you shift dates forward or backward, adjust to the start of a period, or convert between UTC and local time.
Adding or subtracting time:
SELECT DATE('now', '+7 days'); -- one week from today
SELECT DATE('now', '-1 month'); -- one month ago
SELECT DATETIME('now', '+3 hours'); -- three hours from now
SELECT DATE('now', '+1 year'); -- one year from today
Rounding to the start of a period:
SELECT DATE('now', 'start of month'); -- first day of the current month
SELECT DATE('now', 'start of year'); -- January 1st of the current year
SELECT DATETIME('now', 'start of day'); -- midnight today
Timezone conversions:
SELECT DATETIME('now', 'localtime'); -- convert from UTC to local time
SELECT DATETIME('2024-06-15 12:00:00', 'utc'); -- convert from local to UTC
You can chain multiple modifiers together, and they are applied in order from left to right:
SELECT DATE('now', 'start of month', '+1 month', '-1 day');
-- gives you the last day of the current month
That last example is a pattern I use constantly when generating monthly reports; it is a clean, reliable way to calculate the final day of any given month without worrying about how many days each month actually has, including leap years.
Practical Examples
Let me walk through some real scenarios I run into regularly.
Storing the current timestamp when a row is created:
CREATE TABLE orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_name TEXT NOT NULL,
created_at TEXT DEFAULT (DATETIME('now'))
);
Using a default expression like this means you do not have to remember to set the timestamp manually every time you insert a new row.
Finding all records from the last 30 days:
SELECT * FROM orders
WHERE created_at >= DATE('now', '-30 days');
Calculating someone’s age from a birth date:
SELECT
name,
CAST((JULIANDAY('now') - JULIANDAY(birth_date)) / 365.25 AS INTEGER) AS age
FROM people;
Grouping records by month for a report:
SELECT
STRFTIME('%Y-%m', order_date) AS month,
COUNT(*) AS total_orders,
SUM(total_amount) AS revenue
FROM orders
GROUP BY month
ORDER BY month;
Finding records that fall on a weekend:
SELECT * FROM events
WHERE STRFTIME('%w', event_date) IN ('0', '6'); -- Sunday or Saturday
Calculating the number of days between two dates:
SELECT JULIANDAY(end_date) - JULIANDAY(start_date) AS days_elapsed
FROM projects;
Working With Dates From Application Code
When you are inserting dates from your application rather than generating them inside SQL, always format them consistently, ideally using the ISO 8601 format SQLite expects. Here is how that looks in Python:
from datetime import datetime
now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
cursor.execute("INSERT INTO events (title, event_date) VALUES (?, ?)", ("Team Meeting", now))
And here is a query pulling that data back and converting it to a native Python datetime object:
cursor.execute("SELECT event_date FROM events WHERE id = ?", (1,))
row = cursor.fetchone()
event_datetime = datetime.strptime(row[0], '%Y-%m-%d %H:%M:%S')
Keeping the string format consistent between what your application writes and what it expects to read back is essential, since SQLite itself will not validate or reformat inconsistent date strings for you; it simply stores whatever text you give it.
Common Pitfalls
Mixing storage formats within the same column. If some rows store dates as '2024-06-15' and others store them as Unix timestamps like 1718409600, your date functions and comparisons will produce inconsistent, confusing results. Pick one format per column and enforce it consistently, ideally at the application layer.
Forgetting about timezones. DATE('now') and DATETIME('now') return UTC time by default, not your local time. This catches people off guard constantly. If you want local time, you need to explicitly add the 'localtime' modifier.
Assuming SQLite validates date strings. SQLite will happily store the string 'not a real date' in a column intended for dates, since there is no dedicated date type enforcing the format. Application-level validation is your responsibility.
Off-by-one errors with date ranges. When filtering for “today,” remember that a DATETIME column includes time-of-day information, so WHERE event_date = DATE('now') will not match rows that have a time component. You typically need a range comparison instead:
SELECT * FROM events
WHERE event_date >= DATE('now') AND event_date < DATE('now', '+1 day');
Not accounting for leap years and varying month lengths in manual calculations. This is exactly why I recommend using SQLite’s built-in modifiers like 'start of month' and '+1 month' rather than trying to hardcode day counts yourself.
Best Practices
Standardize on ISO 8601 text format for date storage unless you have a specific reason to use Unix timestamps or Julian day numbers. Always store dates in UTC and convert to local time only for display purposes, which avoids a whole category of bugs related to daylight saving time and users in different timezones. Use STRFTIME() for custom formatting needs rather than trying to manipulate date strings manually with substring functions. Index date columns you filter or sort by frequently, especially in larger tables, since date range queries are extremely common in most applications. Validate date input at the application layer, since SQLite will not do this for you automatically.
Wrapping Up
SQLite’s approach to dates and times is different from what you might be used to in other database systems, but once you understand that it is built around flexible storage combined with a small but powerful set of conversion and formatting functions, it becomes quite easy to work with. Sticking to a consistent storage format, understanding how modifiers work, and leaning on functions like STRFTIME() and JULIANDAY() for formatting and calculations will cover the vast majority of real-world date handling needs you will run into while building applications on top of SQLite.
