Understanding SQLite Date Functions with Practical Examples

SQLite Date Functions

SQLite provides a range of date and time functions to work with dates and times. These functions enable you to retrieve the current date and time, perform calculations, and manipulate date values stored in the database. SQLite stores dates and times as TEXT, REAL, or INTEGER types and uses the ISO-8601 format for compatibility:

• TEXT: "YYYY-MM-DD HH:MM:SS.SSS" (e.g., "2022-12-07 15:30:45")
• REAL: Julian day numbers, the number of days since November 22, 4714 B.C.
• INTEGER: Unix Time, the number of seconds since January 1, 1970.

Key SQLite Date Functions

1. DATE(): Returns the date in YYYY-MM-DD format.

2. TIME(): Returns the time in HH:MM:SS format.

3. DATETIME(): Returns date and time in YYYY-MM-DD HH:MM:SS format.

4. JULIANDAY(): Returns the Julian day number.

5. STRFTIME(): Formats date and time as specified by a format string.

6. Modifiers: Add or subtract days, months, years, etc. (+1 day, -1 month).

Syntax:

-- Get the current date
SELECT DATE('now');

-- Get the current time
SELECT TIME('now');

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

-- Format date using STRFTIME
SELECT STRFTIME('%d-%m-%Y', 'now');

-- Calculate future dates
SELECT DATE('now', '+10 days');

Examples:

Example 1: Inserting and Querying Date Data

Python Example

Code:

# Import the SQLite module
import sqlite3

# Connect to SQLite database (or create one if it doesn't exist)
connection = sqlite3.connect("example.db")

# Create a cursor object to execute SQL commands
cursor = connection.cursor()

# Create a table with a date column
cursor.execute("""
    CREATE TABLE IF NOT EXISTS events (
        id INTEGER PRIMARY KEY,     # Auto-incremented primary key
        name TEXT NOT NULL,         # Event name
        event_date TEXT             # Event date in 'YYYY-MM-DD' format
    )
""")

# Insert events into the table
cursor.executemany(
    "INSERT INTO events (name, event_date) VALUES (?, ?)",
    [
        ("Conference", "2024-12-10"),
        ("Workshop", "2024-12-15"),
        ("Webinar", "2024-12-20")
    ]
)

# Commit changes
connection.commit()

# Fetch all events after a specific date
cursor.execute("SELECT * FROM events WHERE event_date > ?", ("2024-12-12",))
results = cursor.fetchall()

# Print the results
for row in results:
    print(row)

# Close the connection
connection.close()

Explanation:

1. Table Creation: A table named events is created with a TEXT column to store dates.

2. Inserting Dates: Dates are inserted in ISO-8601 format (YYYY-MM-DD).

3. Filtering Dates: The WHERE event_date > ? clause filters rows for events occurring after December 12, 2024.

Example 2: Calculating Dates

Python Example

Code:

# Import the SQLite module
import sqlite3

# Connect to SQLite database
connection = sqlite3.connect("example.db")
cursor = connection.cursor()

# Calculate dates using SQLite's date functions
cursor.execute("SELECT DATE('now', '+7 days') AS next_week")
next_week = cursor.fetchone()

# Print the calculated date
print(f"Next week's date: {next_week[0]}")

# Close the connection
connection.close()

Explanation:

1. Date Calculation: The DATE('now', '+7 days') function adds 7 days to the current date.

2. Fetching Result: The result is stored and displayed.

Example 3: Formatting Dates

Python Example

Code:

# Import the SQLite module
import sqlite3

# Connect to SQLite database
connection = sqlite3.connect("example.db")
cursor = connection.cursor()

# Format the current date
cursor.execute("SELECT STRFTIME('%d-%m-%Y', 'now') AS formatted_date")
formatted_date = cursor.fetchone()

# Print the formatted date
print(f"Formatted Date: {formatted_date[0]}")

# Close the connection
connection.close()

Explanation:

1. Custom Formatting: The STRFTIME('%d-%m-%Y', 'now') formats the current date into DD-MM-YYYY format.

2. Result Display: The formatted date is retrieved and printed.

Use Cases of SQLite Date Functions

• Scheduling tasks and tracking deadlines.
• Filtering records based on dates.
• Performing date-based calculations, such as age or duration.
• Formatting date outputs for reports.

Practical Guides to SQLite Snippets and Examples.