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

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:

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.