How to convert big int to date

Question

How do I convert big int to date

Sample output:

name               due
release          | 1300150800000000 

Description:

| name        | text       | NO   | PRI | NULL    |       |
| due         | bigint(20) | YES  |     | NULL    |       |

Answer 1

It seems it contains microseconds since 1970-Jan-01 00:00:00am GMT.

That is after converting your value to seconds, it gives 1300150800, which is equivalent to 2011-Mar-15 01:00:00am GMT.

Therefore to convert it to a datetime you can use MySQL's FROM_UNIXTIME(unix_timestamp, format) after converting it to seconds (by dividing 1000000).

SQL:

SELECT FROM_UNIXTIME(due/1000000, "%Y-%m-%d %H:%i:%s") AS due_date 
FROM   MyTable;

Ref:

• MySQL FROM_UNIXTIME()
• MySQL DATE_FORMAT()