1

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?

azunyan
  • 25
  • 8
  • 4
    And what time is `2147483647` supposed to represent? – Gordon Linoff Aug 19 '19 at 12:30
  • 1
    @GordonLinoff the actual date of when the report was uploaded – azunyan Aug 19 '19 at 12:31
  • 2
    @azunyan That number also happens to be [PHP's max 32-bit integer](https://www.php.net/manual/en/reserved.constants.php). Seems odd is all – Machavity Aug 19 '19 at 12:32
  • 3
    Your input looks suspiciously like 2^31-1, the largest 32-bit signed integer. I think you may have run into an overflow problem... – Nick Aug 19 '19 at 12:33
  • Possible duplicate of [php: convert milliseconds to date](https://stackoverflow.com/questions/557959/php-convert-milliseconds-to-date) – Machavity Aug 19 '19 at 12:33
  • 2
    `2147483647` is `0x7FFFFFFF` which is unlikely to be correct, `2038-01-18 22:14:07` is a correct conversion of that value to time (in seconds) – Alan Birtles Aug 19 '19 at 12:33
  • Also possibly a duplicate of https://stackoverflow.com/questions/23994889/converting-epoch-number-to-human-readable-date-in-mysql – Machavity Aug 19 '19 at 12:34
  • Thanks of the inputs, guys! I will ask what the number represent or something. I do agree that the number is a bit strange. The time zone offset on this current table also contains the same number and all the data for the upload time have the same, as well. Which is weird! – azunyan Aug 19 '19 at 12:36

1 Answers1

2

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.

SSharma
  • 951
  • 6
  • 15
  • But the upload time cannot be in the 70's because the arrival time is this year. I was told that this 2147483647 is from Android getTimeInMillis from calendar.API. So far, I haven't found anything that converts it to the time that I need. But, thank you! – azunyan Aug 20 '19 at 11:28
  • Ok thanks for more input. In that case, it seems that your milliseconds are wrong. I will update my answer with android screenshots shortly. – SSharma Aug 20 '19 at 11:59
  • Is it possible that your upload time is not the exact time in Milliseconds but rather a difference (like `UploadTime = UPLOADEDTimeMilliSeconds - ArrivalTimeMilliSeconds` ) ? In that case you must use arrivalTime in place of '1970-01-01 00:00:00' in that DateAdd. – SSharma Aug 20 '19 at 12:26
  • Thank you so much for the response! I do apologize for not being able to reply right away! I will ask around here in the office about that, but your screenshot seems the actual number that would be the ideal one! Even my boss said that the current number we have right now seemed weird, especially that each report has the same number for the upload time – azunyan Aug 20 '19 at 14:26