Quartz .Net - Meaning of BigInt DateTime

Question

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 1

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)

Answer 2

The value stored in database is the DateTime.Ticks value. From MSDN:

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.

Answer 3

So, unless I missed something and am making this too complicated, I couldn't get the dateadd functions in Ms Sql Server 2008 to handle such large values and I kept getting overflow errors. The approach I took in Ms Sql Server was this: a) find a date closer to now than 0001.01.01 & its ticks value b) use a function to give me a DateTime value.

Notes: * for my application - seconds was good enough. * I've not tested this extensively, but so far, it has acted pretty well for me.

The function:

  CREATE FUNCTION [dbo].[net_ticks_to_date_time]
   (
      @net_ticks BIGINT
   )
   RETURNS DATETIME
   AS
   BEGIN

      DECLARE 
         @dt_2010_11_01 AS DATETIME = '2010-11-01'
      ,  @bi_ticks_for_2010_11_01 AS BIGINT = 634241664000000000
      ,  @bi_ticks_in_a_second AS BIGINT = 10000000

      RETURN
         (
            DATEADD(SECOND , ( ( @net_ticks - @bi_ticks_for_2010_11_01 ) / @bi_ticks_in_a_second ) , @dt_2010_11_01)
         );

   END
  GO

Here is how I came up with the # of ticks to some recent date:

DECLARE 
   @dt2_dot_net_min AS DATETIME2 = '01/01/0001'
   , @dt2_first_date AS DATETIME2
   , @dt2_next_date AS DATETIME2
   , @bi_seconds_since_0101001 BIGINT = 0


   SET @dt2_first_date = @dt2_dot_net_min;

   SET @dt2_next_date = DATEADD ( DAY, 1, @dt2_first_date )



WHILE ( @dt2_first_date < '11/01/2010' )
   BEGIN
      SELECT @bi_seconds_since_0101001 = DATEDIFF(SECOND, @dt2_first_date, @dt2_next_date ) + @bi_seconds_since_0101001

      PRINT 'seconds 01/01/0001 to ' + CONVERT ( VARCHAR, @dt2_next_date,  101) + ' = ' + CONVERT ( VARCHAR, CAST ( @bi_seconds_since_0101001 AS MONEY ), 1)

      SET @dt2_first_date = DATEADD ( DAY, 1, @dt2_first_date );
      SET @dt2_next_date = DATEADD ( DAY, 1, @dt2_first_date )      

   END