CURRENT_DATE and CURRENT_TIMESTAMP in SQL - Mini Project: Build & Apply

Using CURRENT_DATE and CURRENT_TIMESTAMP in SQL

📖 Scenario: You are managing a simple employee attendance database. You want to record when employees check in each day.

🎯 Goal: Create a table to store employee check-ins with the current date and timestamp automatically recorded.

📋 What You'll Learn

Create a table named employee_checkins with columns employee_id (integer), checkin_date (date), and checkin_time (timestamp).

Insert a new check-in record for employee with ID 101 using CURRENT_DATE for the date and CURRENT_TIMESTAMP for the timestamp.

Write a query to select all records from employee_checkins.

💡 Why This Matters

🌍 Real World

Tracking employee attendance and timestamps is common in HR and payroll systems.

💼 Career

Understanding how to use CURRENT_DATE and CURRENT_TIMESTAMP is essential for database developers and administrators managing time-sensitive data.

1

Create the employee_checkins table

Write a SQL statement to create a table called employee_checkins with three columns: employee_id as INTEGER, checkin_date as DATE, and checkin_time as TIMESTAMP.

SQL

-- Create the employee_checkins table with employee_id, checkin_date, and checkin_time columns
-- Your code here

Need a hint?

Use CREATE TABLE with the specified column names and types.

2

Insert a check-in record using CURRENT_DATE and CURRENT_TIMESTAMP

Write a SQL INSERT statement to add a record into employee_checkins with employee_id 101, checkin_date set to CURRENT_DATE, and checkin_time set to CURRENT_TIMESTAMP.

SQL

CREATE TABLE employee_checkins (
    employee_id INTEGER,
    checkin_date DATE,
    checkin_time TIMESTAMP
);

-- Insert a check-in record for employee 101 using CURRENT_DATE and CURRENT_TIMESTAMP
-- Your code here

Need a hint?

Use INSERT INTO with the exact values specified.

3

Query all check-in records

Write a SQL SELECT statement to retrieve all columns from the employee_checkins table.

SQL

CREATE TABLE employee_checkins (
    employee_id INTEGER,
    checkin_date DATE,
    checkin_time TIMESTAMP
);

INSERT INTO employee_checkins (employee_id, checkin_date, checkin_time)
VALUES (101, CURRENT_DATE, CURRENT_TIMESTAMP);

-- Select all records from employee_checkins
-- Your code here

Need a hint?

Use SELECT * FROM employee_checkins; to get all records.

4

Add a comment explaining the use of CURRENT_DATE and CURRENT_TIMESTAMP

Add a SQL comment above the INSERT statement explaining that CURRENT_DATE stores the current date and CURRENT_TIMESTAMP stores the current date and time.

SQL

CREATE TABLE employee_checkins (
    employee_id INTEGER,
    checkin_date DATE,
    checkin_time TIMESTAMP
);

-- Your comment here
INSERT INTO employee_checkins (employee_id, checkin_date, checkin_time)
VALUES (101, CURRENT_DATE, CURRENT_TIMESTAMP);

SELECT * FROM employee_checkins;

Need a hint?

Use -- to add a comment explaining the two functions.