Date Math In Oracle related to .NET Ticks
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?
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.
链接地址: http://www.djcxy.com/p/18534.html上一篇: 寻找某一年开始的日子