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.

Breathing

Answer 1

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.

Broeker

The question is not "What is Datetime.Ticks?" The question is - in Quartz.net, when a date / time is stored as a big int, what is the meaning of that data? – Breathing

The meaning depends on the context. It's usually the start time of trigger, the end time of trigger or last fire time. Column name should reveal the intention on a column basis. – Broeker

Please accept my apologies. I replied in haste, managing to skip straight to "From MSDN:". Thank you for taking the time to answer my question. – Breathing

No worries, glad that I could help. – Broeker

Answer 2

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)

Schulze

I tried this out and got a difference of exactly 2 hours. I think i need to add +2h Time shift from UTC base time. – Tubuliflorous

@Tubuliflorous Yes, if you are UTC+2, then you would need to add 2 hours to the results of the function to get the time in your local time zone. – Schulze

@Schulze In my test using flaot the calculation causes miliseconds differences, maybe roundoff error, e.g. 8:30:00,000 is represented as 08:29:59.997. Taking out the float part gives the exact time! What am I missing? – Assoil

@Assoil Yes, doing SELECT CAST(CAST(CAST('March 13, 2013 8:30' AS DATETIME) AS FLOAT) AS DATETIME) generates a tiny rounding error, and 0:59.997 is SQL Server's approximation of 1 millisecond below 1:00.000. SQL Server doesn't record exact milliseconds, it actually uses 1/300th of a second (3.33 millisecond) accuracy for DATETIME, hence the loss of 3 milliseconds due to rounding errors. – Schulze

This post is old but I just want to say this should be the accepted answer. – Crazy

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

Breathing

Or, to get ticks for a date in C#: new DateTime(2010, 11, 01).Ticks which gives 634241664000000000 – Supramolecular

Answer 4

If you are with Quartz .Net and Postgress use this.

The value stored in database is the DateTime.Ticks value

Select  to_timestamp(((NEXT_FIRE_TIME  - 621355968000000000) / 10000000)) FROM  qrtz_triggers

Mensa