Is there an easy way to retrieve the timestamp of each .bin log record in a csv or excel file ?

I posted this on drone-discuss but without any answer, so I try here again:

In an objective to georeference images, I'd like to export from a .bin log file the CAM & GPS messages with corresponding timestamps (the time that is displayed in the second column of the mission planner "review log" screen). I convert the .bin file to a .log file from the mission planner log utilities. However when importing the produced log file into excel, the timestamp column was removed in the .bin to .log conversion (as if the first two columns of the .bin are removed when creating the .log version of the flight log).

Questions that I have are :

-the timestamp that appears in the second column in mission planner review log corresponds to what time ? GPS time or internal Pixhawk time or else ?

-How can I easily (without programming a utility to decode the .bin log) export ALL columns of the .bin log to a csv type of file ?

-Where are specifications of the .bin data structure & content ?

-Is the disapperance of two columns from .bin to .log conversion a known bug or should a new issue ticket be created (if yes, where to create it, I am lost in all of these forums used for various categories of topics ?)?

Thx

Hugues

Here is how to calculate the GPS and CAM time from timestamp from the source code (DFLog.cs) :

FMT, 130, 45, GPS, BIHBcLLeeEefI, Status, TimeMS, Week, NSats, HDop, Lat, Lng, RelAlt, Alt,Spd,GCrs,VZ,T

GPS, 3, 130040903, 1769, 10, 0.00, -35.3547178, 149.1696673, 885.52, 870.45, 24.56, 321.44, 2.450000, 127615

First the GPS status value has to be 3 or 2.

Then calculate :

UTC Time = 1/6/1980 (6th Jan) + 7 * WEEK + SEC - 16 (don't ask me why about those values, it's in the code)

Where :

WEEK = the GPS week in log

SEC = TimeMS in log / 1000

public static DateTime gpsTimeToTime(int week, double sec)
{
   int leap = 16;

   // not correct for leap seconds                   day   days weeks seconds
   var basetime = new DateTime(1980, 1, 6, 0, 0, 0, DateTimeKind.Utc);
   basetime = basetime.AddDays(week * 7);
   basetime = basetime.AddSeconds((sec - leap));

   return basetime.ToLocalTime();
}

For the other time convertion (IMU...), do the following (need a loop) :

UTC Time = last_GPS_time + TimeMS - offset

Where the last_GPS_time is last GPS time from log,

TimeMS the TimeMS value of current line,

offset is the value of the last parameter (T) of the last GPS message.

Replies

- Krzysztof > Hugues September 4, 2015 at 3:00am
  
  The second column is UTC time, so there is ~17 seconds difference between GPS time (leap seconds).
- Quevauvillers Samuel > Hugues September 3, 2015 at 6:40am
  
  Hi Hugues, this column is simply :
  
  item.time.ToString("yyyy-MM-dd HH:mm:ss.fff");
  
  I checked the source code, I can tell you how export or convert those GPS and other timestamp.
  
  By the way, it would be easy to add a button in the LogViewer and add 4 lines of code to copy the whole table (DataGridView) into the clipboard.
- MR60
  
  Hugues > Quevauvillers Samuel September 3, 2015 at 11:25pm
  
  Best answer awarded! Remains the question of what is the T0 reference to convert from a duration to a date-time-hour format?
- Quevauvillers Samuel > Hugues September 4, 2015 at 3:03am
  
  Here is how to calculate the GPS and CAM time from timestamp from the source code (DFLog.cs) :
  
  FMT, 130, 45, GPS, BIHBcLLeeEefI, Status, TimeMS, Week, NSats, HDop, Lat, Lng, RelAlt, Alt,Spd,GCrs,VZ,T
  
  GPS, 3, 130040903, 1769, 10, 0.00, -35.3547178, 149.1696673, 885.52, 870.45, 24.56, 321.44, 2.450000, 127615
  
  First the GPS status value has to be 3 or 2.
  
  Then calculate :
  
  UTC Time = 1/6/1980 (6th Jan) + 7 * WEEK + SEC - 16 (don't ask me why about those values, it's in the code)
  
  Where :
  
  WEEK = the GPS week in log
  
  SEC = TimeMS in log / 1000
  
  public static DateTime gpsTimeToTime(int week, double sec)
  {
     int leap = 16;
  
     // not correct for leap seconds                   day   days weeks seconds
     var basetime = new DateTime(1980, 1, 6, 0, 0, 0, DateTimeKind.Utc);
     basetime = basetime.AddDays(week * 7);
     basetime = basetime.AddSeconds((sec - leap));
  
     return basetime.ToLocalTime();
  }
  
  For the other time convertion (IMU...), do the following (need a loop) :
  
  UTC Time = last_GPS_time + TimeMS - offset
  
  Where the last_GPS_time is last GPS time from log,
  
  TimeMS the TimeMS value of current line,
  
  offset is the value of the last parameter (T) of the last GPS message.
- Quevauvillers Samuel > Quevauvillers Samuel September 4, 2015 at 5:04am
  
  7 * WEEK [days] + SEC - 17 [sec] (pay attention to units)
  
  Thanks Krzystof
- MR60
  
  Hugues > Quevauvillers Samuel September 4, 2015 at 9:22am
  
  Should I assume that GPS counts seconds since January 6, 1980, or since August 21, 1999 (when they reset the week count to zero again) ?
- Krzysztof > Quevauvillers Samuel September 4, 2015 at 3:08am
  
  16 - it is the leap seconds, but nowdays it should be 17.
Vinnie Tsoi September 1, 2015 at 3:31pm

There is no clock in the Pixhawk. Timestamp from GPS may not always be there as GPS is optional and it can also have GPS glitches so the timestamp is not reliable.
- MR60
  
  Hugues > Vinnie Tsoi September 2, 2015 at 4:59am
  
  A timestamp is displayed as the second column in front of each log record as pointed by the yellow arrow on the picture below. My question is how to export this to a csv or excel file,
- MR60
  
  Forrest Frantz > Hugues September 2, 2015 at 6:48am
  
  doesn't it all ready export it as the file name and sheet name? use TimeStamp = activesheet.name or the worksheet name

of 4

This reply was deleted.

ArduCopter User Group

Replies