SQLite - Date & Time

Hello there, aspiring programmers! Today, we're going to dive into the fascinating world of dates and times in SQLite. As your friendly neighborhood computer teacher, I'll guide you through this journey with plenty of examples and explanations. So, buckle up and let's get started!

Time Strings

In SQLite, dates and times are typically stored as text strings. This might seem strange at first, but it's actually quite flexible and easy to work with. Let's look at some common formats:

ISO8601 Format

The most common format is ISO8601, which looks like this:

YYYY-MM-DD HH:MM:SS

For example:

'2023-06-15 14:30:00'

This represents June 15, 2023, at 2:30 PM.

Other Accepted Formats

SQLite is quite forgiving when it comes to date and time formats. Here are some other accepted formats:

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS

Let's try an example:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_name TEXT,
    event_date TEXT
);

INSERT INTO events (event_name, event_date) VALUES
('Birthday Party', '2023-07-01'),
('Movie Night', '2023-07-15 20:00'),
('Morning Jog', '06:30');

SELECT * FROM events;

This will create a table with different date and time formats. Pretty cool, right?

Modifiers

Now, let's talk about modifiers. These are special keywords that allow us to manipulate dates and times. They're like magic wands for our time strings!

Common Modifiers

Here's a table of common modifiers:

Modifier	Description
+N days	Adds N days to the date
-N days	Subtracts N days from the date
start of month	Changes the date to the first day of the month
start of year	Changes the date to the first day of the year
weekday N	Changes the date to the next date where the day of the week is N (0=Sunday, 1=Monday, etc.)

Let's see these in action:

SELECT 
    date('now') AS today,
    date('now', '+1 day') AS tomorrow,
    date('now', '-1 month') AS last_month,
    date('now', 'start of month') AS month_start,
    date('now', 'weekday 1') AS next_monday;

This query will show you today's date, tomorrow's date, the date a month ago, the start of the current month, and the date of the next Monday. Pretty handy, right?

Formatters

Formatters are like the stylists of the date and time world. They help us present our dates and times in different ways.

strftime() Function

The main function we use for formatting is strftime(). It stands for "string format time". Here's the basic syntax:

strftime(format, time-string, modifiers...)

Let's look at some common format specifiers:

Specifier	Description
%Y	Year (4 digits)
%m	Month (01-12)
%d	Day of the month (01-31)
%H	Hour (00-23)
%M	Minute (00-59)
%S	Second (00-59)
%w	Day of week (0-6 with Sunday=0)

Now, let's put it all together:

SELECT 
    strftime('%Y-%m-%d', 'now') AS date_only,
    strftime('%H:%M', 'now') AS time_only,
    strftime('%Y-%m-%d %H:%M:%S', 'now') AS date_time,
    strftime('%w', 'now') AS day_of_week,
    strftime('%Y-%m-%d', 'now', '+1 month') AS next_month;

This query will show you the current date, current time, current date and time, the day of the week (0-6), and the date a month from now.

A Fun Example

Let's end with a fun example. Imagine you're planning a party, and you want to know what day of the week it will be in 100 days:

SELECT 
    strftime('%Y-%m-%d', 'now', '+100 days') AS party_date,
    CASE cast(strftime('%w', 'now', '+100 days') AS INTEGER)
        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 party_day;

This query will tell you the date 100 days from now and what day of the week it will be. Perfect for party planning!

And there you have it, folks! We've journeyed through the land of SQLite dates and times. Remember, practice makes perfect, so don't be afraid to experiment with these functions. Before you know it, you'll be a date and time wizard! Happy coding!

Credits: Image by storyset