CODEWITHSUNDEEP
c#dateunix-timestampalternate
RunFranks525
RunFranks525

Reputation: 163

strange epoch date number issue

I'm working with an data set in which there is a date field with dates that look like the following:

42437.4261290278
42437.5460402431
42437.5478825116

with the larger of the numbers being the most recent. One of us believes it is relating to unix epoch and alternate time representations. The issue we are facing now is reading those date's above into standard MM-DD-YYYY format. Any one have any ideas on how to convert these alternate date forms into standard dates?

I'm trying to do this in C#. And for reference, I expect that the last two dates listed to be sometime on March 8th, 2016 and the first to be some time before then.

Upvotes: 0

Views: 957

Answers (2)

Andrew Morton
Andrew Morton

Reputation: 25047

Following your assertion that the dates represented are 2016-03-08, I assume the start of the epoch is 1899-12-30:

static string UnixTimeStampToDateAsString(double ts)
{
    DateTime epoch = new DateTime(1899, 12, 30);
    DateTime d = epoch.AddDays(ts);
    return (d.ToString("yyyy-MM-dd HH:mm:ss"));
}

static void Main(string[] args)
{
    foreach (double dateNumber in new double[] { 42437.4261290278, 42437.5460402431, 42437.5478825116 })
    {
        Console.WriteLine(UnixTimeStampToDateAsString(dateNumber));
    }
    Console.ReadLine();

}

Outputs:

2016-03-08 10:13:37
2016-03-08 13:06:17
2016-03-08 13:08:57

I have to state that the 30th of December 1899 is a rather unlikely value, but I suppose someone might have had a "reason" to use that.

Edit Thanks to @EricLippert I can suggest this instead:

Console.WriteLine(DateTime.FromOADate(dateNumber).ToString("yyyy-MM-dd HH:mm:ss"));

Upvotes: 1

Eric Lippert
Eric Lippert

Reputation: 660407

These are OLE Automation VT_DATE values. This is the date system used by Microsoft products such as Excel and pre-.NET versions of Visual Basic. It is a somewhat bizarre date format.

The format is: consider the double to have two parts: a signed integer and an unsigned fraction. The signed integer is the number of days since 30 Dec 1899. Note NOT 31 Dec 1899 and NOT 1 Jan 1900. The fraction is the fraction of the 24 hour (always!) day gone by. No adjustment is made for the 23 or 25 hour days we have twice a year.

This format has an interesting (to me) history; you can read about it in my blog article from 2003:

https://blogs.msdn.microsoft.com/ericlippert/2003/09/16/erics-complete-guide-to-vt_date/

And Stack Overflow founder Joel Spolsky's article from 2006:

http://www.joelonsoftware.com/items/2006/06/16.html

Note that if you have negative VT_DATE values then you must be very careful to get the conversion correct. The code is not hard; it's just a couple lines, but you have to reason about it carefully. I used to ask "take the difference between two VT_DATEs" as an interview question, and a surprising number of candidates cannot actually do subtraction.

Upvotes: 7

Related Questions