How to get the difference between two timestamps in seconds

by

You could use the TIMEDIFF() and the TIME_TO_SEC() functions as follows:

SELECT TIME_TO_SEC(TIMEDIFF('2010-08-20 12:01:00', '2010-08-20 12:00:00')) diff;
+------+
| diff |
+------+
|   60 |
+------+
1 row in set (0.00 sec)

You could also use the UNIX_TIMESTAMP() function as @Amber suggested in an other answer:

SELECT UNIX_TIMESTAMP('2010-08-20 12:01:00') - 
       UNIX_TIMESTAMP('2010-08-20 12:00:00') diff;
+------+
| diff |
+------+
|   60 |
+------+
1 row in set (0.00 sec)

If you are using the TIMESTAMP data type, I guess that the UNIX_TIMESTAMP() solution would be slightly faster, since TIMESTAMP values are already stored as an integer representing the number of seconds since the epoch (Source). Quoting the docs:

When UNIX_TIMESTAMP() is used on a TIMESTAMP column, the function returns the internal timestamp value directly, with no implicit “string-to-Unix-timestamp” conversion.

Keep in mind that TIMEDIFF() return data type of TIME. TIME values may range from ‘-838:59:59’ to ‘838:59:59’ (roughly 34.96 days)

More Related Contents:

  1. Should I use the datetime or timestamp data type in MySQL?
  2. MySQL convert date string to Unix timestamp
  3. Why there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT clause?
  4. Timestamp with a millisecond precision: How to save them in MySQL
  5. Group mysql query by 15 min intervals
  6. How to select rows that have current day’s timestamp?
  7. MySql difference between two timestamps in days?
  8. MySQL CURRENT_TIMESTAMP on create and on update
  9. Calculating time difference between 2 dates in minutes
  10. Use MySQL to determine whether today is a user’s birthday
  11. When is a timestamp (auto) updated?
  12. MySQL column type “TIMESTAMP” implicitly includes “NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP”
  13. How do I alter a mysql table column defaults?
  14. How can I make SQL case sensitive string comparison on MySQL?
  15. Import data in MySQL from a CSV file using LOAD DATA INFILE
  16. INSERT INTO … SELECT FROM … ON DUPLICATE KEY UPDATE
  17. Connect to mysql on Amazon EC2 from a remote server
  18. Docker-compose check if mysql connection is ready
  19. SQL: selecting rows where column value changed from previous row
  20. What is the location of mysql client “.my.cnf” in XAMPP for Windows?
  21. MySQL error 1241: Operand should contain 1 column(s)
  22. MySQL – Supertype/Subtype design
  23. How can I undo a mysql statement that I just executed?
  24. Continue SQL query even on errors in MySQL workbench
  25. How to generate a UUIDv4 in MySQL?
  26. is php sort better than mysql “order by”?
  27. How can “set timestamp” be a slow query?
  28. MySQL vs. JSON – Why? [closed]
  29. OSX ld: library not found for -lssl
  30. MySQL: Find Missing Dates Between a Date Range

Leave a Comment