3

I've got 2 days worth of time that I can't account for :-).

I've got integral values stored in a database that comes from DateTime.UtcNow.Ticks in C# code. I'm trying to write some simple DB queries to convert these back to human-readable times and I can't get the math just right.

According to this documentation: http://msdn.microsoft.com/en-us/library/system.datetime.ticks(v=vs.110).aspx

"A single tick represents one hundred nanoseconds or one ten-millionth of a second. There are 10,000 ticks in a millisecond. The value of this property represents the number of 100-nanosecond intervals that have elapsed since 12:00:00 midnight, January 1, 0001, which represents DateTime.MinValue. It does not include the number of ticks that are attributable to leap seconds."

Therefore, I'm expecting to convert ticks stored in an Oracle database to a date by the following math:

Take the ticks and divide by ten million to convert to seconds. Take the seconds and divide by (60*60*24) to convert to days.

Convert the number of days to an interval and add it to "12:00:00 midnight, January 1, 0001" to get a final date.

In my testing, I used 621355968000000000 as the value for the ticks, which corresponds to Unix epoch time.

This is the query I came up with:

select to_char(to_date('0001-01-01','YYYY-MM-DD') + numtodsinterval(621355968000000000/(10000000*60*60*24),'DAY'), 'DD-Mon-YYYY HH24:MI:SS') as now from dual

However, this returns a result that's 2 full days before Unix epoch time. This query returns the result I want:

select to_char(to_date('0001-01-03','YYYY-MM-DD') + numtodsinterval(621355968000000000/(10000000*60*60*24),'DAY'), 'DD-Mon-YYYY HH24:MI:SS') as now from dual

So what I can't figure out is why I need to start from Jan 3rd instead of Jan 1st? Where are the two lost days coming from?

andrew.w.lane
  • 118
  • 3
  • 10
  • For future readers: there is no point in even trying this (and in case of trying you have to get very reliable inputs (ticks) and analyse the required types (big calculations, where used numeric type might be very relevant)). The recommendable proceeding is relying on the .NET capabilities (this is why you are using .NET precisely :)) `DateTime date2 = new DateTime(ticks);` will easily and accurately convert ticks into the actual date. – varocarbas Nov 18 '13 at 19:00

2 Answers2

0

Your tick time is wrong. If you subtract the dates you will see that your tick time are losing 2 days.

select (621355968000000000 / (10000000 * 60 * 60 * 24)) from dual;

result days: 719162

select TO_DATE('1970-01-01', 'YYYY-MM-DD') - TO_DATE('0001-01-01', 'YYYY-MM-DD') from dual;

result days: 719164

This is the reason to 2 "lost" days.

Andrew Paes
  • 1,940
  • 1
  • 15
  • 20
  • This code would suggest otherwise: var epoch = new DateTime(year: 1970, month: 1, day: 1, hour: 0, minute: 0, second: 0, kind: DateTimeKind.Utc); Console.WriteLine(epoch.Ticks); // output is 621355968000000000 – andrew.w.lane Nov 18 '13 at 18:34
  • 1
    I am happy for the OP to find what he was looking for. But just out of curiosity... what is the difference between the OP's statement "this calculations are off by two days" and your statement "these calculations output 719162 and the real ones 719164"? Is not this the same than what the OP was saying? Why do you assume that the error is in the number of ticks (coming from a-still-unkown place) instead of in the calculations? What further information provides your statement on top of the one provided by the OP? (I am afraid that the answer is: none). – varocarbas Nov 18 '13 at 18:51
  • Geez I'm a noob. I inadvertently marked this as the accepted answer. I've tried to undo that. – andrew.w.lane Nov 18 '13 at 20:09
  • He wondered why he would have to start counting on the 3rd of January and then became clear that the problem was not in the calculation but the constant used in the calculation, which should be 6.21357696 and +17. If he wants to start on January 1, then use the correct constant. – Andrew Paes Nov 18 '13 at 22:34
0

While I cannot provide a reason for where those two lost days are going, the following logic seems to make more sense than arbitrarily starting from 0001-01-03.

The logic is lifted from the following answer on converting .NET ticks to Obj-C Date, which provides a great explanation. Essentially we rebase the calculation from UNIX epoch (1970-01-01) and count the ticks from there.

select to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS') +
       (
          (TICKS_COLUMN - 621355968000000000 ) -- (now ticks - UNIX epoch in CLR ticks)
            /10000000 -- CLR ticks per second
        )/60/60/24 -- convert from seconds to days for date addition arithmetic
as date_from_ticks
from my_table;
notracs
  • 433
  • 5
  • 8