SQL Date Time Functions

Read this article to understand the SQL Date and TIME related functions like DATE, TIME, CURDATE, CURTIME, NOW 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 functions of handling SQL DATE, TIME, DATETIME, TIMESTAMP, YEAR in SQL. We will learn how to effectively use DATE and it’s related functions, 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. Learn all date related data types in detail in this post.

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 different functions per data type and syntax of each functions 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.

Below are the functions which we can use to perform operation on DATE values.

1. CURDATE
2. NOW
3. DATE
4. DATE_ADD
5. DATE_SUB
6. YEAR
7. MONTH
8. DAYOFWEEK
9. DAYOFMONTH
10. DAYNAME
11. MONTHNAME
12. DATEDIFF
13. DATE_FORMAT

CURDATE

Returns the current date in ‘YYYY-MM-DD’ format.

SELECT CURDATE();

NOW

Returns the current date and time in ‘YYYY-MM-DD HH:MM:SS’ format.

SELECT NOW();

DATE

Extracts the date part from a datetime expression.

SET @dt = now();
SELECT date(@dt);

DATE_ADD

Adds a specified interval to a date or datetime value.

SET @dt = now();
SELECT date_add(date(@dt),interval 3 day);

above syntax will add the 3 day interval and return the date in YYYY-MM-DD format.

DATE_SUB

Subtracts a specified interval from a date or datetime value.

SET @dt = now();
SELECT date_sub(date(@dt),interval 3 day);

above syntax will subtracts the 3 day interval and return the date in YYYY-MM-DD format.

YEAR

Returns the year as a four-digit number.

SELECT YEAR(date(now()));

MONTH

Returns the month as an integer (1 to 12).

SELECT MONTH(date(now()));

DAYOFWEEK

Returns the day of the week as an integer (1 = Sunday, 2 = Monday, …, 7 = Saturday).

SELECT DAYOFWEEK(date(now()));

DAYOFMONTH

Returns the day of the month as an integer (1 to 31).

SELECT DAYOFMONTH(date(now()));

DAYNAME

Returns the name of the day of the week (e.g., Sunday, Monday).

SELECT DAYNAME(date(now()));

MONTHNAME

Returns the name of the month (e.g., January, February).

SELECT MONTHNAME(date(now()));

DATEDIFF

Returns the difference in days between two dates.

SELECT datediff(date(now()),date(date_add(now(),interval 3 day)));

DATE_FORMAT

Formats a date or datetime value as per the specified format string.

Syntax

DATE_FORMAT(date_value, format_string)

Here’s a breakdown of the format specifiers you can use in the format_string :

• %Y : Year as a four-digit number (e.g., 2023).
• %y : Year as a two-digit number (e.g., 23).
• %m : Month as a two-digit number (01 to 12).
• %c : Month as a one-digit number (1 to 12).
• %b : Abbreviated month name (e.g., Jan, Feb).
• %M : Full month name (e.g., January, February).
• %d : Day of the month as a two-digit number (01 to 31).
• %e : Day of the month as a one-digit number (1 to 31).
• %a : Abbreviated weekday name (e.g., Sun, Mon).
• %W : Full weekday name (e.g., Sunday, Monday).
• %H : Hour (00 to 23).
• %h : Hour (01 to 12).
• %i : Minutes (00 to 59).
• %s : Seconds (00 to 59).
• %p : AM or PM (works with %h:%i:%s %p ).

Example

SELECT DATE_FORMAT(now(), '%Y-%m-%d %H:%i %p');

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.

Below are the functions which we can use to perform operation on DATE values.

1. CURTIME
2. TIME
3. HOUR
4. MINUTE
5. SECOND

CURTIME

Returns the current time as a TIME value.

SELECT CURTIME();

TIME

Extracts the time part from a DATETIME or TIMESTAMP value.

SET @dt = now();
SELECT time(@dt);

HOUR

Returns the hours from a DATETIME or TIME value (0 to 23).

SET @dt = now();
SELECT hour(@dt);

MINUTE

Returns the minutes from a DATETIME or TIME value (0 to 59).

SET @dt = now();
SELECT minute(@dt);

SECOND

Returns the seconds from a DATETIME or TIME value (0 to 59).

SET @dt = now();
SELECT second(@dt);

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.

Below are the functions which we can use to perform operation on DATETIME values.

1. FROM_UNIXTIME
2. UNIX_TIMESTAMP
3. UTC_TIMESTAMP
4. CONVERT_TZ
5. STR_TO_DATE

FROM_UNIXTIME

Converts a Unix timestamp to a DATETIME value.

SELECT FROM_UNIXTIME(1689791732);

UNIX_TIMESTAMP

Returns the Unix timestamp for a given date or datetime value.

SELECT UNIX_TIMESTAMP(now());

UTC_TIMESTAMP

Returns the current UTC date and time as a DATETIME value.

SELECT UTC_TIMESTAMP();

CONVERT_TZ

Converts a datetime value from one time zone to another.

Syntax

CONVERT_TZ(dt, from_tz, to_tz)

STR_TO_DATE

Converts a string to a DATETIME value using the specified format.

Syntax

STR_TO_DATE(str, format)

Example

SELECT STR_TO_DATE('2023-07-20', '%Y-%m-%d')

Output

2023-07-20

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’.

All functions which are supported by DATETIME are supported by TIMESTAMP.

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.

Below are the functions which we can use for getting YEAR values.

1. EXTRACT

EXTRACT

The EXTRACT() function in MySQL is used to extract a specific part (or component) of a date or datetime value, such as year, month, day, hour, minute, or second. It allows you to retrieve individual components of a date or datetime value, enabling you to perform various date-related operations and analyses.

Syntax

EXTRACT(unit FROM date_value)

• unit : Specifies the date or time part to be extracted. It can be one of the following values: YEAR , MONTH , DAY , HOUR , MINUTE , or SECOND .
• date_value : The date or datetime value from which the specified part is to be extracted.

Example

SELECT
event_name,
event_datetime,
EXTRACT(YEAR FROM event_datetime) AS event_year,
EXTRACT(MONTH FROM event_datetime) AS event_month,
EXTRACT(DAY FROM event_datetime) AS event_day
FROM events;

Linking

We have seen all the required date related functions. Learn all the date related data types here. 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 functions in depth.

Interview Questions

Basic DATE/TIME SQL Interview questions

1. What is the purpose of date functions in MySQL, and why are they important?
2. How would you retrieve the current date and time using a date function?
3. Explain the difference between the DATE() and TIME() functions in MySQL.
4. How do you extract the year, month, and day from a DATETIME column using date functions?
5. Suppose you have a table with a DATE column representing customers’ birthdates. How would you find all customers who were born in a specific month and year?
6. How can you calculate the age of a person based on their birthdate using date functions?
7. Suppose you have a DATETIME column recording user activities. How would you find the most recent activity in the table?
8. What is the purpose of the DATE_FORMAT() function in MySQL? Provide an example of how to use it.
9. How would you add a specific number of days to a date using a date function?
10. Explain the purpose of the UNIX_TIMESTAMP() function and when it is useful.

Intermediate DATE/TIME SQL Interview questions

1. Suppose you have a table containing event data with a DATETIME column representing event start times. How would you find the time slot with the highest number of overlapping events?
2. Describe how you would handle date and time data when dealing with data from different time zones in a distributed system using MySQL.
3. You have a table storing customer subscription data with a DATETIME column representing subscription start times. How would you find the number of active subscriptions for each day in the last month?

Advanced DATE/TIME SQL Interview questions

1. You have a table with a DATETIME column storing timestamps of user activities. How would you find the total number of activities performed by each user for each day of the week?

Happy offline learning… Lets meet in next session.