Float numbers in Date Time conversion from Unix format to Excel!

Hi,

I'm writing a sensor data log in a SD Card to use it in a Excel spreadsheet afterwards, and one of the info is the current time.

To convert the date time from Unix format to Excel format I use the known formula that for a Unix Timestamp equal to 1462420139 I get 42495.15902! However these are the results in Arduino:

// Excel conversion formula
// Excel Timestamp = (Unix Timestamp / 86400) + 25569

String excelTime = String(now.unixtime() / 86400 + 25569);

// Result = 42495
Serial.println(excelTime);

excelTime = String((float)now.unixtime() / 86400.00000 + 25569.00000);

// Result = 42495.16
Serial.println(excelTime);

How to get all five decimal places and not only 2 to import in Excel?

If you need accuracy, just do not use floats.

BTW using String is something for mini-sketches and programs that do not need long term stability.

print defaults to two decimals. You can supply a second argument for the number of digits after the decimal.

Serial.print(someFloat, 4);

Will print 4 digits after the decimal.

Delta_G: Will print 4 digits after the decimal.

Which does not make them meaningful.

Whandall: Which does not make them meaningful.

No it doesn't. But it gives some knowledge the OP was missing.

I think it will make him believe it could be done like he is thinking it should work.

The next question could be 'Why does my program print wrong values?'

void setup() {
  Serial.begin(115200);
  float test = 1234567.8901234;
  Serial.print(F("value is "));
  Serial.println(test, 7);
}
void loop() {}
value is 1234567.8750000

Success, I did it!

Just change the formula and the resulting code to this:

// Adjusted Excel conversion formula with remainder function (%)
// Excel Timestamp = Integer [(Unix Timestamp / 86400) + 25569] + Real [(Unix Timestamp % 86400) / 86400]
// 1 day = 86400 seconds

String excelTime = String(now.unixtime() / 86400 + 25569) + "." + String(long(float(now.unixtime() % 86400) * 100000/86400 + 0.5));

// For 1462470042 the result is 42495.73660 (correct!)
Serial.println(excelTime);

Not math friendly but it works... :stuck_out_tongue_closed_eyes:

42495.73660

I wouldn't count on that. With floats you can only count on about 6 digits of precision. See @Whandall's example above.

That type of programming fits the extensive String usage.

May you live in interesting times!

Delta_G: 42495.73660

I wouldn't count on that. With floats you can only count on about 6 digits of precision. See @Whandall's example above.

Yes, but Whandall used more than 5 digits in his example, float have only 6-7 decimal digits of precision and so at the 8 digit he got the expected error.

In my case, if you check it carefully, I only used 5 digits of precision, so I never exceeded the float precision. You may easily check in your calculator that the result is correct:

1462470042 / 86400 + 25569 = 42495.7366

In the end this value is to be logged in a text file, so I don't get why I shouldn't use strings mainly when the number is too big to be normally handled!

NOTE: In reality I used 6 digits of precision when I add 0.5, still inside the precision of the float. I did this to round the value instead of truncate it, for instance, the round of 8.5 is 9, so, long(8.5) = 8 while long(8.5 + 0.5) = 9!

No, it's not 6 after the decimal. It's six digits total. The ones before the decimal count too. If you have numbers in the billions then the hundreds, tens, and ones place will have issues.

ruiseixas: In the end this value is to be logged in a text file, so I don't get why I shouldn't use strings mainly when the number is too big to be normally handled!

Because the String class has a nasty habit of making Swiss cheese out of the very limited amount of RAM on an Arduino. It fragments the memory with just about every step. Using c-style char arrays is much safer in RAM limited environments.

ruiseixas: mainly when the number is too big to be normally handled!

Is it?

void setup() {
  Serial.begin(115200);
  long testL = 424957366;
  Serial.print(F("value is "));
  Serial.println(testL);
  Serial.print(F("value could be printed as "));
  Serial.print(testL / 10000L);
  Serial.write('.');
  Serial.println(testL % 10000L);
}
void loop() {}
value is 424957366
value could be printed as 42495.7366

Delta_G: No, it's not 6 after the decimal. It's six digits total. The ones before the decimal count too. If you have numbers in the billions then the hundreds, tens, and ones place will have issues.

I used in the total, where are you seeing more than 6 in total?

42495.73660

I count 10 digits there.

Delta_G: 42495.73660

I count 10 digits there.

Yes, but that is the result of the string, I added two numbers of 5 digits as a string, not as a calculation! There are two distinct calculations with 5 digits, and only in the end I added as string, XXXXX + "." + XXXXX, there isn't any number with 10 digits, just a string!

ruiseixas: I used in the total, where are you seeing more than 6 in total?

ruiseixas: In my case, if you check it carefully, I only used 5 digits of precision, so I never exceeded the float precision. You may easily check in your calculator that the result is correct:

1462470042 / 86400 + 25569 = 42495.7366

I count 9.

These nine where used in my example.

Whandall: I count 9.

These nine where used in my example.

Nine in the string, please count in the formulas while as numbers... Strings can be of any size!

Yes, your example fails if you use 10 digits! Nevertheless you are using string operations when you add the "." separator... So you have it, strings are needed for very long numbers!

You may easily check in your calculator that the result is correct:

Strings in a calculator?

But besides that, 9 digits will not fit in a float even when they resided in string before (with exeptions). You can push them in, but you are only guaranteed to get back 6 to 7 that are correct.

With longs you get 9 full digits without any problems, ten with restrictions. If you need more, there are classes for arbitrary length numbers. Often you can split the number, or you have a natural fractional part.

Floats have their place, but they are very rarely neccessary.

I'm not here to preach, anybody is free to believe whatever he wants.