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

If you take it as microseconds since epoch: `perl -e ' print scalar localtime 1300150800'` you get March 15th, 2011 — Thilo, Mar 23 '11 at 03:42
Check out this question: http://stackoverflow.com/questions/4261684/how-to-decode-some-number-into-timedate — Mark Ransom, Mar 23 '11 at 03:47

Amil Waduwawara · Accepted Answer · 2011-03-23T04:11:39.100

17

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:

edited Mar 23 '11 at 04:11

answered Mar 23 '11 at 03:58

Amil Waduwawara

@efox, It seems you're new to StackOverflow. Please accept if you got the answer. – Amil Waduwawara Mar 28 '11 at 04:20
1

It appears FROM_UNIXTIME() would introduce the [Y2K38 problem](http://stackoverflow.com/a/2012620/673991). Wonder if there's an alternative that doesn't. Otherwise (calculator tapping sounds) BIGINT seems good to year 9999 even counting microseconds. – Bob Stein Jul 30 '13 at 17:19

score 0 · Answer 2 · answered Jul 26 '17 at 13:48

0

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

Output: 2015-03-01

answered Jul 26 '17 at 13:48

Allwin

2 Answers2