SQL - Date & Time: A Comprehensive Guide for Beginners

Hello there, future database wizards! Today, we're diving into the fascinating world of dates and times in SQL. As your friendly neighborhood computer teacher, I'm here to guide you through this journey with plenty of examples and explanations. So, grab your virtual time machine, and let's get started!

SQL - Date & Time

Date & Time Datatypes in SQL

Before we start manipulating dates and times, we need to understand how SQL stores this information. Think of these datatypes as special containers designed specifically for temporal data.

DATE

The DATE datatype stores a date in the format YYYY-MM-DD.

CREATE TABLE events (
    event_id INT PRIMARY KEY,
    event_name VARCHAR(100),
    event_date DATE
);

INSERT INTO events (event_id, event_name, event_date)
VALUES (1, 'Company Picnic', '2023-07-15');

In this example, we're creating an events table and adding a company picnic scheduled for July 15, 2023. The DATE datatype ensures we're storing just the date, without any time information.

TIME

The TIME datatype stores time in the format HH:MI:SS.

CREATE TABLE class_schedule (
    class_id INT PRIMARY KEY,
    class_name VARCHAR(100),
    start_time TIME
);

INSERT INTO class_schedule (class_id, class_name, start_time)
VALUES (1, 'Introduction to SQL', '09:30:00');

Here, we're creating a class schedule and adding an SQL class that starts at 9:30 AM. The TIME datatype allows us to store this time precisely.

DATETIME

The DATETIME datatype combines both date and time, typically in the format YYYY-MM-DD HH:MI:SS.

CREATE TABLE appointments (
    appointment_id INT PRIMARY KEY,
    patient_name VARCHAR(100),
    appointment_datetime DATETIME
);

INSERT INTO appointments (appointment_id, patient_name, appointment_datetime)
VALUES (1, 'John Doe', '2023-06-20 14:30:00');

In this example, we're scheduling a doctor's appointment for John Doe on June 20, 2023, at 2:30 PM. The DATETIME datatype allows us to store both the date and time in a single column.

TIMESTAMP

The TIMESTAMP datatype is similar to DATETIME, but it's often used for tracking when a record was last modified.

CREATE TABLE blog_posts (
    post_id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO blog_posts (post_id, title, content)
VALUES (1, 'My First Blog Post', 'Hello, World!');

In this example, the last_updated column will automatically be set to the current date and time when a record is inserted or updated.

Date & Time Functions in SQL

Now that we know how to store dates and times, let's explore some of the magical functions SQL provides to manipulate this data. I like to think of these functions as time-traveling tools in our SQL toolkit!

Current Date and Time

Let's start with the basics - getting the current date and time.

SELECT CURRENT_DATE() AS today,
       CURRENT_TIME() AS current_time,
       NOW() AS current_datetime;

This query will give you the current date, time, and datetime. It's like asking SQL, "Hey, what time is it?" Handy, right?

Extracting Parts of a Date

Sometimes, we only need specific parts of a date. SQL has functions for that too!

SELECT YEAR('2023-06-20') AS year,
       MONTH('2023-06-20') AS month,
       DAY('2023-06-20') AS day,
       WEEKDAY('2023-06-20') AS weekday;

This query extracts the year, month, day, and weekday from the given date. It's like dissecting a date to get the information we need.

Date Arithmetic

SQL allows us to perform arithmetic operations on dates. This is particularly useful for calculating durations or finding future/past dates.

SELECT DATE_ADD('2023-06-20', INTERVAL 7 DAY) AS week_later,
       DATE_SUB('2023-06-20', INTERVAL 1 MONTH) AS month_ago,
       DATEDIFF('2023-12-31', '2023-06-20') AS days_until_new_year;

In this example, we're adding 7 days to a date, subtracting 1 month from a date, and calculating the number of days between two dates. It's like having a time machine at our fingertips!

Formatting Dates

Sometimes, we need to present dates in a specific format. The DATE_FORMAT function comes to our rescue.

SELECT DATE_FORMAT('2023-06-20', '%W, %M %d, %Y') AS formatted_date,
       DATE_FORMAT('2023-06-20 14:30:00', '%h:%i %p') AS formatted_time;

This query formats the date as "Tuesday, June 20, 2023" and the time as "02:30 PM". It's like dressing up our dates for a fancy occasion!

Here's a table of some commonly used format specifiers:

Specifier	Description	Example
%Y	4-digit year	2023
%y	2-digit year	23
%M	Month name	January
%m	Month (01-12)	01
%d	Day of the month (01-31)	01
%W	Weekday name	Monday
%w	Day of the week (0-6, 0 is Sunday)	1
%H	Hour (00-23)	13
%h	Hour (01-12)	01
%i	Minutes (00-59)	30
%s	Seconds (00-59)	45
%p	AM or PM	PM

Putting It All Together

Let's wrap up with a more complex example that combines several concepts:

CREATE TABLE user_logins (
    login_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    login_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO user_logins (user_id) VALUES (1), (2), (3);

SELECT 
    user_id,
    login_time,
    DATE_FORMAT(login_time, '%W, %M %d, %Y at %h:%i %p') AS formatted_login_time,
    CASE 
        WHEN WEEKDAY(login_time) IN (5, 6) THEN 'Weekend Login'
        ELSE 'Weekday Login'
    END AS login_type,
    TIMESTAMPDIFF(HOUR, login_time, NOW()) AS hours_since_login
FROM user_logins
WHERE login_time > DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK);

This example creates a user_logins table, inserts some data, and then queries it with various date and time manipulations. We're formatting the login time, determining if it was a weekend or weekday login, and calculating how many hours have passed since the login.

And there you have it, folks! We've traveled through time with SQL, learning about date and time datatypes and functions. Remember, practice makes perfect, so don't be afraid to experiment with these concepts. Before you know it, you'll be bending time to your will... in your databases, at least!

Credits: Image by storyset