Convert Bigint data to human readable date-time in Sql

I have created a MySql table and feed data therein with below code

CREATE TABLE IF NOT EXISTS DB (
    INN VARCHAR(200) NOT NULL, 
    Time BIGINT not NULL
);

Now I want to get a table which will report the Maximum and Minimum values of Time when grouped by INN. Below is my code -

SELECT INN, from_unixtime(MIN(Time)), from_unixtime(MAX(Time)) FROM DB GROUP BY INN

I want to get the Time values reported as normal date-time instead of Epoch time. But with above code, I am getting <NA> values.

A typical Time value is like 1546380001264082944

Can someone please help me to get the correct code to achieve the same.

The problem here is to do with the precision of the unix timestamp you are using.

Consider this:

SELECT FROM_UNIXTIME(1546380001), FROM_UNIXTIME(1546380001264082944)

The output is:

2019-01-01T22:00:01Z	(null)

The timestampt value you have, 1546380001264082944, contains a level of precision beyond that accepted by MySQL.

The definition of FROM_UNIXTIME is:

FROM_UNIXTIME(unix_timestamp[,format])

The doc states:

> unix_timestamp is an internal timestamp value representing seconds
> since '1970-01-01 00:00:00' UTC

The precision of your timestamp is significantly greater than seconds since the Unix Epoch.

The docs are available here.

The value 1546380001264082944 is too big to be epoch seconds or even milliseconds. This is easily verified by putting this value on <https://currentmillis.com/>.

You have stored precision upto a nanosecond. So, divide the column value by 1e9 before passing them to from_unixtime.

SELECT INN, from_unixtime(MIN(Time) / 1000000000), from_unixtime(MAX(Time) / 1000000000)
FROM DB
GROUP BY INN