How to convert big int to date

How do I convert big int to date

Sample output:

name               due
release          | 1300150800000000 

Description:

| name        | text       | NO   开发者_C百科| PRI | NULL    |       |
| due         | bigint(20) | YES  |     | NULL    |       |

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()

select CAST( from_unixtime(end_time/1000) as DATE) from myTable ;

Output: 2015-03-01