Week 02: SQL – Dates and Times

Author

Eric Araujo

In SQLite, date and time operations are handled using a set of built-in functions that work with text values in ISO-8601 format: ‘YYYY-MM-DD HH:MM:SS’. These functions can parse, manipulate, and format date/time values.

Here’s a breakdown of how date and time functions and operators work in SQLite:

🕒 Common Date & Time Functions

1. DATE(timestring, modifier1, modifier2, …)

Returns the date (no time part).

SELECT DATE('now'); -- Current date (UTC)
SELECT DATE('now', 'localtime'); -- Current date (local time)
SELECT DATE('2025-03-30', '+1 day'); -- Adds 1 day
SELECT DATE('2025-03-30', '-1 month'); -- Subtracts 1 month

2. TIME(timestring, modifier1, modifier2, …)

Returns the time (no date part).

SELECT TIME('now'); -- Current time (UTC)
SELECT TIME('now', 'localtime'); -- Current time (local)

3. DATETIME(timestring, modifier1, modifier2, …)

Returns both date and time.

SELECT DATETIME('now'); -- Current date and time (UTC)
SELECT DATETIME('now', '+1 hour'); -- Adds one hour

4. JULIANDAY(timestring, modifier1, …)

Returns the Julian day number, which is a floating point value representing days since noon in Greenwich on November 24, 4714 B.C.

SELECT JULIANDAY('2025-03-30');

5. STRFTIME(format, timestring, modifier1, …)

Returns a formatted string using format codes.

SELECT STRFTIME('%Y-%m-%d', 'now'); -- "2025-03-30"
SELECT STRFTIME('%H:%M:%S', 'now'); -- Time part
SELECT STRFTIME('%Y', 'now');       -- Year only

🧩 Common format codes:

%Y = year (e.g. 2025)
%m = month (01–12)
%d = day (01–31)
%H = hour (00–23)
%M = minute (00–59)
%S = seconds (00–59)

🛠️ Useful Modifiers

You can use one or more modifiers to shift the date/time:

Modifier	Effect
`'localtime'`	Converts to local time
`'utc'`	Converts to UTC
`'+NNN days'`	Adds NNN days
`'-NNN months'`	Subtracts NNN months
`'start of month'`	Sets date to the 1st of the month
`'weekday N'`	Moves to next weekday (0=Sun…6=Sat)
`'start of year'`	Sets date to January 1st

SELECT DATE('now', 'start of month', '+1 month', '-1 day');
-- Last day of current month

🔍 Examples

-- Age from birthday
SELECT (JULIANDAY('now') - JULIANDAY('1990-05-15')) / 365 AS age;

-- Timestamp 7 days from now
SELECT DATETIME('now', '+7 days');

-- Current time, formatted
SELECT STRFTIME('%d-%m-%Y %H:%M', 'now', 'localtime');

🔗 Tip: Date/time values in SQLite are stored as TEXT

SQLite doesn’t have a dedicated DATE or TIME type. Store your datetime values as ISO strings (YYYY-MM-DD HH:MM:SS) for full compatibility with these functions.

📦 Example Database: appointments

🧱 Table: appointments

%%capture
%load_ext sql
%sql sqlite:///dbs/w02/appointments.db
%config SqlMagic.style = '_DEPRECATED_DEFAULT'

%%sql

DROP TABLE IF EXISTS appointments;

CREATE TABLE appointments (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    client_name TEXT NOT NULL,
    scheduled_at TEXT NOT NULL  -- Stored in 'YYYY-MM-DD HH:MM:SS' format
);

-- Sample data
INSERT INTO appointments (title, client_name, scheduled_at) VALUES
('Consultation', 'Alice', '2025-03-30 10:00:00'),
('Follow-up', 'Bob', '2025-03-31 14:30:00'),
('Strategy Meeting', 'Charlie', '2025-04-01 09:00:00'),
('Project Review', 'Diana', '2025-04-01 16:45:00'),
('Feedback Session', 'Eve', '2025-04-02 11:15:00');

 * sqlite:///dbs/w02/appointments.db
Done.
Done.
5 rows affected.

[]

🧪 Example Queries Using Date/Time Functions

✅ 1. Get all appointments today (UTC)

%%sql
SELECT * 
FROM appointments 
WHERE DATE(scheduled_at) = DATE('now');

 * sqlite:///dbs/w02/appointments.db
Done.

id	title	client_name	scheduled_at

✅ 2. Get all appointments in the next 3 days

%%sql
SELECT * 
FROM appointments 
WHERE DATE(scheduled_at) BETWEEN DATE('now') AND DATE('now', '+3 days');

 * sqlite:///dbs/w02/appointments.db
Done.

id	title	client_name	scheduled_at

✅ 3. Show appointments with day of the week

%%sql
SELECT 
    id,
    title,
    client_name,
    scheduled_at,
    STRFTIME('%w', scheduled_at) AS weekday_num,
    CASE STRFTIME('%w', scheduled_at)
        WHEN '0' THEN 'Sunday'
        WHEN '1' THEN 'Monday'
        WHEN '2' THEN 'Tuesday'
        WHEN '3' THEN 'Wednesday'
        WHEN '4' THEN 'Thursday'
        WHEN '5' THEN 'Friday'
        WHEN '6' THEN 'Saturday'
    END AS weekday
FROM appointments;

 * sqlite:///dbs/w02/appointments.db
Done.

id	title	client_name	scheduled_at	weekday_num	weekday
1	Consultation	Alice	2025-03-30 10:00:00	0	Sunday
2	Follow-up	Bob	2025-03-31 14:30:00	1	Monday
3	Strategy Meeting	Charlie	2025-04-01 09:00:00	2	Tuesday
4	Project Review	Diana	2025-04-01 16:45:00	2	Tuesday
5	Feedback Session	Eve	2025-04-02 11:15:00	3	Wednesday

✅ 4. Count how many appointments per day

%%sql
SELECT 
    DATE(scheduled_at) AS date,
    COUNT(*) AS total_appointments
FROM appointments
GROUP BY DATE(scheduled_at);

 * sqlite:///dbs/w02/appointments.db
Done.

date	total_appointments
2025-03-30	1
2025-03-31	1
2025-04-01	2
2025-04-02	1

✅ 5. Check if any appointment is scheduled on a weekend

%%sql
SELECT *
FROM appointments
WHERE STRFTIME('%w', scheduled_at) IN ('0', '6');  -- Sunday or Saturday

 * sqlite:///dbs/w02/appointments.db
Done.

id	title	client_name	scheduled_at
1	Consultation	Alice	2025-03-30 10:00:00

✅ 6. Show how many hours until each appointment (from now)

%%sql
SELECT 
    id,
    title,
    scheduled_at,
    ROUND((JULIANDAY(scheduled_at) - JULIANDAY('now')) * 24, 2) AS hours_until
FROM appointments;

 * sqlite:///dbs/w02/appointments.db
Done.

id	title	scheduled_at	hours_until
1	Consultation	2025-03-30 10:00:00	-104.18
2	Follow-up	2025-03-31 14:30:00	-75.68
3	Strategy Meeting	2025-04-01 09:00:00	-57.18
4	Project Review	2025-04-01 16:45:00	-49.43
5	Feedback Session	2025-04-02 11:15:00	-30.93

🌍 Example: Show today’s local appointments

%%sql
SELECT *
FROM appointments
WHERE DATE(scheduled_at) = DATE('now', 'localtime');

 * sqlite:///dbs/w02/appointments.db
Done.

id	title	client_name	scheduled_at

Explanation:

DATE(‘now’, ‘localtime’) gets today’s date in the local time zone.
This query filters all appointments scheduled for today (local time).