SQLite is a powerful relational database management system used in many applications and devices due to its lightweight design, reliability, and flexibility.
While SQLite does not have built-in date and time data types, it provides robust support for working with dates and times through its date and time functions.
In this comprehensive guide, you’ll learn how to store date and time values in SQLite and manipulate them using SQLite’s datetime functions.
Introduction to Handling Date Time in SQLite
Date and time capabilities are essential for many real-world applications that deal with scheduling, time-series data, log events, etc. SQLite enables storing dates and times as TEXT, REAL or INTEGER values and provides a set of functions like datetime(), date(), time(), julianday(), strftime() etc. for manipulating date/time values.
While SQLite does not have a separate date or time data type, the flexibility of using numeric or string types gives us multiple options to store temporal data efficiently. The powerful date and time functions help us convert between formats and perform date arithmetic with ease.
In this guide, we will explore the following topics in detail with examples:
- Storing dates and times in SQLite tables
- Using
datetime(),julianday(),strftime(),date(),time()functions - Date arithmetics with
datetime()modifiers - Handling timezones
- Best practices for temporal data
To illustrate the examples, we will use a sample database of an e-commerce site that contains orders, products and customer info.
Let’s get started!
Quick Overview of Datetime Functions in SQLite
Here is a quick comparison of the main datetime functions in SQLite:
| Function | Description |
|---|---|
datetime() | Converts values, handles modifiers and date math |
julianday() | Converts datetime to Julian days |
strftime() | Formats datetimes with custom strings |
date() | Extracts date part |
time() | Extracts time part |
Storing Dates and Times in SQLite
SQLite allows storing date/time values as TEXT, REAL or INTEGER:
- TEXT: Store as an ISO-8601 strings
- REAL: Store as Julian day numbers
- INTEGER: Store as Unix timestamps
Each approach has pros and cons. Let’s look at examples of each method.
Storing Date and Time as TEXT in SQLite
To store a date, time or datetime as TEXT, use the ISO-8601 formats:
- Date:
YYYY-MM-DD - Time:
HH:MM:SS - Datetime:
YYYY-MM-DD HH:MM:SS
For example:
CREATE TABLE meetings (
id INTEGER PRIMARY KEY,
title TEXT,
meeting_date TEXT,
start_time TEXT,
end_time TEXT
);
INSERT INTO meetings VALUES
(1, 'Monthly Review', '2023-01-15', '10:00:00', '12:00:00');
This stores the dates and times as formatted strings that are human-readable when queried:
SELECT * FROM meetings;
id title meeting_date start_time end_time
---------- -------------- ------------ ---------- --------
1 Monthly Review 2023-01-15 10:00:00 12:00:00
Pros:
- Human-readable format
- Allows storing just the date or time part if required
Cons:
- Requires parsing and reformatting for computations
- Takes more space than numeric storage
Storing Date and Time as REAL in SQLite
SQLite can store dates and times as Julian day numbers in the REAL type. The Julian day number represents the number of days since noon UTC on November 24, 4714 BCE in the proleptic Gregorian calendar.
For example:
CREATE TABLE meetings (
id INTEGER PRIMARY KEY,
title TEXT,
meeting_datetime REAL
);
-- Get Julian day number
INSERT INTO meetings VALUES
(1, 'Monthly Review', julianday('2023-01-15 10:00:00'));
When queried with the SELECT statement, we need to convert the Julian day number back to a human-readable datetime:
SELECT
id,
title,
datetime(meeting_datetime) AS meeting_datetime
FROM meetings;
id title meeting_datetime
---------- -------------- -------------------
1 Monthly Review 2023-01-15 10:00:00
Pros:
- Compact numeric storage
- Efficient date arithmetic
Cons:
- Require conversion for human readability
- Stores only combined datetime
Storing Datetime as INTEGER in SQLite
SQLite can also store datetimes as the UNIX timestamp – the number of seconds since the Unix epoch 1970-01-01 00:00:00 UTC.
For example:
CREATE TABLE meetings (
id INTEGER PRIMARY KEY,
title TEXT,
meeting_unixtime INTEGER
);
INSERT INTO meetings VALUES
(1, 'Monthly Review', strftime('%s','2023-01-15 10:00:00'));
To make the timestamp human-readable:
SELECT
id,
title,
datetime(meeting_unixtime, 'unixepoch') AS meeting_datetime
FROM meetings;
id title meeting_datetime
---------- -------------- -------------------
1 Monthly Review 2023-01-15 10:00:00
Pros:
- Compact integer storage
- Allows timestamp arithmetic
Cons:
- Limited range -Unable to store dates before 1970
- Require conversion for human readability
As we can see, each storage format has trade-offs. For most use cases, storing as ISO-8601 strings provides the best balance in terms of human readability, storage efficiency and date manipulations using SQLite’s date functions.
Now let’s look at how to use SQLite’s date and time functions to manipulate datetime values efficiently.
Using the datetime() Function
The most versatile date/time function in SQLite is datetime(). It can be used to:
- Format date/time values
- Perform date arithmetic using modifiers
- Convert between storage formats
The syntax is:
datetime(timestring, modifier, modifier, ...)
It accepts a date/time value as the first argument followed by optional modifiers. Let’s see some examples:
Formatting Dates and Times in SQLite
To get the current date/time:
SELECT datetime('now');
2023-02-14 09:30:00
To format a date/time string:
SELECT datetime('2023-02-14 09:30:45');
2023-02-14 09:30:45
Extracting parts of a datetime:
SELECT
date(datetime('2023-02-14 09:30:45')) AS date,
time(datetime('2023-02-14 09:30:45')) AS time;
date time
---------- ----------
2023-02-14 09:30:45
Date and Time Functions in SQLite
SQLite also provides separate date() and time() functions:
date()
The date() function returns just the date part of a datetime:
SELECT date('2023-02-14 09:30:45');
2023-02-14
time()
The time() function returns just the time part:
SELECT time('2023-02-14 09:30:45');
09:30:45
These are convenient for extracting the required date or time component from a datetime value.
Date Arithmetic with Modifiers in SQLite
We can use modifiers with datetime() to perform arithmetic on dates/times:
SELECT
datetime('2023-02-14 09:30:45', '-1 day') AS yesterday,
datetime('2023-02-14 09:30:45', '+1 month') AS next_month;
yesterday next_month
------------------- -------------------
2023-02-13 09:30:45 2023-03-14 09:30:45
Some common modifiers are:
+/- NNN days+/- NNN hours+/- NNN minutes+/- NNN seconds+/- NNN months+/- NNN years
This provides an easy way to do date math in SQLite queries!
Converting Datetime Storage Formats in SQLite
We can use datetime() to convert between storage formats:
SELECT
datetime(julianday('2023-02-14 09:30:45')) AS julianday_format,
datetime(strftime('%s','2023-02-14 09:30:45')) AS unix_format;
julianday_format unix_format
----------------------- -----------------------
2023-02-14 09:30:45 2023-02-14 09:30:45
This provides flexibility to store dates in compact formats but convert to human-readable datetimes when required.
Next, let’s look at other useful date functions like julianday(), strftime(), etc.
Julian Day Functions in SQLite
The julianday() and strftime() functions provide alternate ways to format datetime values.
julianday()
The julianday() function converts a datetime value into a Julian day number:
SELECT julianday('2023-02-14 09:30:45');
2460625.89615794
To make it human-readable, we can pass it into datetime():
SELECT datetime(julianday('2023-02-14 09:30:45'));
2023-02-14 09:30:45
Use cases:
- Storing dates/times in an efficient numeric format
- Performing date arithmetic as day offsets
strftime()
The strftime() function formats datetimes using standard C format specifiers like %Y, %m, etc:
SELECT strftime('%Y-%m-%d', '2023-02-14 09:30:45');
2023-02-14
It supports various formatting options:
SELECT
strftime('%Y', datetime('now')) AS year,
strftime('%m', datetime('now')) AS month,
strftime('%d', datetime('now')) AS day;
year month day
---- ----- ---
2023 02 14
Use cases:
- Custom formatting of dates and times
- Localization of dates using locale-specific formats
Handling Timezones in SQLite
SQLite stores dates and times internally as UTC, but allows converting to local timezones using modifiers:
UTC Timestamps
By default, datetime() returns datetimes in UTC:
SELECT datetime('now');
2023-02-14 17:00:00
Local Time
To get the local timezone datetime, use the localtime modifier:
SELECT datetime('now', 'localtime');
2023-02-14 09:00:00
This converts the UTC timestamp to the local system timezone.
Specifying Timezone Offsets
We can also specify a timezone offset like ‘+05:30’:
SELECT datetime('now', '+05:30');
2023-02-14 12:30:00
This allows for controlling the timezone precisely.
Best Practices for Handling Timezones
Some tips for handling timezones:
- Store timestamps in UTC for consistency
- Apply timezone conversion during formatting for display
- Be aware of daylight savings time transitions if storing local times
- Use a library like pytz for advanced timezone handling in Python
Handling time zones properly is crucial for accurate reporting and analytics.
Best Practices for Temporal Data
Here are some recommended best practices for working with dates and times in SQLite:
- Use ISO-8601 strings for human readability and flexible manipulations.
- Store in UTC for consistency across systems.
- Add timezone columns if storing local times to handle DST properly.
- Use
datetime()for formatting and date arithmetic. - Avoid local time comparisons as results can be ambiguous around DST.
- Index datetime columns for efficient range queries.
- Consider compression (ZIP/RLE) for reducing datetime storage.
- Backup time-series history older than 2 years into archive tables.
- Validate dates before inserting to avoid bad data.
Following these tips will help build robust systems that handle temporal data accurately and efficiently at scale.
Summary
Working with dates and times is a critical aspect of most data-driven applications. In this guide, we covered the key capabilities that SQLite provides for handling datetimes:
- Storing as TEXT, REAL or INTEGER and converting between formats
- Powerful
datetime()function for formatting, arithmetic and conversion - Options like
julianday(),strftime(), etc for specialized needs - Modifiers for timezone handling and offsets
- Best practices to follow
SQLite’s datetime features give developers extensive flexibility to address diverse date and time needs. Whether it is timestamping events, scheduling, temporal analytics or any other use case – SQLite enables building robust systems tailored to your specific requirements.
Hopefully this guide provided a comprehensive overview of working with datetimes in SQLite. The built-in functions combined with SQLite’s flexibility make it a great choice for working with dates and times across platforms and languages.