-1

I'm trying to analyze an MYSQL table.

I have dates times in columns, that signify the start and the end time of an item.

{"stmt":"SELECT start_at, end_at FROM trafficdata;","header":
["start_at","end_at"],

"rows":

[["1022290663000","1022809063000"],["1172792113000","1173483313000"],["1351803408000","1353531408000"],["1290517173000","1293022773000"]

Looking to output this into datetime, in an hour format, with SQLite

LeCoda
  • 538
  • 7
  • 36
  • 79
  • Please see this post: https://stackoverflow.com/questions/56007124/how-do-i-convert-system-currenttimemillis-to-time-format-hhmmss – Dirk De Winnaar Jun 09 '20 at 06:05

2 Answers2

2

Those values may be an integer number counting the milliseconds since the epoch reference of first moment of 1970 in UTC, 1970-01-01T00:00Z.

Here is some Java code parsing those numbers as Instant objects.

List< Long > inputs = List.of( 1_022_290_663_000L , 1022809063000L , 1172792113000L , 1173483313000L , 1351803408000L , 1353531408000L , 1290517173000L , 1293022773000L ) ;
for( Long input : inputs ) 
{
    Instant instant = Instant.ofEpochMilli( input ) ;
    System.out.println( input + " → " + instant ) ;
}

See this code run live at IdeOne.com.

1022290663000 → 2002-05-25T01:37:43Z

1022809063000 → 2002-05-31T01:37:43Z

1172792113000 → 2007-03-01T23:35:13Z

1173483313000 → 2007-03-09T23:35:13Z

1351803408000 → 2012-11-01T20:56:48Z

1353531408000 → 2012-11-21T20:56:48Z

1290517173000 → 2010-11-23T12:59:33Z

1293022773000 → 2010-12-22T12:59:33Z

To calculate the elapsed time between a pair, subtract the earlier number from the later number, divide by 1,000 for a count of seconds, divide by 60 for a count of minutes, and divide by 60 again for a count of hours.

I do not use SQLite, but I imagine you can do this using arithmetic operators found in SQLite.

Perhaps something like:

SELECT ( ( end_at - start_at ) / 1000 / 60 / 60 ) 
FROM whatever ;
Community
  • 1
  • 1
Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • Great. How would i use this in a SQLite query to make these columns have this appropriate format? – LeCoda Jun 09 '20 at 06:09
  • 1
    @MichaelHolborn You did not specify an "appropriate format". I thought you were asking for elapsed time between each pair? – Basil Bourque Jun 09 '20 at 06:10
  • Apologies - I had meant to find the solution using SQLite (In this task I haev to use SQL, otherwise i'd agree with you and use Java or Python : / ) – LeCoda Jun 09 '20 at 06:13
  • @MichaelHolborn See my edits at bottom, with my guess as to SQL code in SQLite. – Basil Bourque Jun 09 '20 at 06:14
  • yes that is correct. Thank you for that! The returned value is still an integer, like {"stmt":"SELECT ( ( end_at - start_at ) / 1000 / 60 / 60 ) \nFROM metadata ;","header":["( ( end_at - start_at ) / 1000 / 60 / 60 )"],"rows":[["144"],["192"],["480"],["696"],["720"],["336"],["336"],["624"],["240"],["0"],["0"],["0"],["408"],["696"],["768"],["720"],["0"],["624"],["720"],["720"],["552"],["0"],["744"],["384"],["0"],["792"],["336"],["360"],["816"],["360"],["72"],["624"],["1128"],["984"],["600"],["0"],["480"],["1152"],["696"],["408"],["0"],["384"],["768"],["1176"],["0"],["552"],["240"], – LeCoda Jun 09 '20 at 06:15
  • 2
    @MichaelHolborn the concept is the same no matter which language you use: subtract the two times and divide to convert milliseconds to hours. – Code-Apprentice Jun 09 '20 at 06:15
  • If this is in hours, then I'm all good! just need to know the time format really – LeCoda Jun 09 '20 at 06:15
  • @BasilBourque 100% - Thank you so much, was very stuck on te problem – LeCoda Jun 09 '20 at 06:16
0

I think that value is current Time Millis. You can convert to Java Util Date. like java.util.Date date= new Date (long value); as a parameter the long value needs to be passed.

Tarapada
  • 11
  • 3