Date and time types in MySQL - Mini Project: Build & Apply

Working with Date and Time Types in MySQL

📖 Scenario: You are managing a small library database. You need to store information about books and the dates when they were added to the library and when they were last borrowed.

🎯 Goal: Create a table to store book information including date and time fields, insert sample data, and query the data using date and time types.

📋 What You'll Learn

Create a table called books with columns id (integer), title (varchar), added_date (DATE), and last_borrowed (DATETIME).

Insert three books with exact titles and dates provided.

Write a query to select all books added after a specific date.

Write a query to select books last borrowed before a specific datetime.

💡 Why This Matters

🌍 Real World

Libraries, event planners, and many businesses use date and time fields to track when events happen or records are created.

💼 Career

Understanding how to store and query date and time data is essential for database administrators and backend developers.

1

Create the books table with date and datetime columns

Write a SQL statement to create a table called books with these columns: id as INT, title as VARCHAR(100), added_date as DATE, and last_borrowed as DATETIME.

MySQL

-- Your code here to create the books table with the specified columns

Hint

Use CREATE TABLE with the exact column names and types.

2

Insert three books with exact dates

Insert these three rows into the books table: (1, 'The Great Gatsby', '2023-01-15', '2023-06-10 14:30:00'), (2, '1984', '2023-03-22', '2023-06-12 09:15:00'), and (3, 'To Kill a Mockingbird', '2023-05-05', '2023-06-11 16:45:00').

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100),
  added_date DATE,
  last_borrowed DATETIME
);
-- Your code here to insert the three books with exact values

Hint

Use INSERT INTO books (id, title, added_date, last_borrowed) VALUES with the exact rows.

3

Query books added after a specific date

Write a SQL query to select all columns from books where added_date is after '2023-02-01'. Use the exact condition added_date > '2023-02-01'.

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100),
  added_date DATE,
  last_borrowed DATETIME
);

INSERT INTO books (id, title, added_date, last_borrowed) VALUES
(1, 'The Great Gatsby', '2023-01-15', '2023-06-10 14:30:00'),
(2, '1984', '2023-03-22', '2023-06-12 09:15:00'),
(3, 'To Kill a Mockingbird', '2023-05-05', '2023-06-11 16:45:00');

-- Your code here to select books added after 2023-02-01

Hint

Use SELECT * FROM books WHERE added_date > '2023-02-01'.

4

Query books last borrowed before a specific datetime

Write a SQL query to select all columns from books where last_borrowed is before '2023-06-12 00:00:00'. Use the exact condition last_borrowed < '2023-06-12 00:00:00'.

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100),
  added_date DATE,
  last_borrowed DATETIME
);

INSERT INTO books (id, title, added_date, last_borrowed) VALUES
(1, 'The Great Gatsby', '2023-01-15', '2023-06-10 14:30:00'),
(2, '1984', '2023-03-22', '2023-06-12 09:15:00'),
(3, 'To Kill a Mockingbird', '2023-05-05', '2023-06-11 16:45:00');

SELECT * FROM books WHERE added_date > '2023-02-01';

-- Your code here to select books last borrowed before 2023-06-12 00:00:00

Hint

Use SELECT * FROM books WHERE last_borrowed < '2023-06-12 00:00:00'.

Practice

1. Which MySQL data type should you use to store only a date without time?

easy

A. DATE

B. DATETIME

C. TIME

D. TIMESTAMP

Solution

1. Step 1: Understand the data types

   DATE stores only the date (year, month, day). DATETIME and TIMESTAMP store date and time. TIME stores only time.

2. Step 2: Match the requirement

   Since we want to store only the date without time, DATE is the correct choice.

3. Final Answer:

   DATE -> Option A

4. Quick Check:

   Date only = DATE [OK]

Hint: Date only? Use DATE type [OK]

Common Mistakes:

• Choosing DATETIME when only date is needed
• Using TIME for date storage
• Confusing TIMESTAMP with DATE

2. Which of the following is the correct syntax to declare a column named start_time that stores only time in MySQL?

easy

A. start_time TIME;

B. start_time DATE;

C. start_time DATETIME;

D. start_time TIMESTAMP;

Solution

1. Step 1: Identify the correct data type for time only

   The TIME type stores only time values (hours, minutes, seconds).

2. Step 2: Check syntax correctness

   Declaring a column as start_time TIME; is valid syntax in MySQL.

3. Final Answer:

   start_time TIME; -> Option A

4. Quick Check:

   Time only column = TIME type [OK]

Hint: Use TIME type for time-only columns [OK]

Common Mistakes:

• Using DATE or DATETIME for time-only columns
• Missing semicolon in declaration
• Confusing TIMESTAMP with TIME

3. Given the table events with a column event_datetime DATETIME, what will the query SELECT DATE(event_datetime) FROM events WHERE event_datetime = '2024-06-15 14:30:00'; return?

medium

A. 2024-06-15 14:30:00

B. 2024-06-15

C. 14:30:00

D. Error: Invalid function

Solution

1. Step 1: Understand the DATE() function

   The DATE() function extracts only the date part (year-month-day) from a datetime value.

2. Step 2: Apply the function to the given datetime

   Applying DATE('2024-06-15 14:30:00') returns 2024-06-15.

3. Final Answer:

   2024-06-15 -> Option B

4. Quick Check:

   DATE() extracts date only [OK]

Hint: DATE() extracts date part from datetime [OK]

Common Mistakes:

• Expecting full datetime as output
• Confusing DATE() with TIME() function
• Thinking DATE() causes error

4. You wrote this query to insert a time value: INSERT INTO schedule (meeting_time) VALUES ('25:00:00'); but it gives an error. What is the problem?

medium

A. Using DATETIME instead of TIME column type

B. Missing quotes around time value

C. Time value exceeds valid hour range (0-23)

D. Incorrect table name

Solution

1. Step 1: Check the time value format

   Valid time values must have hours between 00 and 23. '25:00:00' has 25 hours, which is invalid.

2. Step 2: Understand MySQL time validation

   MySQL rejects time values outside the valid range, causing an error on insert.

3. Final Answer:

   Time value exceeds valid hour range (0-23) -> Option C

4. Quick Check:

   Hours must be 0-23 in TIME values [OK]

Hint: Hours in TIME must be 0-23 [OK]

Common Mistakes:

• Assuming 25 is valid hour
• Forgetting quotes around time
• Confusing column type with error cause

5. You want to store the exact moment a user logs in, including date and time, and also want automatic timezone conversion to UTC. Which MySQL data type should you use?

hard

A. DATETIME

B. DATE

C. TIME

D. TIMESTAMP

Solution

1. Step 1: Understand the data types and timezone behavior

   TIMESTAMP stores date and time and automatically converts values to UTC for storage and back to local time on retrieval. DATETIME stores date and time but does not handle timezone conversion.

2. Step 2: Match requirement for timezone conversion

   Since automatic timezone conversion is needed, TIMESTAMP is the correct choice.

3. Final Answer:

   TIMESTAMP -> Option D

4. Quick Check:

   TIMESTAMP handles timezone conversion [OK]

Hint: Use TIMESTAMP for timezone-aware datetime [OK]

Common Mistakes:

• Choosing DATETIME and expecting timezone conversion
• Using DATE or TIME for full datetime
• Ignoring timezone needs