How to use SQLite's date and time functions?

SQLite's date and time functions provide rich date processing capabilities:

1. Date Time Storage Format

   • SQLite does not have a dedicated date time type
   • Usually stored as TEXT (ISO8601 string), REAL (Julian day number), or INTEGER (Unix timestamp)
   • Recommended to use TEXT type to store ISO8601 format: 'YYYY-MM-DD HH:MM:SS'

2. Main Date Time Functions

   • date(): Returns date
   • time(): Returns time
   • datetime(): Returns date time
   • julianday(): Returns Julian day number
   • strftime(): Formats date time
   • unixepoch(): Unix timestamp conversion

3. Getting Current Date Time

   sql
   -- Current date
   SELECT date('now');
   -- Output: 2026-02-18
   
   -- Current time
   SELECT time('now');
   -- Output: 11:24:00
   
   -- Current date time
   SELECT datetime('now');
   -- Output: 2026-02-18 11:24:00
   
   -- Current Unix timestamp
   SELECT strftime('%s', 'now');
   -- Output: 1739869440

4. Date Time Calculation

   sql
   -- Add/subtract days
   SELECT date('now', '+7 days');
   SELECT date('now', '-1 month');
   
   -- Add/subtract time
   SELECT datetime('now', '+3 hours', '-30 minutes');
   
   -- Calculate date difference
   SELECT julianday('now') - julianday('2026-01-01');

5. Date Time Formatting

   sql
   -- Custom format
   SELECT strftime('%Y-%m-%d %H:%M:%S', 'now');
   
   -- Common format specifiers
   -- %Y: 4-digit year
   -- %m: Month (01-12)
   -- %d: Day (01-31)
   -- %H: Hour (00-23)
   -- %M: Minute (00-59)
   -- %S: Second (00-59)
   -- %w: Day of week (0-6, 0=Sunday)
   -- %j: Day of year (001-366)

6. Date Time Parsing

   sql
   -- Parse string to date time
   SELECT datetime('2026-02-18 11:24:00');
   
   -- Convert from Unix timestamp
   SELECT datetime(1739869440, 'unixepoch');
   
   -- Convert from Julian day number
   SELECT datetime(2460585.974, 'julianday');

7. Date Time Comparison

   sql
   -- Compare dates
   SELECT * FROM orders 
   WHERE order_date > date('now', '-30 days');
   
   -- Date range query
   SELECT * FROM events 
   WHERE event_date BETWEEN date('now') AND date('now', '+7 days');

8. Practical Application Scenarios

   sql
   -- Create orders table
   CREATE TABLE orders (
       id INTEGER PRIMARY KEY,
       order_date TEXT,
       status TEXT
   );
   
   -- Insert current time
   INSERT INTO orders (order_date, status) 
   VALUES (datetime('now'), 'pending');
   
   -- Query today's orders
   SELECT * FROM orders 
   WHERE date(order_date) = date('now');
   
   -- Query this week's orders
   SELECT * FROM orders 
   WHERE order_date >= date('now', 'weekday 0', '-7 days');

9. Time Zone Handling

   sql
   -- UTC time
   SELECT datetime('now', 'utc');
   
   -- Local time
   SELECT datetime('now', 'localtime');
   
   -- Time zone conversion
   SELECT datetime('now', '+8 hours');

10. Performance Optimization

    • Create indexes for date columns
    • Wrapping columns with functions causes index invalidation
    • Consider using generated columns to store formatted dates
    • Avoid using complex date functions in WHERE clauses

SQLite's date time functions are powerful and can meet most date processing needs.