SQL Date, Time, DateTime, TimeStamp datatypes

Read this article to understand the SQL Date and date related data types like DATE, TIME, DATETIME, TIMESTAMP, YEAR along with the examples. We have shared few examples and we have shared basic as well as advance level common interview questions also.

In this post, I will explore the fundamentals of handling SQL DATE, TIME, DATETIME, TIMESTAMP, YEAR in SQL. We will learn how to effectively use DATE and it’s related data types, manipulate and extract information from them, and understand the nuances of date and time calculations in SQL queries. 

First, let’s talk about the theory of SQL DATE/TIMESTAMP and how we should use it. Below are the few data types which we can use to store the date or time related values.

1. DATE
2. TIME
3. DATETIME
4. TIMESTAMP
5. YEAR

All above data types are supported by almost all SQL engines. Let’s see the use and syntax of each data types one by one.

DATE

The DATE data type is used to store date values in the format ‘YYYY-MM-DD’. It represents a date without any time component.

Here are some examples of how you can use the DATE data type:

Create a table with a DATE column

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

Insert a date into the DATE column

INSERT INTO events (event_name, event_date)
VALUES ('Birthday Party', '2023-07-17');

Retrieve data based on date

-- Get all events on a specific date
SELECT * FROM events WHERE event_date = '2023-07-17';
-- Get events after a specific date
SELECT * FROM events WHERE event_date > '2023-07-17';
-- Get events between two dates
SELECT * FROM events WHERE event_date BETWEEN '2023-07-01' AND '2023-07-31';

These examples demonstrate some common operations involving the DATE data type in MySQL.

TIME

The TIME data type is used to store time values in the format ‘HH:MM:SS’. It represents a time of day without any date component.

Here are some examples of how you can use the TIME data type:

Create a table with a TIME column

CREATE TABLE appointments (
appointment_id INT PRIMARY KEY AUTO_INCREMENT,
patient_name VARCHAR(50),
appointment_time TIME
);

Insert a date into the TIME column

INSERT INTO appointments (patient_name, appointment_time)
VALUES ('John Doe', '14:30:00');

Retrieve data based on time

-- Get all appointments at a specific time
SELECT * FROM appointments WHERE appointment_time = '14:30:00';
-- Get appointments after a specific time
SELECT * FROM appointments WHERE appointment_time > '12:00:00';
-- Get appointments between two times
SELECT * FROM appointments WHERE appointment_time BETWEEN '09:00:00' AND '17:00:00';

These examples demonstrate some common operations involving the DATE data type in MySQL.

DATETIME

The DATETIME data type is used to store date and time values in the format ‘YYYY-MM-DD HH:MM:SS’. It represents a specific point in time. DATETIME data type is a combination of DATE and TIME data type.

Here are some examples of how you can use the DATETIME data type:

Create a table with a DATETIME column

CREATE TABLE logs (
log_id INT PRIMARY KEY AUTO_INCREMENT,
log_message VARCHAR(255),
log_timestamp DATETIME
);

Insert a date into the DATETIME column

INSERT INTO logs (log_message, log_timestamp)
VALUES ('Error occurred', '2023-07-17 14:30:00');

Retrieve data based on DATETIME

-- Get all logs at a specific datetime
SELECT * FROM logs WHERE log_timestamp = '2023-07-17 14:30:00';
-- Get logs after a specific datetime
SELECT * FROM logs WHERE log_timestamp > '2023-07-17 12:00:00';
-- Get logs between two datetime values
SELECT * FROM logs WHERE log_timestamp BETWEEN '2023-07-17 00:00:00' AND '2023-07-17 23:59:59';

These examples demonstrate some common operations involving the DATETIME data type in MySQL.

TIMESTAMP

The TIMESTAMP data type is used to store date and time values in the format ‘YYYY-MM-DD HH:MM:SS’. It represents a specific point in time. TIMESTAMP data type is a combination of DATE and TIME data type. TIMESTAMP has a narrower range, from ‘1970-01-01 00:00:01’ to ‘2038-01-19 03:14:07’.

Here are some examples of how you can use the TIMESTAMP data type:

Create a table with a TIMESTAMP column

CREATE TABLE user_activity (
user_id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50),
last_login_timestamp TIMESTAMP
);

Insert a date into the TIMESTAMP column

INSERT INTO user_activity (username, last_login_timestamp)
VALUES ('john_doe', '2023-07-17 14:30:00');

Retrieve data based on TIMESTAMP

-- Get all users with a specific last login timestamp
SELECT * FROM user_activity WHERE last_login_timestamp = '2023-07-17 14:30:00';
-- Get users who logged in after a specific timestamp
SELECT * FROM user_activity WHERE last_login_timestamp > '2023-07-17 12:00:00';
-- Get users who logged in within a specific time range
SELECT * FROM user_activity WHERE last_login_timestamp BETWEEN '2023-07-17 00:00:00' AND '2023-07-17 23:59:59';

These examples demonstrate some common operations involving the TIMESTAMP data type in MySQL.

YEAR

The YEAR data type is used to store a year value in a 2-digit or 4-digit format (‘YY’ or ‘YYYY’). It is often used to represent years in a compact form.

Here are some examples of how you can use the YEAR data type:

Create a table with a YEAR column

CREATE TABLE books (
book_id INT PRIMARY KEY AUTO_INCREMENT,
book_title VARCHAR(100),
publication_year YEAR
);

Insert a year into the YEAR column

INSERT INTO books (book_title, publication_year)
VALUES ('The Great Gatsby', 1925);

Retrieve data based on YEAR

-- Get all books published in a specific year
SELECT * FROM books WHERE publication_year = 2023;
-- Get books published after a specific year
SELECT * FROM books WHERE publication_year > 2000;
-- Get books published in a range of years
SELECT * FROM books WHERE publication_year BETWEEN 2010 AND 2020;

These examples demonstrate some common operations involving the YEAR data type in MySQL.

Linking

We have seen all the required date related data types like DATE, TIME, DATETIME, TIMESTAMP and YEAR. We have seen how to create a table, how to insert the date values and fetch the values. There are a lots of functions we can use for manipulating the date data types. You can refer all the interview questions and quiz. Please refer all the links for understanding the DATE data types in depth.

Interview Questions

Basic DATE/TIME SQL Interview questions

1. What are the common date data types in MySQL, and how do they differ from each other?
2. Explain the DATE data type in MySQL and provide an example of how to insert and retrieve data using this data type.
3. What is the difference between the DATETIME and TIMESTAMP data types in MySQL? When would you use one over the other?
4. How do you perform date calculations in MySQL? Provide examples of adding and subtracting days from a date.
5. Describe the TIME data type in MySQL and demonstrate its usage with an example.
6. What is the range of years supported by the YEAR data type in MySQL, and how does it differ for 2-digit and 4-digit formats?
7. How can you retrieve the current date and time in a MySQL query? Provide the necessary function and an example query.
8. Suppose you have a table with a DATETIME column storing timestamps of user activities. How would you find the users who performed activities in the last 24 hours?
9. Explain the concept of automatic timestamp updating using the TIMESTAMP data type in MySQL. How do you set it up for a specific column?
10. How do you format date and time values in MySQL queries? Provide an example of formatting a DATETIME column as ‘Month Day, Year Hour:Minute AM/PM’.

Intermediate DATE/TIME SQL Interview questions

1. How can you extract the year, month, or day from a DATE or DATETIME column in MySQL? Provide examples for each extraction.
2. Suppose you have a table containing sales data with a DATETIME column representing the sale date. How would you find the total sales for each month in the last quarter?

Advanced DATE/TIME SQL Interview questions

1. You have a table storing event data with a DATETIME column representing the start time of each event. How would you find the top 5 busiest hours of the day, considering all events within the last month?

Happy offline learning… Lets meet in next session.