How to deal with Date and Time in MySQL Server

Tutorialsrack 23/09/2019 MySQL

In this article, we will learn about how to deal with date and time in MySQL. In MySQL, it supports a bunch of date and time utility functions that we can use to handle DATE and TIME efficiently.

MySQL DATE and TIME Function 

Functions used to get the current date and time in MySQL:

NOW() Function

NOW() function is used to get the current date and time, a query is as follows:

SELECT now();  -- date and time

CURDATE() Function

CURDATE() function is used to get the only current Date, a query is as follows:

SELECT curdate(); -- Current date only

DATE() Function

DATE() function is used to get the date part only, a query is as follows:

SELECT date(now()); -- date part only

CURTIME() Function

CURTIME() function is used to get the only current time in 24-hour format, a query is as follows:

SELECT curtime(); -- time in 24-hour format

Find rows between two dates or timestamps

To find rows between two dates, a query will be as follows:

SELECT *
FROM payment
WHERE payment_date BETWEEN '2005-05-01' AND '2005-06-30';

To find rows between two dates and time, a query will be as follows:

-- Can include time by specifying in YYYY-MM-DD hh:mm:ss format:
SELECT *
FROM payment
WHERE payment_date BETWEEN '2005-05-01 12:00:00' AND '2005-06-30 23:30:00';

To find rows created within the last week, a query will be as follows:

SELECT *
FROM payment
WHERE payment_date > date_sub('2005-06-30', interval 1 week);

DATE_ADD() and DATE_SUB() Function

There’s also DATE_ADD() and DATE_SUB() Function. For example, to find payments scheduled between one week ago and 2 days from a given date:

SELECT *
FROM payment
WHERE payment_date BETWEEN date_sub('2005-06-01 12:00:00', interval 1 week) 
AND date_add('2005-06-01 12:00:00', interval 2 day);

Extracting Parts From Timestamps

you can extract a part from timestamp by using the corresponding function:

YEAR() function to get a year from a timestamp, a query is as follows:

SELECT year(now()); -- To get year

MONTH() function to get a month from a timestamp, a query is as follows:

SELECT month(now()); -- To get month

DAY() function to get a day from a timestamp, a query is as follows:

SELECT day(now()); -- To get day

HOUR() function to get an hour from a timestamp, a query is as follows:

SELECT hour(now()); -- To get hour

MINUTE() function to get minutes from a timestamp, a query is as follows:

SELECT minute(now()); -- To get minute

SECOND() function to get seconds from a timestamp, a query is as follows:

SELECT second(now()); -- To get second

DAYOFWEEK() function

DAYOFWEEK() function to get a day of the week from a timestamp, a query is as follows:

-- it returns 1-7 (integer), where 1 is Sunday and 7 is Saturday
SELECT dayofweek(now());

DAYNAME() Function

DAYNAME() function to get the name of the day from a timestamp, a query is as follows:

-- it returns the string day name like Monday, Tuesday, etc
SELECT dayname(now());

UNIX_TIMESTAMP() Function

UNIX_TIMESTAMP() Function to convert a timestamp to a UNIX timestamp (integer seconds):

-- This will assume time to be 12am
SELECT unix_timestamp('2019-09-21');


-- You can specify an exact timestamp to be converted down to the second
SELECT unix_timestamp('2019-09-21 14:53:21');


-- calling unix_timestamp without a parameter will be like calling it for current timestamp
SELECT unix_timestamp(); -- same as SELECT unix_timestamp(now());

To calculate the difference between two timestamps, convert them to UNIX timestamps then perform the subtraction:

-- show seconds between delivery and shipping timestamps
SELECT unix_timestamp(payment_date) - unix_timestamp(last_update)
FROM payment;


-- convert computed difference to hh:mm:ss format:
SELECT sec_to_time(unix_timestamp(payment_date) - unix_timestamp(last_update))
FROM payment;

DATE_FORMAT() Function

Sometimes, we want to show the date in a user-defined format such as the first day, then month and in the last year (day/month/year format). Then we can use DATE_FORMAT() function to format the date. A query is as follows:

-- Print the MySQL date in a user-defined format
SELECT DATE_FORMAT(DATE('2019-09-21'), '%d/%m/%Y') Formatted_date;

DATEDIFF() Function

In MySQL, when we want to count the difference between two dates, we can use the DATEDIFF() function. A query is as follows:

-- Print the difference between two dates
SELECT DATEDIFF('2019-09-01','2019-08-01') as Date_Difference;

TIMEDIFF() Function

In MySQL, when we want to count the difference between two times or DateTime, we can  use the TIMEDIFF() function. A query is as follows:

-- Print the difference between two times
SELECT TIMEDIFF('22:22:22','20:20:20') as TIME_DIfference;

-- Print the difference between two datetimes
SELECT TIMEDIFF('2019/09/23 12:00:00','2019/09/22 12:00:00') as TIME_DIfference;

I hope this article will help you to understand how to deal with date and time in MySQL.

Share your valuable feedback, please post your comment at the bottom of this article. Thank you!

Related Posts