SQLite Date and Time Functions

SQLite has no native DATE or DATETIME column type, but it provides a complete set of date and time functions that work with text strings in ISO 8601 format (YYYY-MM-DD HH:MM:SS), Julian day numbers, or Unix timestamps. These functions cover everything from getting the current time to calculating date differences and formatting output.

The Three Date Storage Formats

FORMAT          EXAMPLE                   NOTES
────────────────────────────────────────────────────────────
ISO 8601 text   '2024-07-24 14:30:00'     Most readable, recommended
Julian day      2460516.10417             Fractional days since 4714 BC
Unix timestamp  1721829000                Seconds since 1970-01-01 UTC

All three work with every SQLite date function.
Most developers use ISO 8601 text for readability.

date() — Return a Date String

SELECT date('now');              -- today's date: '2024-07-24'
SELECT date('2024-07-24');       -- same date returned
SELECT date('now', '+7 days');   -- one week from today
SELECT date('now', '-1 month');  -- one month ago
SELECT date('now', 'start of month');  -- first day of this month
SELECT date('now', 'start of year');   -- Jan 1 of this year

time() — Return a Time String

SELECT time('now');              -- current UTC time: '14:30:00'
SELECT time('now', 'localtime'); -- local time instead of UTC
SELECT time('14:30:00', '+2 hours'); -- '16:30:00'
SELECT time('now', '-30 minutes');   -- 30 minutes ago

datetime() — Date and Time Together

SELECT datetime('now');
-- '2024-07-24 14:30:00'

SELECT datetime('now', 'localtime');
-- Local time (e.g. '2024-07-24 19:30:00' for IST +5:30)

SELECT datetime('now', '+1 day', '+2 hours');
-- Tomorrow, 2 hours later: '2024-07-25 16:30:00'

SELECT datetime('2024-01-15', 'start of month', '+1 month', '-1 day');
-- Last day of January: '2024-01-31'

strftime() — Custom Format Output

FORMAT CODE   MEANING           EXAMPLE OUTPUT
──────────────────────────────────────────────────
%Y            4-digit year      2024
%m            Month (01–12)     07
%d            Day (01–31)       24
%H            Hour (00–23)      14
%M            Minute (00–59)    30
%S            Second (00–59)    00
%w            Day of week       3 (0=Sun,6=Sat)
%j            Day of year       206
%W            Week of year      29

SELECT strftime('%d/%m/%Y', 'now');    -- '24/07/2024'
SELECT strftime('%B %d, %Y', 'now');   -- not supported (use %m)
SELECT strftime('%Y-%m', 'now');       -- '2024-07'  (year-month key)
SELECT strftime('%s', 'now');          -- Unix timestamp as text

julianday() — For Date Arithmetic

Julian day numbers represent dates as a continuous floating-point number. Subtracting two Julian day values gives the exact number of days between them.

-- Days between two dates
SELECT julianday('2024-12-31') - julianday('2024-07-24') AS days_remaining;
days_remaining: 160.0

-- Age in years (approximate)
SELECT CAST(
  (julianday('now') - julianday('1995-03-10')) / 365.25
AS INTEGER) AS age_years;
age_years: 29

-- Hours between two datetimes
SELECT ROUND(
  (julianday('2024-07-24 18:00:00') - julianday('2024-07-24 09:00:00')) * 24,
  1
) AS hours_diff;
hours_diff: 9.0

unixepoch() — Unix Timestamps

-- Current Unix timestamp
SELECT unixepoch('now');          -- e.g. 1721829000

-- Convert Unix timestamp back to readable date
SELECT datetime(1721829000, 'unixepoch');
-- '2024-07-24 14:30:00'

-- Convert with local timezone
SELECT datetime(1721829000, 'unixepoch', 'localtime');

Practical Examples

Orders placed in the last 30 days

SELECT id, customer, order_date
FROM orders
WHERE order_date >= date('now', '-30 days');

Group sales by month

SELECT strftime('%Y-%m', order_date) AS month,
       COUNT(*)    AS orders,
       SUM(amount) AS revenue
FROM orders
GROUP BY strftime('%Y-%m', order_date)
ORDER BY month;

month   │ orders │ revenue
────────┼────────┼────────
2024-05 │ 12     │ 4800
2024-06 │ 18     │ 7200
2024-07 │ 9      │ 3600

Employees with birthdays this month

SELECT name, birthdate
FROM employees
WHERE strftime('%m', birthdate) = strftime('%m', 'now');

Add a record with the current timestamp

INSERT INTO events (description, created_at)
VALUES ('User logged in', datetime('now'));

Find overdue tasks

SELECT title, due_date
FROM tasks
WHERE due_date < date('now')
  AND status != 'done';

Modifier Reference

MODIFIER              EFFECT
──────────────────────────────────────────────────────────
'+N days'             Add N days
'-N months'           Subtract N months
'+N years'            Add N years
'+N hours'            Add N hours
'start of day'        Midnight of the given day
'start of month'      First day of the month at 00:00:00
'start of year'       January 1 at 00:00:00
'weekday N'           Next occurrence of weekday N (0=Sun)
'unixepoch'           Treat input as Unix timestamp
'localtime'           Convert UTC to local time
'utc'                 Convert local time to UTC

Key Takeaways

• Store dates as ISO 8601 text ('YYYY-MM-DD') for maximum compatibility with SQLite date functions
• date('now'), time('now'), and datetime('now') return the current UTC date/time
• Add 'localtime' as a modifier to get local time instead of UTC
• julianday() subtraction gives exact day counts between two dates
• strftime('%Y-%m', col) is the standard way to group records by month