Lecture 48: Working with Dates and Times

This lecture covers common SQL date and time functions. Dates and times are a special type of data that often require specific functions for manipulation and formatting. While SQLite stores dates and times as TEXT, REAL, or INTEGER values, it provides a set of built-in functions to work with them effectively.

Current Date and Time Functions

These functions return the current date and/or time.

• `DATE(‘now’)`: Returns the current date in ‘YYYY-MM-DD’ format.
• `TIME(‘now’)`: Returns the current time in ‘HH:MM:SS’ format.
• `DATETIME(‘now’)`: Returns the current date and time in ‘YYYY-MM-DD HH:MM:SS’ format.

SELECT DATE('now');
SELECT TIME('now');
SELECT DATETIME('now');

Date and Time Modifiers

You can add or subtract time from a date using modifiers. These are very powerful.

SELECT DATE('now', '+1 day');        -- Tomorrow's date
SELECT DATE('now', '-1 month');       -- Date one month ago
SELECT DATETIME('now', '+1 year', '+2 hours'); -- Date one year and two hours from now

Common modifiers include: `NNN days`, `NNN hours`, `NNN minutes`, `NNN seconds`, `NNN months`, `NNN years`, `start of day`, `start of month`, `start of year`, `weekday N`.

Extracting Parts of a Date

You can extract specific parts of a date or time string.

• `STRFTIME(format, timestring, modifier, …)`: This is the most versatile function. It formats a date/time string according to a specified format.

Common format codes:

• `%Y`: Year (e.g., 2025)
• `%m`: Month (01-12)
• `%d`: Day of month (01-31)
• `%H`: Hour (00-23)
• `%M`: Minute (00-59)
• `%S`: Second (00-59)
• `%w`: Day of week (0=Sunday, 6=Saturday)

SELECT STRFTIME('%Y', '2025-07-31');       -- Result: '2025'
SELECT STRFTIME('%m', '2025-07-31');       -- Result: '07'
SELECT STRFTIME('%H:%M', '2025-07-31 14:30:00'); -- Result: '14:30'
SELECT STRFTIME('%w', '2025-07-31');       -- Result: '4' (Thursday)

Calculating Differences

You can calculate the difference between two dates by converting them to a common format (like Julian days or Unix timestamps) and subtracting, or by using modifiers.

-- Number of days between two dates
SELECT JULIANDAY('2025-08-10') - JULIANDAY('2025-07-31'); -- Result: 10.0

Why are these useful?

Date and time functions are critical for:

• Reporting: Grouping data by month, year, or day of the week.
• Age Calculation: Determining age from a birthdate.
• Scheduling: Finding events within a date range.

In the Next Lecture…

We will explore more advanced SQL features, including common table expressions (CTEs) and window functions, which allow for even more complex data analysis.

Lecture 47: Working with: Numeric Functions