Quartz .Net - Meaning of BigInt DateTime
we've used sql server as our persisted data store for Quartz.net. I'd like to write some queries looking @ the Time values. Specifically - Qrtz_Fired_Triggers.Fired_Time, Qrtz_Triggers.Next_fire_time, Prev_fire_time.
For the life of me, I can't find anything that says what this data is - ticks, milliseconds, microseconds, nanoseconds. I've guessed at a couple of things, but they've all proven wrong.
The best answer would include the math to convert the big int into a datetime and perhaps even a link(s) to the pages/documentation that I should have found - explaining the meaning of the data in those fields.
If you have specific instructions on using Quartz .Net libraries to view this information, that would be appreciated, but, I really have 2 goals - to understand the meaning of the date/time data being stored and to keep this in T-SQL. If I get the one, I can figure out T-SQL or out.
Answer
On the SQL side, you can convert from Quartz.NET BIGINT times to a DateTime in UTC time with:
SELECT CAST(NEXT_FIRE_TIME/864000000000.0 - 693595.0 AS DATETIME) FROM QRTZ_TRIGGERS
Numbers Explanation
Values stored in the column are the number of ticks from .NET DateTime.MinValue in UTC time. There are 10000 ticks per millisecond.
The 864000000000.0 represents the number of ticks in a single day. You can verify this with
SELECT DATEDIFF(ms,'19000101','19000102')*10000.0
Now, if we take March 13, 2013 at midnight, .NET returns 634987296000000000 as the number of ticks.
var ticks = new DateTime(2013, 3, 13).Ticks;
To get a floating point number where whole numbers represent days and decimal numbers represent time, we take the ticks and divide by the number of ticks per day (giving us 734939.0 in our example)
SELECT 634987296000000000/(DATEDIFF(ms,'19000101','19000102')*10000.0)
If we get put the date in SQL and convert to a float, we get a different number: 41344.0
SELECT CAST(CAST('March 13, 2013 0:00' AS DATETIME) AS FLOAT)
So, we need to generate a conversion factor for the .NET-to-SQL days. SQL minimum date is January 1, 1900 0:00, so the correction factor can be calculated by taking the number of ticks for that time (599266080000000000) and dividing by the ticks per day, giving us 693595.0
SELECT 599266080000000000/(DATEDIFF(ms,'19000101','19000102')*10000.0)
So, to calculate the DateTime of a Quartz.NET date:
- take the value in the column
- divide by the number of ticks per day
- subtract out the correction factor
- convert to a DATETIME
SELECT CAST([Column]/864000000000.0 - 693595.0 AS DATETIME)