What is the unit of the difference of two timestamps in MySQL?-CodePudding

There are two columns (t0 and t1) whose types are timestamp (t0 = 2021-11-18 20:25:09 and t1 = 2021-11-18 20:36:41)

I want to find t1 - t0 (expecting ~11 minutes or ~ 700seconds), but the result is 1132.

I was wondering how - is done between two timestamps and what the unit is.

CodePudding user response：

Use the TIMESTAMPDIFF function for that purpose

For your question:

mysql converts the string into a number and then subtracts, its deterministic but not the result you want

SELECT TIMESTAMPDIFF(MINUTE, '2021-11-18 20:25:09','2021-11-18 20:36:41')

| TIMESTAMPDIFF(MINUTE, '2021-11-18 20:25:09','2021-11-18 20:36:41') |
| -----------------------------------------------------------------: |
|                                                                 11 |

SELECT TIMESTAMPDIFF(SECOND, '2021-11-18 20:25:09','2021-11-18 20:36:41')

| TIMESTAMPDIFF(SECOND, '2021-11-18 20:25:09','2021-11-18 20:36:41') |
| -----------------------------------------------------------------: |
|                                                                692 |

db<>fiddle here

CodePudding user response：

> SELECT TIMEDIFF(t1, t2) FROM t;
 ------------------ 
| TIMEDIFF(t1, t2) |
 ------------------ 
| -00:11:32        |
 ------------------ 
1 row in set (0.000 sec)

Please see the example here

CodePudding user response：

- does nothing useful for timestamps, except that the sign of the result will tell you which is greater (assuming month and day are not 0), which is easier tested with a comparison operator.

In general, if you cast a timestamp to a number, you get a number formed by putting the parts of the timestamp together. For instance:

select timestamp('2021-11-18 20:25:09.012345') 0

gives

20211118202509.012345

- effectively casts both operands to a number. So differences in days become differences in millions, differences in months become differences in hundreds of millions, and differences in years become differences in tens of billions. This doesn't provide any useful measure of the difference between two timestamps.