Collections:
TIMESTAMPDIFF() - Difference in Timestamp Component
How to calculate the difference between two timestamps in a given component unit using the TIMESTAMPDIFF() function?
✍: FYIcenter.com
TIMESTAMPDIFF(unit, to_time, from_time) is a MySQL built-in function that calculates the difference from one timestamp to another in a given component unit. For example:
SELECT TIMESTAMPDIFF(DAY, '2028-12-31 23:59:59', '2028-12-30 01:01:01.000002'); -- +-------------------------------------------------------------------------+ -- | TIMESTAMPDIFF(DAY, '2028-12-31 23:59:59', '2028-12-30 01:01:01.000002') | -- +-------------------------------------------------------------------------+ -- | -1 | -- +-------------------------------------------------------------------------+ SELECT TIMESTAMPDIFF(HOUR, '2028-12-31 23:59:59', '2028-12-30 01:01:01.000002'); -- +--------------------------------------------------------------------------+ -- | TIMESTAMPDIFF(HOUR, '2028-12-31 23:59:59', '2028-12-30 01:01:01.000002') | -- +--------------------------------------------------------------------------+ -- | -46 | -- +--------------------------------------------------------------------------+ SELECT TIMESTAMPDIFF(SECOND, '2028-12-31 23:59:59', '2028-12-30 01:01:01.000002'); -- +----------------------------------------------------------------------------+ -- | TIMESTAMPDIFF(SECOND, '2028-12-31 23:59:59', '2028-12-30 01:01:01.000002') | -- +----------------------------------------------------------------------------+ -- | -169137 | -- +----------------------------------------------------------------------------+ SELECT TIMESTAMPDIFF(MICROSECOND, '2028-12-31 23:59:59', '2028-12-30 01:01:01.000002'); -- +---------------------------------------------------------------------------------+ -- | TIMESTAMPDIFF(MICROSECOND, '2028-12-31 23:59:59', '2028-12-30 01:01:01.000002') | -- +---------------------------------------------------------------------------------+ -- | -169137999998 | -- +---------------------------------------------------------------------------------+ SELECT TIMEDIFF('2028-12-31 23:59:59', '2029-01-01 01:01:01.000002'); -- +---------------------------------------------------------------+ -- | TIMEDIFF('2028-12-31 23:59:59', '2029-01-01 01:01:01.000002') | -- +---------------------------------------------------------------+ -- | -01:01:02.000002 | -- +---------------------------------------------------------------+ SELECT DATEDIFF('2028-12-31 23:59:59', '2029-01-01 01:01:01.000002'); -- +---------------------------------------------------------------+ -- | DATEDIFF('2028-12-31 23:59:59', '2029-01-01 01:01:01.000002') | -- +---------------------------------------------------------------+ -- | -1 | -- +---------------------------------------------------------------+
Reference information of the TIMESTAMPDIFF() function:
TIMESTAMPDIFF(unit, to_time, from_time): int Subtracts to_time from from_time and returns the difference as an integer for the given unit, which should be one of the following values: MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR. Arguments, return value and availability: unit: Required. The timestamp component unit for the result. to_time: Required. The timestamp to be subtracted. from_time: Required. The timestamp to be subtracted from. int: Return value. The difference as an integer for the given unit. Available since MySQL 4.
Related MySQL functions:
⇒ TIME_FORMAT() - Formatting Time
⇐ TIMESTAMPADD() - Incrementing Timestamp
2023-11-17, 253🔥, 0💬
Popular Posts:
What is test testing area for? The testing area is provided to allow visitors to post testing commen...
How to run Queries with SQL Server Management Studio Express in SQL Server? 1. Launch and connect SQ...
How To Format Time Zone in +/-hh:mm Format in SQL Server Transact-SQL? From the previous tutorial, y...
How to set database to be READ_ONLY in SQL Server? Databases in SQL Server have two update options: ...
How To Assign Debug Privileges to a User in Oracle? In order to run SQL Developer in debug mode, the...