Skip to main content
Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.

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

By Ben Nadel on
Tags: SQL

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:

Dates augmented with INTERVAL in MySQL.

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.



Reader 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 🙌

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Blog
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.