UTC conversion to date and time problem

[OnJay]

I need to do the UTC to actual date conversion is for some CSV data files that we recorded a few days ago but I'm having a problem with the UTC time vs the system time. Today I recorded a CSV file with the UTC time using the time(NULL) function and also the year, month, day etc using the GetSystemDate and GetSystemTime functions. I'm confused since the numbers seem to disagree! For the UTC time I got 3528537632 and for the date and time I got 10/25/2011  2:20:31 PM recorded in the data file. Converting the 3528537632 number to date and time using Excel I get 10/23/11 13:20, but it should be 10/25/2011  2:20:31 PM. So Excel is giving me a date/time in error by two days and 1 hour.

For my data files that we recorded the other day, I could simply add 49 hours to the 10/23/11 13:20 and would get the correct date, but that's a bit unsettling. Any idea on what's happening here? Is the UTC time incorrect? Or is it just the Excel conversion that's incorrect? 

Any ideas?

Thanks,

John

[RobertoBozzolo · Accepted solution]

Now this is strange: I would expect a difference of 1 day because of the bug described in this article, not 2 days! Nevertheless, the 2-days shift is there to see...

The 1-hour error is explained by DST.

Additionally, consider using GetCurrentDateTime () function to obtain UTC date and time in a single pass. The following code gives the same result for time () and GetCurrentDateTime () functions, after appropriate corrections are applied due to DST and time zone: you can paste it in the Interactive Execution window and run it.

#include <ansi_c.h>
#include <utility.h>
#include <userint.h>
static double dt;

DebugPrintf ("time() + DST + zone: %u\n", time (NULL) + 7200);
GetCurrentDateTime (&dt);
DebugPrintf ("GetCurrentDateTime : %f\n", dt);

[OnJay]

Thanks Roberto. I am very surprised (really) that there is at least the 1 day error in Excel's calculation. I thought Excel would be the gold standard. I don't understand where the extra 1 day would come from, but I recorded the UTC time of 3528537632 and the date and time from of 10/25/2011  2:20:31 PM using the LW CVI GetSystemDate and GetSystemTime functions. So I'm pretty sure I got the correct UTC and data & time, and don't want to spend any more time tracking down the extra one day error, although it is perplexing and arouses my curiousity.

Thanks again,

John

[ElectroLund]

I can confirm this 2-day error when dropping CVI UTC dates into Excel as well.

I guess for my purposes, a "correction factor" of +2 works.  Not satisfying, but there you go.