If you’ve ever built something with SQLite, you may know how annoying it can be to work with timestamps. There is no timestamp data type in SQLite, which leaves the responsibility of format consistency and string parsing to the application layer. This might be a bit of a curve ball, especially if you’re used to a database with tons of support for different data types (PostgreSQL has 42 data types listed in their documentation!).
In this guide, we’ll outline some best practices for storing and working with timestamps in SQLite, so your app doesn’t think it's 1970.
Why is there no Timestamp data type?
As we've covered here previously in our blog post on types and affinities in SQLite, SQLite takes a more flexible approach to data types than traditional RDBMS, and deliberately omits specialized types like Timestamps. Instead, SQLite allows you to store temporal data in any of three storage classes: TEXT, REAL, or INTEGER.
This choice may seem a bit strange on its face, but it is aligned with SQLite’s overall philosophy of flexibility through simplicity. In general, SQLite’s design emphasizes fewer moving parts that can cover the most amount of use cases. To do so, it necessarily shifts some responsibilities usually handled by your DB engine to the application it is embedded in, like data type validation, timestamp formatting, timezone handling, and date arithmetic. For example, rather than having the database enforce that a "created_at" column must be a valid ISO-8601 timestamp, your application code needs to ensure dates are stored consistently - whether that's as Unix timestamps (INTEGER), ISO-8601 strings (TEXT), or Julian dates (REAL). Your application also needs to handle timezone conversions and ensure consistent date comparisons, since SQLite won't automatically normalize "2024-03-15" and "1710460800" as the same point in time.
While this means more thought is required when storing date times, it allows SQLite to remain lightweight and performant while supporting as many use cases as possible.
Why is storing timestamp as a string complicated?
Storing timestamps as strings introduces several challenges.
First, string comparisons in SQLite are lexicographical, meaning they're compared character-by-character. This can lead to unexpected results when sorting or comparing dates:
SELECT * FROM events
WHERE created_at > '2024-03-15'; -- Works as expected
SELECT * FROM events
WHERE created_at > '2024-3-15'; -- Breaks due to missing leading zero
String storage also makes date arithmetic more complex. Simple operations like "find all events from the last 7 days" become verbose:
-- With string timestamps, complex date math:
SELECT * FROM events
WHERE datetime(created_at) > datetime('now', '-7 days');
-- With INTEGER timestamps, simple math:
SELECT * FROM events
WHERE created_at > (strftime('%s', 'now') - 7*24*60*60);
String timestamps also consume more storage space - an ISO-8601 timestamp like "2024-03-15T14:30:00Z" takes up 20 bytes, while a Postgres Timestamp takes up 8. If storage size is a concern, there are ways to reduce the number of bytes required.
Storage Formats for Timestamps in SQLite
The best all-arounder: ISO-8601 Text
For most applications, storing timestamps as ISO-8601 text strings provides the most robust and maintainable solution. It’s easier to read, and makes debugging and direct database queries easier.
Most modern programming languages include built-in support for parsing and formatting ISO-8601 strings, reducing the need for custom conversion code. The format naturally accommodates timezone information when needed, supports millisecond precision for high-accuracy timing requirements, and eliminates the ambiguity that can arise with integer-based timestamps. A typical ISO-8601 timestamp in SQLite would look like 2025-05-29 14:16:00.000, clearly representing the year, month, day, hour, minute, second, and milliseconds.
The best at scale: Unix Timestamps
While ISO-8601 is the recommended format, Unix timestamps offer compelling advantages in specific scenarios. If your handling a lot of timestamp data, and storage is at a premium, the integer representation of Unix timestamps can significantly reduce storage requirements. Applications primarily dealing with recent dates (post-1970) or performing frequent timestamp calculations may also benefit from the simplified arithmetic possible with Unix timestamps.
Systems that interface primarily with Unix-based systems or APIs may find Unix timestamps provide better compatibility and reduce conversion overhead, too. However, you should exercise caution when using the auto modifier with dates in early 1970, as timestamps from the first 63 days will be misinterpreted as Julian day numbers due to SQLite's automatic format detection rules.
Best Practices for Timestamp Operations
Use the datetime function
SQLite's datetime() function is your swiss army knife for handling timestamps consistently. It provides a reliable way to parse, format, and manipulate temporal data regardless of how it's stored. The function accepts both Unix timestamps and ISO-8601 strings.
– Converting between formats
SELECT datetime(1710460800, 'unixepoch'); -- INTEGER to TEXT
SELECT strftime('%s', '2024-03-15 00:00:00'); -- TEXT to INTEGER
-- Timezone conversions
SELECT datetime('2024-03-15 00:00:00', 'UTC');
SELECT datetime('now', 'localtime');
-- Parsing different formats
SELECT datetime('2024-03-15'); -- date only
SELECT datetime('2024-03-15T14:30:00Z'); -- ISO-8601
The datetime() function also serves as a validator - it will return NULL for invalid dates, helping catch formatting errors early. This is particularly important since SQLite's flexible typing won't prevent storing invalid date strings on its own.
Store in UTC Consistently
Storing timestamps in UTC (Coordinated Universal Time) is fundamental to reliable timestamp handling. It eliminates timezone ambiguity and simplifies calculations and comparisons across different timezones. Plus, no dealing with the pesky complexities of daylight saving time transitions.
Applications should convert timestamps to UTC before storage and maintain them in UTC throughout all database operations. Local time conversions should only occur at the presentation layer when displaying timestamps to users. This can be accomplished directly within SQLite using SQLite's ‘localtime’ modifier:
SELECT datetime(timestamp_column, 'localtime') FROM your_table
Precision Handling
The subsec modifier ensures that datetime functions preserve and display subsecond precision, which is particularly important for applications tracking closely-spaced events or requiring precise time measurements. When storing ISO-8601 strings, always include the millisecond component to maintain consistent precision across all timestamp operations. The precision handling system in SQLite is forward-compatible, meaning that future versions may support even higher precision while maintaining compatibility with existing millisecond-precision timestamps.
Date Calculations
SQLite provides multiple approaches to date arithmetic, each optimized for different types of calculations.
For day-based calculations, the julianday() function provides the most precise results by working with fractional days since 4714 BC. Second-based calculations are best handled using the unixepoch() function, which works with seconds since 1970. When presenting time differences to users, the timediff() function generates human-readable time spans that automatically account for varying month lengths and leap years. The choice of calculation method should match the natural time scale of the operation being performed.
-- Day-based calculations using julianday()
SELECT julianday('now') - julianday('2024-01-01') as days_elapsed;
SELECT date(julianday('now') + 30) as date_in_30_days;
-- Working with fractional days
SELECT julianday('2024-03-15 15:00:00') - julianday('2024-03-15 09:00:00'); -- Returns 0.25 (6 hours)
-- Second-based calculations using unixepoch()
SELECT unixepoch('now') - unixepoch('2024-01-01') as seconds_elapsed;
SELECT datetime(unixepoch('now') + 86400) as tomorrow; -- Add 24 hours in seconds
-- Time differences for display
SELECT (
CASE
WHEN diff < 60 THEN diff || ' seconds ago'
WHEN diff < 3600 THEN (diff/60) || ' minutes ago'
WHEN diff < 86400 THEN (diff/3600) || ' hours ago'
ELSE (diff/86400) || ' days ago'
END) as friendly_time
FROM (
SELECT (unixepoch('now') - unixepoch(created_at)) as diff
FROM events
);
Month and Year Operations
Month and year calculations require some special attention due to the irregular nature of calendar months.
When performing these calculations, explicitly specify either the ceiling or floor modifier to handle ambiguous dates consistently. The 'start of month' and 'start of year' modifiers ensure consistent period boundaries across all calculations. This becomes particularly important when dealing with end-of-month dates or performing calculations that cross month boundaries. For example, adding one month to January 31st could result in either February 28th (or 29th in leap years) or March 3rd, depending on the modifier used.
-- Month and Year Operations
-- Start of current month/year
SELECT date('now', 'start of month');
SELECT date('now', 'start of year');
-- Add months handling edge cases
SELECT date('2024-01-31', '+1 month'); -- Results in 2024-02-29 (leap year)
SELECT date('2023-01-31', '+1 month'); -- Results in 2023-02-28
-- Last day of month calculations
SELECT date(date('now', 'start of month', '+1 month', '-1 day'));
-- Month boundaries
SELECT date('now', 'start of month');
SELECT date('now', 'start of month', '+1 month', '-1 day'); -- end of month
Format Standardization
Implementing consistent timestamp formatting throughout an application will save you time and a lot of headaches. To create a standardized set of timestamp formats, you can use SQLite's strftime() function, and implement helper functions to enforce standards across your app. Just make sure to document your chosen format standards clearly.
-- Create reusable views/functions for consistent formatting
CREATE VIEW v_formatted_dates AS
SELECT
strftime('%Y-%m-%d %H:%M:%S', created_at) as standard_format,
strftime('%Y-%m-%d', created_at) as date_only,
strftime('%H:%M:%S', created_at) as time_only,
strftime('%Y-%m-%dT%H:%M:%SZ', created_at) as iso8601_utc,
strftime('%s', created_at) as unix_timestamp
FROM events;
-- Helper function for consistent date parsing
CREATE FUNCTION format_timestamp(ts TEXT)
RETURNS TEXT AS
BEGIN
RETURN strftime('%Y-%m-%d %H:%M:%S', ts);
END;
-- Function for human-readable dates
CREATE FUNCTION friendly_date(ts TEXT)
RETURNS TEXT AS
BEGIN
RETURN strftime('%b %d, %Y', ts);
END;
-- Example usage:
SELECT
friendly_date(created_at) as post_date,
format_timestamp(updated_at) as last_update
FROM posts;
-- Standardized date comparisons
SELECT * FROM events
WHERE date(created_at, 'start of day') = date('now', 'start of day');
Common Pitfalls to Avoid
Timezone handling is (possibly?) the number one most common source of timestamp-related bugs. Never assume timestamps are in local time without explicit confirmation, and always specify timezone conversions clearly in your queries.
Range limitations in SQLite's timestamp functions should also be carefully considered. While SQLite supports dates between years 0000 and 9999, applications should validate input dates to ensure they fall within this range. Implement appropriate error handling for dates outside the supported range to prevent silent failures or corrupt data.
Precision loss can occur when converting between different timestamp formats or when mixing precision levels within an application. Maintain consistent precision handling throughout your application, and be aware of the precision capabilities of different timestamp formats.
Format ambiguity becomes problematic when mixing different timestamp formats within the same database column. Establish clear conventions for timestamp storage and stick to them consistently.
Performance Considerations
Both ISO-8601 strings and Unix timestamps support efficient indexing, but Unix timestamps may provide better performance for range queries due to their simpler numeric format. Calculation efficiency depends heavily on choosing the right timestamp functions for each operation. Use julianday() or unixepoch() for efficient date math rather than string manipulation. \
You should strive to minimize conversions between different timestamp formats, as these operations can be computationally expensive. Consider caching frequently used calculations, especially when performing complex date arithmetic or generating reports.
-- Cache frequently used calculations
CREATE VIEW v_daily_stats AS SELECT date(created_at) as day, count(*) as event_count, min(created_at) as first_event, max(created_at) as last_event FROM events_unix GROUP BY date(created_at);
-- Using the cached view is more efficient than recalculating SELECT * FROM v_daily_stats WHERE day >= date('now', '-7 days');
Conclusion
Even if we’d all rather be in 1970 (Let it Be AND Bridge Over Troubled Water, anyone?), it’s mission-critical that your code handles timestamps reliably. By following these best practices and understanding SQLite's timestamp capabilities and limitations, you can implement robust and maintainable time-based functionality in your applications.
If you’re already building with SQLite, or just want a performant database with local-first capabilities, sign up for a (free) SQLite Cloud account now!
