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, 2259🔥, 0💬
Popular Posts:
How Many Groups of Data Types in MySQL? MySQL support 3 groups of data types as listed below: String...
How to convert a JSON (JavaScript Object Notation) quoted string into a regular character string usi...
What are binary literals supported in SQL Server Transact-SQL? Binary literals in Transact-SQL are s...
What Happens to the Current Transaction If a DDL Statement Is Executed in Oracle? If a DDL statement...
What Is the Difference Between GETDATE() and GETUTCDATE() in SQL Server Transact-SQL? The difference...