MySQL: Working with date time arithmetic #Part 2.3.1

This entry is part 4 of 5 in the series MySQL DataTypes

So, this is the second last part of the long awaited MySQL datetime series. Previously we have leant how to work with MySQL datetime datatypes and how to automate some insertions using some MySQL datetime commands. Obviously the only thing left to discuss is, the (difficult) datetime arithmetic inside MySQL. As in any language, the datetime arithmetic is not as straight forward as normal arithmetic. The reason is simple, we can’t say 2+2=4 while dealing with dates ;).

Suppose we have a date, ‘2004-02-28’ in YYYY-MM-DD format (which is, a default datetime format for MySQL) and we want to add one more day to the date. So, the result is (supposedly) ‘2004-03-01’. But wait, 2004 is a leap year, so it should be ‘2004-02-29’, right?

These are only a few of the difficulties we face while dealing with datetime arithmetic. Luckily, like any efficient programming language, MySQL (although it is a structured query language) has it’s own set of functions to deal with all these datetime stuffs. Now let us see how we can effectively use those functions to do all our datetime stuffs with queries. But before we start, I would recommend you to go through these two posts related to MySQL:

  1. MySQL date, time and datetime datatype overview
  2. Automatically insert date and time in MySQL tables

Done? Okay, now let us move into further details. In this tutorial we will discuss only the basic and primary arithmetic. In 90% of the cases we will need only these concepts to get our jobs done. Please note that we will show nesting only with default current datetime mysql functions. The nesting is done basically depending on the format of the argument of the main function and format of the return of the nested function.

#1: The basic date time arithmetic in MySQL

To learn the basic arithmetic calculations, we need to learn three (basically two) date/time functions

  • DATE_ADD
  • DATE_SUB
  • ADDTIME
  • SUBTIME

#1.1: DATE_ADD – adds INTERVALS to date:

DATE_ADD, as the name suggests, adds specified interval to the provided date. The syntax and usage is given below.

Syntax:

DATE_ADD('YYYY-MM-DD HH:mm:SS', INTERVAL <expr> <UNIT>)

So, it passes two arguments.

  • The first argument is the YYYY-MM-DD HH:mm:SS format. We can ignore the HH:mm:SS and use only YYYY-MM-DD in case we want only date related calculations.
  • The second argument passes the INTERVALwhich is to be added with the proper value. The expressions are
    • expr: The positive or negative integer which to be added to the mentioned date
    • UNIT: The unit of the expr. This can be day, month, hour, minute, second and so on. A detailed instruction is found on the table below.
 UNIT EXPR Example
 MICROSECOND Microseconds  INTERVAL 100 MICROSECOND
 SECOND Seconds  INTERVAL -5 SECOND
 MINUTE Minutes  INTERVAL 30 MINUTE
 HOUR Hours  INTERVAL 2 HOUR
 DAY Days  INTERVAL 5 DAY
 WEEK Weeks  INTERVAL 2 WEEK
 MONTH Months  INTERVAL 4 MONTH
 YEAR Years  INTERVAL 2 YEAR

Those were only a few. More detailed list of Unit vs Expr can be found here.

Nesting:

The above function can also be nested with proper MySQL datetime functions. For example, CURDATE() or NOW() can be passed into the first argument.

Examples:

Here are some direct usage of DATE_ADD using SELECT query:

SELECT DATE_ADD('2004-02-28', INTERVAL 1 DAY) AS leap_year;
SELECT DATE_ADD(CURDATE(), INTERVAL 1 YEAR) AS day_next_year;
SELECT YEAR(DATE_ADD(CURDATE(), INTERVAL 101 YEAR)) AS next_101_year; --Extracts the year from the date_add arithmetic
SELECT DATE_ADD(NOW(), INTERVAL 30 MINUTE) AS time_next_half_hour;

The output is shown below

MySQL DATE_ADD Examples
MySQL DATE_ADD Examples

#1.2: DATE_SUB – subtracts INTERVAL from date:

Theoretically, it does the same job as DATE_ADD with negative interval. So the following codes are similar

--So the code
SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY);
--is equivalent to
SELECT DATE_ADD(CURDATE(), INTERVAL -1 DAY);

It has the same synopsis (syntax) and expressions as DATE_ADD. The output of the above query is shown below.

DATE_ADD vs DATE_SUB in MySQL
DATE_ADD vs DATE_SUB in MySQL

 #1.3: ADDTIME() and SUBTIME() to work only with time:

As the names suggest, ADDTIME() is used for adding time to a specified time and SUBTIME() is used for subtracting time from a specified time. Here are in depth details.

Syntax:

ADDTIME(<expr1>, <expr2>);
SUBTIME(<expr1>, <expr2>);
  • expr1, expr2 are both in HH:mm:SS format.
  • ADDTIME adds expr2 to expr1 and returns the result.
  • SUBTIME subtracts expr2 from expr1 and returns the result.

Nesting:

This can be nested with time returning functions such as CURTIME().

Examples:

SELECT ADDTIME('12:00:01', '00:30:59');
SELECT SUBTIME('12:59:03', '00:09:04');
SELECT ADDTIME(CURTIME(), '14:00:00');

The output is shown below

MySQL ADDTIME and SUBTIME
MySQL ADDTIME and SUBTIME

 #2: Advance date-time arithmetic functions:

Now that we are familiar with the basics, let us now see, some advanced functions which will become handy in real world projects. Here we are going to discuss the following functions

  • DATEDIFF
  • TIMEDIFF
  • LASTDAY
  • EXTRACT
  • MAKEDATE
  • MAKETIME

#2.1: DATEDIFF – calculates days between two dates:

The difference between DATEDIFF and DATE_SUB is that, DATEDIFF returns the number of days between two dates. It can be either positive or negative.

Syntax:

DATEDIFF(<expr1>, <expr2>)
  • expr1, expr2 can be either YYYY-MM-DD HH:mm:SS or YYYY-MM-DD. Only YYYY-MM-DD will be used for the calculation
  • It returns expr1 – expr2 in number of days.

Nesting:

Due to the format of the arguments it supports, CURDATE() and NOW() can be nested.

Example:

SELECT DATEDIFF('2010-12-31', '2011-01-01');
SELECT DATEDIFF('2011-12-31', '2010-01-01');
SELECT DATEDIFF('2011-12-21', '2011-12-30');
SELECT DATEDIFF(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), CURDATE());

The output is

MySQL DATEDIFF
Working with MySQL DATEDIFF

Food for thought:

Note how the last query can be used to get the number of days in the upcoming month. Now try and think how we can use similar query to get the number of days in upcoming year.

[learn_more caption=”Want to check the answer?”]

SELECT DATEDIFF(DATE_ADD(NOW(), INTERVAL 1 YEAR), NOW());

CURDATE could also be used, instead of NOW[/learn_more]

#2.2: TIMEDIFF – calculates HH:mm:SS b/w two times:

This is pretty straight forward. It takes two arguments in time format, subtracts and returns the result.

Syntax:

TIMEDIFF(<expr1>, <expr2>)
  • expr1, expr2 is of the format HH:mm:SS
  • It returns expr1 – expr2 as time value in HH:mm:SS format.

Nesting:

It can be nested with CURTIME() function. We will see this in the examples.

Examples:

SELECT TIMEDIFF('24:12:01', '12:12:00');
SELECT TIMEDIFF(CURTIME(), '00:30:00');

The output is shown below.

Using MySQL TIMEDIFF
Using MySQL TIMEDIFF

#2.3: LAST_DAY – of specified month of date:

This returns the last day date of the year-month-day passed in the argument.

Syntax:

LAST_DAY(<date>)
  • date is the YYYY-MM-DD value passed.
  • It returns a YYYY-MM-DD which corresponds to the last day of the month of the year.

Nesting:

As obvious, it can be nested with CURDATE() function.

Examples:

SELECT LAST_DAY('2012-02-01');
SELECT LAST_DAY(CURDATE());

The output is

Using MySQL LAST_DAY
Using MySQL LAST_DAY

#2.4: EXTRACT – what you need from date/time stamp:

This allows extracting a specific part of the datetime from the passed argument.

Syntax:

EXTRACT(<unit> FROM <date>)
  • The unit is same as DATE_ADD().
  • The date can be
    • Complete YYYY-MM-DD HH:mm:SS
    • Just date format as YYYY-MM-DD
    • Just time format as HH:mm:SS

It returns just the expression for the specified unit.

Nesting:

Because of the flexibility, it can be nested with all the date time functions as NOW(), CURDATE(), CURTIME() etc.

Examples:

SELECT EXTRACT(MONTH FROM '2011-02-28');
SELECT EXTRACT(DAY FROM LAST_DAY('2012-02-01')); --effective eh?
SELECT EXTRACT(DAY FROM DATE_ADD(CURDATE(), INTERVAL 20 DAY)); --even more complex
SELECT EXTRACT(HOUR FROM CURTIME()); --Works with HH:mm:SS too

The outputs are

Using MySQL EXTRACT
Using MySQL EXTRACT

#2.5: MAKEDATE – from year and day:

It constructs a date when the year and day of the year is given.

Syntax:

MAKEDATE(<year>, <day_of_year>)
  • The year is the year of which the date is to be calculated.
  • The day_of_year is the count of the day from the beginning.
  • It returns a date in YYYY-MM-DD format.

Nesting:

Due to the format of the input arguments, it can not be nested directly with any MySQL current datetime functions.

Examples:

SELECT MAKEDATE(2012, 365);
SELECT MAKEDATE(2010, DATEDIFF(CURDATE(), '2011-04-12'));
SELECT MAKEDATE(2000, DATEDIFF(CURDATE(), '2000-01-01'));
SELECT MAKEDATE(2000, DATEDIFF(DATE_ADD(CURDATE(), INTERVAL 1 DAY), '2000-01-01')) AS today;

The output is

Using MySQL MAKEDATE
Using MySQL MAKEDATE

Food for thought:

Note how the last query gives today’s date. Can you think further to give same date for the next year? As in, today’s date is 2011-12-19. Can you print 2012-12-19 in similar approach?

[learn_more caption=”Want to check the answer?”]

SELECT MAKEDATE(2001, DATEDIFF(DATE_ADD(CURDATE(), INTERVAL 1 DAY), '2000-01-01')) AS next_year_today;

Easy right? We just took the difference from the same year and constructed from one more year (2001).
[/learn_more]

#2.6: MAKETIME – from HH, mm & SS:

This is very simple, yet useful function. It constructs a Time format from the given arguments.

Syntax:

MAKETIME(<hour>, <minute>, <second>)
  • The hour, minute, secondarguments can hold only the legal values
    • 0-59 for minute, second

It returns a nicely formatted HH:mm:SS value from the arguments.

Nesting:

Due to it’s format, it can not be nested with current datetime mysql functions.

Examples:

SELECT MAKETIME(12, 34, 11);
SELECT MAKETIME(12, EXTRACT(MINUTE FROM CURTIME()), 59);

The outputs are

Using MySQL MAKETIME
Using MySQL MAKETIME

So that was all. Hope it will help you understand better the concept of MySQL datetime arithmetic. If you have any doubt, just throw in using the comments form. In the next tutorial, we will see how to use these functions in real world using a MySQL table. You can also check the official MySQL documentation for more available datetime functions. Oh, and here is a downloadable SQL file for your ease!

[download id=”12″ format=”1″]

2 comments

  1. Pingback: DateTime difference calculation in PHP 5.3 OOP or 5.2 procedural style

Comments are closed.