SQLite - Date & Time

SQLite - Date & Time

SQLite does not have dedicated DATE or TIME data types. Instead, it stores dates and times as TEXT (ISO 8601 strings), REAL (Julian day numbers), or INTEGER (Unix timestamps). A powerful set of built-in functions lets you manipulate these values with ease.

Sr.No.	Function	Example Output
1	date(timestring, modifiers…)	Returns the date as ‘YYYY-MM-DD’
2	time(timestring, modifiers…)	Returns the time as ‘HH:MM:SS’
3	datetime(timestring, modifiers…)	Returns ‘YYYY-MM-DD HH:MM:SS’
4	julianday(timestring, modifiers…)	Returns the Julian day number
5	strftime(format, timestring, modifiers…)	Returns a custom-formatted date/time string

The timestring argument can be a date/time value in a recognized format, or ‘now’ for the current time. Modifiers adjust the timestring value.

Best Practice: Storing Dates

For most applications, it is recommended to store dates and times as TEXT in ISO 8601 format (YYYY-MM-DD HH:MM:SS). This format is human-readable, unambiguous, and sorts correctly as text. Store all times in UTC to avoid timezone issues, and convert to local time in your application layer.

Common Modifiers

Modifiers are applied from left to right to alter a time value.

• N days (or hours, minutes, seconds, months, years): Add or subtract a time unit. Example: '+7 days', '-1 month'.
• start of month (or year, day): Wind the date back to the beginning of the period.
• weekday N: Advances the date to the next weekday N (0=Sunday, 1=Monday, …).
• localtime: Assumes the timestring is UTC and converts it to the local timezone.
• utc: Assumes the timestring is in the local timezone and converts it to UTC.

strftime() Format Specifiers

The strftime() function is incredibly useful for custom formatting. Here are some common format specifiers:

Substitution	Description
%Y	Year: 0000-9999
%m	Month: 01-12
%d	Day of month: 01-31
%H	Hour: 00-23
%M	Minute: 00-59
%S	Second: 00-59
%s	Seconds since 1970-01-01 (Unix timestamp)
%w	Day of week: 0-6 (Sunday is 0)
%j	Day of year: 001-366
%W	Week of year: 00-53

Practical Examples

Let’s assume we have an events table: CREATE TABLE events (name TEXT, event_date TEXT);

-- Get the current date and time (UTC)
SELECT datetime('now');
-- Result: '2024-05-10 14:30:00' (example)

-- Get the date for the first day of the next month
SELECT date('now', 'start of month', '+1 month');
-- Result: '2024-06-01'

-- Get the current Unix timestamp
SELECT strftime('%s', 'now');
-- Result: '1715351400' (example)

-- Format today's date in a friendly format (e.g., 'Friday, May 10, 2024')
SELECT strftime('%A, %B %d, %Y', 'now');

-- Find all events happening in the next 7 days
SELECT * FROM events
WHERE event_date BETWEEN date('now') AND date('now', '+7 days');

-- Calculate the number of days between two dates
SELECT julianday('2025-01-01') - julianday('now');

-- Convert a Unix timestamp to a human-readable datetime
SELECT datetime(1672531200, 'unixepoch');
-- Result: '2023-01-01 00:00:00'

-- Convert a Unix timestamp to local time
SELECT datetime(1672531200, 'unixepoch', 'localtime');