Using INTERVAL To Perform Date Math Without DATE_ADD() In MySQL 5.7.32

By Ben Nadel

Published 2021-10-09 in SQL — Comments (3)

This post is more of a note-to-self than anything else; but, historically, every single example that I've ever seen of adding time to a date/time stamp in MySQL has used the DATE_ADD() function. But, I was just looking through the MyQueue project by Kota Saito when I saw that they incremented a date/time value using simple maths and the INTERVAL keyword. I immediately had to try this out for myself in MySQL 5.7.32.

Here's the test that I ran - it uses UTC_TIMESTAMP() to create the "control case" for the current time. Then, I attempted to add a few hours to this base value using both the DATE_ADD() approach that I'm familiar with and the maths approach that I saw Kota use:


SELECT
	-- Our control case, so that we know what time it is NOW.
	UTC_TIMESTAMP() AS control,

	-- The DATE_ADD() function is how I would have performed date maths in the past,
	-- when needing to add a duration to a given date.
	DATE_ADD( UTC_TIMESTAMP(), INTERVAL 1 HOUR ) AS test1,

	-- This attempts to perform the same maths, but without DATE_ADD().
	( UTC_TIMESTAMP() + INTERVAL 1 HOUR ) AS test2
;

And, when we run this SQL code, we get the following record back:

Hot dog! Both test cases came back with the same value! If INTERVAL works with vanilla math, I'd much rather use that than DATE_ADD() for reduced syntactic noise.

Using `EXTRACT()` With `INTERVAL` In MySQL

After realizing that I didn't understand the full power of INTERVAL in MySQL, I looked up the documentation for Temporal Intervals in MySQL and I saw that there was an EXTRACT() function. This function uses the INTERVAL concept to read portions of a date/time stamp. So, for example, if you wanted to read the Year out of date, you can do:

EXTRACT( YEAR FROM date_time )

Since I don't believe I ever saw this function before, I wanted to give it a quick spin - the following SQL read all of the documented INTERVAL values from the current date:

SELECT
	EXTRACT( MICROSECOND FROM UTC_TIMESTAMP() ),
	EXTRACT( SECOND FROM UTC_TIMESTAMP() ),
	EXTRACT( MINUTE FROM UTC_TIMESTAMP() ),
	EXTRACT( HOUR FROM UTC_TIMESTAMP() ),
	EXTRACT( DAY FROM UTC_TIMESTAMP() ),
	EXTRACT( WEEK FROM UTC_TIMESTAMP() ),
	EXTRACT( MONTH FROM UTC_TIMESTAMP() ),
	EXTRACT( QUARTER FROM UTC_TIMESTAMP() ),
	EXTRACT( YEAR FROM UTC_TIMESTAMP() ),
	EXTRACT( SECOND_MICROSECOND FROM UTC_TIMESTAMP() ),
	EXTRACT( MINUTE_MICROSECOND FROM UTC_TIMESTAMP() ),
	EXTRACT( MINUTE_SECOND FROM UTC_TIMESTAMP() ),
	EXTRACT( HOUR_MICROSECOND FROM UTC_TIMESTAMP() ),
	EXTRACT( HOUR_SECOND FROM UTC_TIMESTAMP() ),
	EXTRACT( HOUR_MINUTE FROM UTC_TIMESTAMP() ),
	EXTRACT( DAY_MICROSECOND FROM UTC_TIMESTAMP() ),
	EXTRACT( DAY_SECOND FROM UTC_TIMESTAMP() ),
	EXTRACT( DAY_MINUTE FROM UTC_TIMESTAMP() ),
	EXTRACT( DAY_HOUR FROM UTC_TIMESTAMP() ),
	EXTRACT( YEAR_MONTH FROM UTC_TIMESTAMP() )
;

And, running this SQL gives us the following record:

Using INTERVAL with EXTRACT() to pick portions of a date in MySQL.

Very cool. The YEAR_MONTH interval value seems like it would be particularly helpful when "bucketing" aggregations that need to be updated on an ongoing basis.

Want to use code from this post? Check out the license.

Short link: https://bennadel.com/4129

Reader Comments

Chris G Oct 9, 2021 at 5:39 PM

236 Comments

I appreciate that you expand my SQL knowledge in unexpected ways. I had never heard of INTERVAL or EXTRACT before. Very cool! And yeah, using YEAR_MONTH as a monthly aggregate is a nice shortcut for sure! Thanks 🙌