FIT timestamp_16 Heart Rate - Excel

I'm hoping someone might be able to help me out. I'm not a programmer but am very tech savvy and can generally navigate my way around.

I've downloaded the Garmin SDK and ran the Fit2CSV tool on some Garmin FIT files I exported from Garmin Connect's daily summary. I'm not entirely sure why there were 5 FIT files in the download and which of the files is going to have the data I need but I have converted them all successfully to CSV. Specifically, I need to be able to extract and plot the heart rate data from the daily wellness file and be able to accurately plot the values of the heart rate from the timestamp_16.

After opening each file, I am superfluously dumbfounded with how to convert the timestamp_16 data into a meaningful timestamp. I did find a great timestamp converter here http://www.nlvocables.com/blog/?p=969 which also included a great excel formula for converting timestamps using the Garmin epoch: =((A1+631065600)/60/60/24)+DATE(1970,1,1)

However, for the life of me, I just CANNOT work out how to get the actual timestamp from the timestamp_16 values in a wellness file. There is no documentation I've found that helps in any way.

As I said, I am not a programmer and the only solution to the problem I have found was a programming solution being:

mesgTimestamp += ( timestamp_16 - ( mesgTimestamp & 0xFFFF ) ) & 0xFFFF;

I have no idea what this means and no idea how to action it.

Is there someone who can PLEASE help me out and explain what to do?

Top Replies

Jason Brisbane over 2 years ago in reply to Ben FIT +1 verified

Hi Ben, thank you SO MUCH for this explanation. I think I've been able to work it out in excel but there are still a few issues I just wanted to clarify. I'm getting very close to the values I see plotted…

All Replies

0 Jason Brisbane over 2 years ago

UPDATE:

Downloaded the latest version of the SDK.
I have tried adding options to the fit2csv java command and tried adding the -iso8601 modifier but unfortunately it still did not convert any of the timestamp_16 files into ISO8601 datetimes.

I used the command java -jar fitcsvtool.jar -iso8601 -b wellness5kb.fit wellness5kb.csv

The conversion was successful but no change to any of the timestamp_16 fields. Why is this so hard!? Why make life so difficult!?!?!?

Any help is so greatly appreciated!!!

0 Jason Brisbane over 2 years ago

Update 2 - Ben FIT is amazing and his response below is incredibly helpful

0 Ben FIT over 2 years ago in reply to Jason Brisbane

Fields like timestamp, start_time, time_created, etc all have a data type of date_time. date_time values are the number of seconds since the FIT Epoch, which is described here: https://developer.garmin.com/fit/cookbook/datetime/

The timestamp_16 field is not a date_time, rather it is a uint16. The 16 bit values are used to keep the size of the file small, which is important for files that may contain 24 hours of monitoring information. The timestamp_16 values are the value of the previous timestamp field with the upper two bytes masked off plus any time change that occurred between the two monitoring messages.

What you will see in the file is a monitoring message with a timestamp field, a full date_time, followed by monitoring messages with a timestamp_16 field.

Displaying the values as hex makes is easier to see.

Definition,0,monitoring,timestamp,1,
Data,0,monitoring,timestamp,"0x3C75A6D4",s
Definition,1,monitoring,timestamp_16,1,,heart_rate,1
Data,1,monitoring,timestamp_16,"0xA710",s,heart_rate,"83",bpm

If the last seen timestamp is 0x3C75A6D4, masking the upper two bytes results in a value of 0x0000A6D4. Compare that to the timestamp_16 value in the next message 0xA710 and you get...

0xA710 - 0xA6D4 = 0x3C = 60 seconds

60 seconds between messages is what is expected, but there may be some variation.

You can mask the lower two bytes of the full timestamp 0x3C750000 and then AND it with the timestamp_16 to get a full date_time value of 0x3C75A6D4.

Every so often there will be a monitoring message with a timestamp field (full date time) that you want to hold on to. Then as you come across monitoring messages with a timestamp_16, you use last timestamp and current timestamp_16 to come up with a timestamp for that message.

+1 Jason Brisbane over 2 years ago in reply to Ben FIT

Hi Ben, thank you SO MUCH for this explanation. I think I've been able to work it out in excel but there are still a few issues I just wanted to clarify. I'm getting very close to the values I see plotted in Garmin Connect.

First, I've run the Fit2CSV-data.bat file to get the Wellness CSV converted into a CSV that I can use.

From there, I've referred to the the ohr_settings.timestamp[s] field to get my constant timestamp value before the timestamp_16 that follows

I have converted the ohr settings timestamp decimal value (937404960) into a hexadecimal value using the DEC2HEX function in excel (0x37DFAA20).

If I mask the lower bytes of the timestamp I get 37DF

I then DEC2HEX my timestamp_16 value of 43612 to get AA5C being the upper bytes

Using the concatenate function I combine these two values to get my new resulting timestamp

Once I have this value, I then run HEX2DEC on it to get my full timestamp.

Using the excel function =((AJ11+631065600)/60/60/24)+DATE(1970,1,1) I am then able to convert this resulting timestamp from the Garmin Epoch into an actual datetime value.

The last step is to then convert this final datetime into a local date time by adding the UTC offset as a decimal. As I am in UTC+10 this equates to the addition of .416667 to the final computed date time to get me to where I need to be.

I get a computed local date time value for the first measurement of 15/09/2019 00:16

This is correct to the minute. Thank you for helping out, this is the solution.

0 jdbcomo over 2 years ago in reply to Jason Brisbane

Thanks Jason,

I was trying to do the same thing in Excel and this has helped. With regards to your final excel function ("=(Aj11..", this can be significantly simplified.. You don't need to convert it to a 'Unix" timestamp, and then add the date for 1/1/1970. You can just add the full timestamp that you have calculated to the start of the garmin epoch (31/12/1989 +your timezone). Note that Excel's "epoch" starts at 01/01/1900 0:00:00, unins starts on 1/1/1970 ans Garmin starts at 31/12/1989

My formula is

Time ="final Timestamp"/86400+"Garmin Epoch start date" (86400 is 24*60*60)

I've combined the concatenation into the formula and it is:

Time =HEX2DEC(CONCATENATE($N$4,DEC2HEX(A2)))/86400+$M$2

Where

- $N$4 is the Hex value of the upper bytes (from the full timestamp)

- A2 is the timestamp_16 that you are converting

- $M$2 is the start of the Garmin Epoc in your timezone. I'm UTC+8, so I've set the start of the epoch to 31/12/1989 8:00