Converting epoch number to human readable date in mysql

by

Your epoch value 1389422614485 seems like having the millisecond precision. So you need to use some mysql mathematical functions along with from_unixtime() for generating human readable format.

mysql> select from_unixtime(floor(1389422614485/1000));
+------------------------------------------+
| from_unixtime(floor(1389422614485/1000)) |
+------------------------------------------+
| 2014-01-11 12:13:34                      |
+------------------------------------------+

Update July 2020: As of MySQL 8.0, the floor function is no longer necessary when working with milliseconds:

mysql> select from_unixtime(1594838230234/1000);
+------------------------------------------+
| from_unixtime(1594838230234/1000)        |
+------------------------------------------+
| 2020-07-15 18:37:10.2340                 |
+------------------------------------------+

More Related Contents:

  1. Delete the entire content of all table in a Database [closed]
  2. MyISAM versus InnoDB [closed]
  3. How do I find the MySQL my.cnf location
  4. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  5. MySQL select one column DISTINCT, with corresponding other columns
  6. MySQL Syntax error message “Operand should contain 1 column(s)”
  7. How to use mysql JOIN without ON condition?
  8. Format number to 2 decimal places
  9. How to remove all non-alpha numeric characters from a string in MySQL?
  10. INSERT … ON DUPLICATE KEY (do nothing)
  11. Define a variable within select and use it within the same select
  12. ERROR: Error 1005: Can’t create table (errno: 121)
  13. Merge two tables in one SQL query and make the date values unique
  14. mySQL subquery limit
  15. How to increase MySQL connections(max_connections)?
  16. COUNT(*) from multiple tables in MySQL
  17. libmysqlclient15-dev on macs?
  18. What are practical differences between `REPLACE` and `INSERT … ON DUPLICATE KEY UPDATE` in MySQL?
  19. MySQL Select ID’s which occur on different rows with multiple specific values for a column
  20. How to compare timestamp dates with date-only parameter in MySQL?
  21. How to sort a MYSQL table in a permanent way?
  22. UPDATE with ORDER BY and LIMIT not working in MYSQL
  23. How to Get True Size of MySQL Database?
  24. Query to count the number of tables I have in MySQL
  25. MySQL vs. JSON – Why? [closed]
  26. How to kill MySQL connections
  27. `MODIFY COLUMN` vs `CHANGE COLUMN`
  28. Find method to store an IP in MySQL for better performance
  29. Ruby on Rails – cannot load such file — mysql2/2.2/mysql2 (LoadError)
  30. How to solve MySQL max_user_connections error

Leave a Comment