w3resource

Comprehensive Guide to SQLite Datetime Functions


Working with SQLite Datetime: Managing Dates and Times

SQLite supports various date and time operations using built-in functions. The datetime function is one of the most widely used tools for handling date and time data in SQLite. You can format, compare, and calculate differences between date and time values stored in tables.

SQLite stores date and time as strings in one of these formats:

    1. YYYY-MM-DD (date)

    2. YYYY-MM-DD HH:MM:SS (datetime)

    3. Julian day numbers

    4. Unix timestamps (seconds since 1970-01-01 00:00:00 UTC)


Syntax of SQLite Datetime

DATETIME(timestring, modifier1, modifier2, ...)
  • timestring: The date and time value in text or integer format.
  • modifier: Optional arguments for adjusting the date/time (e.g., +1 day, -1 hour).

Examples of SQLite Datetime

1. Getting Current Datetime

Code:

-- Get the current date and time
SELECT DATETIME('now');

Explanation:

  • The DATETIME('now') function retrieves the current UTC date and time.

2. Adding/Subtracting Time

Code:

-- Add 7 days to the current date and time
SELECT DATETIME('now', '+7 days');

-- Subtract 3 hours from the current date and time
SELECT DATETIME('now', '-3 hours');

Explanation:

  • Modifiers like +7 days and -3 hours allow date/time arithmetic.

3. Converting Unix Timestamps to Datetime

Code:

-- Convert a Unix timestamp to a human-readable datetime format
SELECT DATETIME(1672531200, 'unixepoch');

Explanation:

  • The unixepoch modifier interprets the value as a Unix timestamp and converts it to a standard datetime format.

4. Formatting Specific Datetime

Code:

-- Create a specific datetime
SELECT DATETIME('2024-12-09 15:30:00');

Explanation:

  • The DATETIME function converts the provided string into a datetime object for further operations.

5. Difference Between Dates

Code:

-- Calculate the difference in days between two dates
SELECT JULIANDAY('2024-12-31') - JULIANDAY('2024-01-01') AS days_difference;

Explanation:

  • The JULIANDAY function converts dates into Julian Day numbers, enabling date arithmetic.

Practical Example with Tables

Table Setup

Code:

-- Create a table to store event data
CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_name TEXT,
    event_date TEXT
);

-- Insert sample data
INSERT INTO events (event_name, event_date) VALUES 
('New Year Party', '2024-01-01 00:00:00'),
('Conference', '2024-03-15 09:00:00'),
('Wedding', '2024-07-20 16:00:00');

Querying Dates

Code:

-- Get all events happening after a specific date
SELECT * 
FROM events 
WHERE event_date > DATETIME('2024-01-01 12:00:00');

Explanation:

  • The query retrieves events occurring after noon on January 1, 2024.

Upcoming Events

Code:

-- Get events scheduled in the next 30 days
SELECT * 
FROM events 
WHERE event_date BETWEEN DATETIME('now') AND DATETIME('now', '+30 days');

Explanation:

  • This query filters events scheduled within the next 30 days from the current datetime.

Advantages of SQLite Datetime Functions

    1. Flexibility: Handles multiple date and time formats.

    2. Simplicity: Built-in functions make it easy to work with dates without external libraries.

    3. Portability: Compatible across platforms due to SQLite's lightweight nature.

    4. Powerful Arithmetic: Supports adding, subtracting, and comparing dates.


Additional Tips

    1. Store datetime values in ISO 8601 format (YYYY-MM-DD HH:MM:SS) for better compatibility.

    2. Use the STRFTIME function for custom formatting. Example: STRFTIME('%d-%m-%Y', 'now').

    3. Avoid storing dates as plain text unless strictly necessary; use standard formats for easier querying.

Practical Guides to SQLite Snippets and Examples.



Follow us on Facebook and Twitter for latest update.