CODEWITHSUNDEEP
mysqlsql
azunyan
azunyan

Reputation: 25

Convertint millisecond to date

I have a column in a table that contains time in milliseconds (2147483647), or so I have told. I am trying to convert it into the actual time (human time), but haven't been so lucky. I have tried everything I thought was going to be of help, though none of the things I have found on google, and on here, have been helpful. 

SELECT arrivalTime, FROM_UNIXTIME(uploadTime), "; //UNIX_TIMESTAMP(uploadTime) * 1000 // UNIX_TIMESTAMP() * 1000

The arrivalTime is uploaded in a different time format but I already have that working. I also have a different table using a different time format, which is also working but I am including it here on my post just in case it can be used as a reference or someone might find it useful in their code.

Date_Add('1970-01-01 0:0:0', INTERVAL(uploadTime/1000 - (timeZoneOffset*60)) SECOND) AS uploadTime

Any help or suggestion would be GREATLY appreciated!!!

PS: the current query gives me this 2038-01-18 22:14:07 as the time, which is obviously wrong. And I have also tried this 

FROM_UNIXTIME(uploadTime/1000);

but didn't also do what I wanted

PSS: Okay, after asking around, I found out that this 2147483647 is from Android getTimeInMillis from calendar.API. Hope that helps anyone?

Upvotes: 1

Views: 285

Answers (1)

SSharma
SSharma

Reputation: 953

I would do something like this in SQL Server 

DateAdd(Second, (2147483647/1000), CAST('1970-01-01 00:00:00' AS datetime))  AT TIME ZONE 'Central European Standard Time' AS uploadTime


This gives me the output:
enter image description here

After your update that the milliseconds comes from Android getTimeInMillis():
enter image description here
Using this milliseconds in my function above:

DateAdd(Second, (1566302250040/1000), CAST('1970-01-01 00:00:00' AS datetime))  AT TIME ZONE 'Central European Standard Time'


results into 2019-08-20 11:57:30.000 +02:00 which seems to be right.

Upvotes: 2

Related Questions