When working with MySQL databases, you may need to calculate how many hours have passed since a record was created. This can be useful for applications that need to track order age, user activity, support tickets, task duration, notifications, or other time-based events.
If your table contains a created_at column, MySQL provides built-in date and time functions that make it easy to calculate the elapsed time between the creation date and the current time.
Calculate Hours Since a Row Was Created
A straightforward way to calculate the number of complete hours since a row was created is to use TIMESTAMPDIFF():
SELECT TIMESTAMPDIFF(HOUR, created_at, NOW()) AS hours_passedFROM table_name;The query compares the value in created_at with the current date and time returned by NOW(). TIMESTAMPDIFF() allows you to specify the unit of the result, such as seconds, minutes, hours, days, months, or years.
Why Use TIMESTAMPDIFF() Instead of DATEDIFF()?
You may also see the following MySQL query used to calculate hours:
SELECT DATEDIFF(NOW(), created_at) * 24 AS hours_passedFROM table_name;This works as a simple day-based calculation, but it is important to understand how DATEDIFF() works. MySQL's DATEDIFF() returns the difference in days and uses only the date portions of the values, ignoring the time portion.
For example, if a record was created at 11:00 PM yesterday and the current time is 1:00 AM today, only two hours have actually passed. A calculation based on DATEDIFF() * 24 could report 24 hours.
For applications where the exact elapsed hours matter, TIMESTAMPDIFF(HOUR, created_at, NOW()) is generally the clearer solution.
Example Table
Suppose you have a table named orders with the following columns:
CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, customer_name VARCHAR(100), created_at DATETIME);You can calculate the number of hours since each order was created with:
SELECT id, customer_name, created_at, TIMESTAMPDIFF(HOUR, created_at, NOW()) AS hours_passedFROM orders;The result could look conceptually like this:
| ID | Customer | Created At | Hours Passed |
|---|---|---|---|
| 1 | John | 2026-08-28 10:00:00 | 27 |
| 2 | Sarah | 2026-08-29 08:30:00 | 4 |
Calculate Minutes or Seconds Since Creation
The advantage of TIMESTAMPDIFF() is that you can change the unit without changing the overall query structure.
Calculate Minutes
SELECT TIMESTAMPDIFF(MINUTE, created_at, NOW()) AS minutes_passedFROM table_name;Calculate Seconds
SELECT TIMESTAMPDIFF(SECOND, created_at, NOW()) AS seconds_passedFROM table_name;Calculate Days
SELECT TIMESTAMPDIFF(DAY, created_at, NOW()) AS days_passedFROM table_name;MySQL documents TIMESTAMPDIFF() as returning the difference between two date or datetime expressions using the specified unit.
Calculate Hours for a Specific Row
If you only want to calculate the elapsed hours for one record, add a WHERE condition:
SELECT TIMESTAMPDIFF(HOUR, created_at, NOW()) AS hours_passedFROM ordersWHERE id = 10;This is useful when displaying the age of a particular order, ticket, task, or user activity record.
Find Rows Older Than 24 Hours
You can also use the same concept to find records that have been in the database for more than 24 hours:
SELECT *FROM ordersWHERE created_at < NOW() - INTERVAL 24 HOUR;This approach is often preferable when your goal is filtering records rather than simply displaying the elapsed time.
Find Records Created Within the Last 24 Hours
To retrieve records created during the previous 24 hours, use:
SELECT *FROM ordersWHERE created_at >= NOW() - INTERVAL 24 HOUR;This can be useful for dashboards, activity feeds, reports, and automated processing jobs.
Return Hours With Decimal Precision
TIMESTAMPDIFF(HOUR, ...) returns complete hours. If you need a decimal value, such as 5.75 hours, calculate the difference in seconds and divide by 3600:
SELECT TIMESTAMPDIFF(SECOND, created_at, NOW()) / 3600 AS hours_passedFROM table_name;This provides more precise elapsed-time information than an integer-only hour calculation.
Format the Result as Hours and Minutes
If you want to display elapsed time in a more readable format, you can use TIMEDIFF():
SELECT TIMEDIFF(NOW(), created_at) AS time_passedFROM table_name;For example, the result may look like 27:35:12, representing 27 hours, 35 minutes, and 12 seconds. MySQL also provides TIMEDIFF() specifically for subtracting two time or datetime expressions.
Important: created_at Should Store Date and Time
For accurate elapsed-time calculations, your created_at column should normally contain both the date and time, such as a MySQL DATETIME or TIMESTAMP value.
created_at DATETIMEA DATE column stores only the calendar date, while DATETIME stores both the date and time. MySQL documents these temporal data types separately.
Handling NULL created_at Values
If created_at is NULL, the date-difference calculation will also return NULL. MySQL documents this behavior for TIMESTAMPDIFF() and DATEDIFF().
If your application needs a fallback value, you can handle it with COALESCE():
SELECT TIMESTAMPDIFF( HOUR, COALESCE(created_at, NOW()), NOW() ) AS hours_passedFROM table_name;Common Use Cases
Calculating hours since a database row was created can be useful in many types of applications, including:
- Tracking how long an order has been pending
- Calculating the age of support tickets
- Monitoring user activity
- Finding abandoned shopping carts
- Identifying overdue tasks
- Creating time-based notifications
- Generating operational reports
- Building SLA and response-time tracking
- Processing records after a specific time period
Conclusion
MySQL makes it simple to calculate the time that has passed since a row was created. While DATEDIFF() can be useful for day-based calculations, TIMESTAMPDIFF() is a better fit when you need to calculate elapsed hours from a DATETIME or TIMESTAMP value.
SELECT TIMESTAMPDIFF(HOUR, created_at, NOW()) AS hours_passedFROM table_name;For more precise results, you can calculate minutes or seconds and convert them into hours. Choosing the right MySQL date and time function helps ensure your application reports elapsed time accurately.
Frequently Asked Questions
How do I calculate hours between two dates in MySQL?
Use TIMESTAMPDIFF() with HOUR as the unit:
SELECT TIMESTAMPDIFF(HOUR, start_date, end_date) AS hours;How do I calculate hours since a row was created in MySQL?
If your table has a created_at column, use:
SELECT TIMESTAMPDIFF(HOUR, created_at, NOW()) AS hours_passedFROM table_name;What is the difference between DATEDIFF() and TIMESTAMPDIFF()?
DATEDIFF() returns the difference in days and considers only the date portions of its arguments. TIMESTAMPDIFF() lets you specify the unit, including hours, minutes, and seconds, making it more suitable for elapsed-time calculations.
Can I calculate minutes instead of hours in MySQL?
Yes. Replace HOUR with MINUTE:
SELECT TIMESTAMPDIFF(MINUTE, created_at, NOW()) AS minutes_passedFROM table_name;Can I calculate the exact number of hours with decimals?
Yes. Calculate the difference in seconds and divide the result by 3600:
SELECT TIMESTAMPDIFF(SECOND, created_at, NOW()) / 3600 AS hours_passedFROM table_name;What happens if created_at is NULL?
The result of the date difference will be NULL. You can use COALESCE() if your application requires a fallback value.
Can I find rows that are older than 24 hours?
Yes. You can compare the creation timestamp with the current time minus a 24-hour interval:
SELECT *FROM table_nameWHERE created_at < NOW() - INTERVAL 24 HOUR;Which MySQL function should I use for elapsed time?
For an integer difference in a specific unit, TIMESTAMPDIFF() is usually the most direct choice. For displaying a time interval, TIMEDIFF() can also be useful.
Need Help With Your MySQL or Web Application?
Working with database logic is only one part of building a reliable application. If you need help with MySQL development, custom web applications, API integrations, application modernization, or ongoing development support, the ScriptBaker team can help.
Contact to ScriptBaker about your project and let’s discuss how we can help you build, improve, or maintain your application.