NOW, CURDATE, CURTIME in MySQL - Mini Project: Build & Apply

Using NOW, CURDATE, and CURTIME in MySQL

📖 Scenario: You are managing a small online store database. You want to keep track of when orders are placed by storing the full date and time, just the date, and just the time separately for analysis.

🎯 Goal: Build a simple MySQL table to store order timestamps using the NOW(), CURDATE(), and CURTIME() functions.

📋 What You'll Learn

Create a table called orders with columns order_id, order_datetime, order_date, and order_time.

Insert one row into orders using NOW() for order_datetime, CURDATE() for order_date, and CURTIME() for order_time.

Select all columns from the orders table to see the stored timestamps.

💡 Why This Matters

🌍 Real World

Tracking order times helps businesses analyze sales patterns and improve customer service.

💼 Career

Database developers and analysts often use date and time functions to manage and report on time-sensitive data.

1

Create the orders table

Write a SQL statement to create a table called orders with these columns: order_id as an integer primary key, order_datetime as DATETIME, order_date as DATE, and order_time as TIME.

MySQL

-- Create the orders table with the specified columns
-- Your code here

Hint

Use CREATE TABLE orders and define each column with its type. Remember to set order_id as the primary key.

2

Insert a row using NOW(), CURDATE(), and CURTIME()

Write a SQL INSERT statement to add one row into orders with order_id 1, order_datetime set to NOW(), order_date set to CURDATE(), and order_time set to CURTIME().

MySQL

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  order_datetime DATETIME,
  order_date DATE,
  order_time TIME
);

-- Insert one row with order_id 1 and use NOW(), CURDATE(), CURTIME()
-- Your code here

Hint

Use INSERT INTO orders (order_id, order_datetime, order_date, order_time) and set the values using the MySQL functions NOW(), CURDATE(), and CURTIME().

3

Select all data from orders

Write a SQL SELECT statement to get all columns and rows from the orders table.

MySQL

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  order_datetime DATETIME,
  order_date DATE,
  order_time TIME
);

INSERT INTO orders (order_id, order_datetime, order_date, order_time) VALUES (1, NOW(), CURDATE(), CURTIME());

-- Select all columns from orders
-- Your code here

Hint

Use SELECT * FROM orders; to see all the data you inserted.

4

Add a new order with NOW(), CURDATE(), and CURTIME()

Write a SQL INSERT statement to add a second row into orders with order_id 2, using NOW() for order_datetime, CURDATE() for order_date, and CURTIME() for order_time.

MySQL

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  order_datetime DATETIME,
  order_date DATE,
  order_time TIME
);

INSERT INTO orders (order_id, order_datetime, order_date, order_time) VALUES (1, NOW(), CURDATE(), CURTIME());

SELECT * FROM orders;

-- Insert a second order with order_id 2 using NOW(), CURDATE(), CURTIME()
-- Your code here

Hint

Use the same pattern as before but change order_id to 2.

Practice

1. What does the MySQL function NOW() return?

easy

A. Only the current date

B. The current date and time

C. Only the current time

D. The current year

Solution

1. Step 1: Understand the function purpose

   NOW() is designed to return both date and time together.

2. Step 2: Compare with other functions

   CURDATE() returns only date, CURTIME() returns only time, so NOW() returns both.

3. Final Answer:

   The current date and time -> Option B

4. Quick Check:

   NOW() = current date + time [OK]

Hint: NOW() gives date and time together [OK]

Common Mistakes:

• Confusing NOW() with CURDATE()
• Thinking NOW() returns only time
• Assuming NOW() returns only year

2. Which of the following is the correct syntax to get only the current date in MySQL?

easy

A. SELECT CURDATE();

B. SELECT NOW();

C. SELECT CURRENT_TIME();

D. SELECT CURTIME();

Solution

1. Step 1: Identify function for current date

   CURDATE() returns only the current date in MySQL.

2. Step 2: Check other options

   NOW() returns date and time, CURRENT_TIME() and CURTIME() return only time.

3. Final Answer:

   SELECT CURDATE(); -> Option A

4. Quick Check:

   CURDATE() = current date only [OK]

Hint: Use CURDATE() for date only [OK]

Common Mistakes:

• Using NOW() when only date is needed
• Confusing CURTIME() with CURDATE()
• Using CURRENT_TIME() for date

3. What will be the output of this query if run at exactly 2024-06-15 14:30:45?

SELECT NOW(), CURDATE(), CURTIME();

medium

A. '2024-06-15 00:00:00', '14:30:45', '2024-06-15'

B. '2024-06-15', '14:30:45', '2024-06-15 14:30:45'

C. '14:30:45', '2024-06-15 14:30:45', '2024-06-15'

D. '2024-06-15 14:30:45', '2024-06-15', '14:30:45'

Solution

1. Step 1: Understand each function output

   NOW() returns full date and time, CURDATE() returns date only, CURTIME() returns time only.

2. Step 2: Match given time values

   At 2024-06-15 14:30:45, NOW() = '2024-06-15 14:30:45', CURDATE() = '2024-06-15', CURTIME() = '14:30:45'.

3. Final Answer:

   '2024-06-15 14:30:45', '2024-06-15', '14:30:45' -> Option D

4. Quick Check:

   NOW() = full datetime, CURDATE() = date, CURTIME() = time [OK]

Hint: NOW() full, CURDATE() date, CURTIME() time [OK]

Common Mistakes:

• Mixing order of outputs
• Assuming CURTIME() returns date
• Confusing NOW() with CURDATE()

4. Identify the error in this query:

SELECT NOW, CURDATE(), CURTIME();

medium

A. NOW is missing parentheses

B. CURDATE() should be CURTIME()

C. CURTIME() is misspelled

D. No error, query is correct

Solution

1. Step 1: Check function syntax

   MySQL functions require parentheses even if no arguments are passed. NOW without () is invalid.

2. Step 2: Verify other functions

   CURDATE() and CURTIME() have correct syntax with parentheses.

3. Final Answer:

   NOW is missing parentheses -> Option A

4. Quick Check:

   Functions need () even if empty [OK]

Hint: Always use () with MySQL functions [OK]

Common Mistakes:

• Omitting parentheses for functions
• Assuming functions can be called without ()
• Thinking CURDATE() is incorrect

5. You want to insert the current date and time into a table's created_at column. Which query correctly uses MySQL functions to do this?

hard

A. INSERT INTO logs (created_at) VALUES (CURDATE());

B. INSERT INTO logs (created_at) VALUES (CURTIME());

C. INSERT INTO logs (created_at) VALUES (NOW());

D. INSERT INTO logs (created_at) VALUES ('CURDATE()');

Solution

1. Step 1: Understand the column data type

   created_at usually stores full date and time, so we need a function that returns both.

2. Step 2: Choose correct function

   NOW() returns current date and time, CURDATE() returns only date, CURTIME() only time, and quoting function names inserts strings, not values.

3. Final Answer:

   INSERT INTO logs (created_at) VALUES (NOW()); -> Option C

4. Quick Check:

   Use NOW() for full datetime insert [OK]

Hint: Use NOW() to insert full date and time [OK]

Common Mistakes:

• Using CURDATE() when time is needed
• Using CURTIME() when date is needed
• Inserting function name as string