Calculating time difference between 2 dates in minutes

Selecting records based on time difference

SELECT *
FROM your_table
WHERE TIMESTAMPDIFF(MINUTE, your_start_time_column, your_end_time_column) > 20;


Let's say you have a date endurance challenge, this SQL query will fetch the records where the time difference exceeds 20 minutes.

Just a note though, you'd want to be on MySQL 5.6 or above to comfortably use TIMESTAMPDIFF.

Precisely calculating time with respect to timezone

SELECT ROUND(TIME_TO_SEC(TIMEDIFF(your_end_time_column, your_start_time_column))/60) AS Minutes
FROM your_table;


The above line of destiny gives you minute difference at the second-level precision by rounding. Yes, MySQL has got your back!

But what about different timezones?

SELECT TIMESTAMPDIFF(
    MINUTE,
    CONVERT_TZ(your_start_time_column, '+00:00', your_timezone),
    CONVERT_TZ(NOW(), '+00:00', your_timezone)
) AS TimeDifference
FROM your_table;


Scientific way to handle it! It's like telling MySQL, "Hey, consider the timezone before you decide to calculate the time difference."

Creating timeframe comparison with current timestamp

SELECT *
FROM your_table
WHERE TIMESTAMPDIFF(MINUTE, your_timestamp_column, CURRENT_TIMESTAMP()) > 20;


If you want records that are 20 minutes old, well, ask and you shall receive! But remember, CURRENT_TIMESTAMP() is your secret time-travelling weapon.

Quite magnificent date manipulation

We humans add 20 minutes to the time, and so can MySQL:

SELECT DATE_ADD('2023-01-01 08:00', INTERVAL 20 MINUTE);


Voila! Now you've got '2023-01-01 08:20'.

Working with Unix timestamps

The UNIX_TIMESTAMP() can help to leap over the hurdle of Unix timestamp:

SELECT TIMESTAMPDIFF(MINUTE, FROM_UNIXTIME(your_unix_timestamp_column), NOW())
FROM your_table;


A superpower that converts Unix timestamps to human-readable dates for comparison with current time.

Dynamic time differences

If you need to find records 20 minutes apart:

SELECT *, TIMESTAMPDIFF(MINUTE, start_column, end_column) AS diff_minutes
FROM your_table
HAVING diff_minutes > 20;


Simply use a HAVING clause for evaluating the value directly from the SELECT statement.