Menu
I have a epoch number say 1389422614485. The datatype for the value storing this value is varchar. I want to convert its value to human readable time. How can we do it? Any example for this conversion?
201
Convert from epoch to human-readable dateString date = new java.text.SimpleDateFormat("MM/dd/yyyy HH:mm:ss").format(new java.util.Date (epoch*1000)); Epoch in seconds, remove '*1000' for milliseconds. myString := DateTimeToStr(UnixToDateTime(Epoch)); Where Epoch is a signed integer. Replace 1526357743 with epoch.
You can take an epoch time divided by 86400 (seconds in a day) floored and add 719163 (the days up to the year 1970) to pass to it. Awesome, this is as manual as it gets.
The Unix epoch (or Unix time or POSIX time or Unix timestamp) is the number of seconds that have elapsed since January 1, 1970 (midnight UTC/GMT), not counting leap seconds (in ISO 8601: 1970-01-01T00:00:00Z).
You want to use the TIMESTAMP data type. It's stored as an epoch value, but MySQL displays the value as 'YYYY-MM-DD HH:MM:SS'. Show activity on this post. MySql DateTime data type store the date in format 'YYYY-MM-DD HH:MM:SS' with range from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
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 | +------------------------------------------+ If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
