Converting Quartz.Net time columns to datetime

Question

Post reply

Converting Quartz.Net time columns to datetime

Jedak

SSCertifiable

Points: 5589
More actions
November 24, 2010 at 6:31 pm

#229057

My company has a product that utilizes the Quartz.Net[/url] scheduling library. It is using SQL Server as its persistent storage. During some troubleshooting it was discovered some scheduling setup the application was doing was incorrect. This led to the need to for some TSQL method to convert the time column data to a datetime data type so it could be read to see when the triggers had fired and were going to fire. I have been unable to create a pure TSQL method to convert the data to a datetime data type in SQL Server 2k5. I have found a way in SQL Server 2k8, and there is also SQLCLR, but I'd prefer a pure TSQL without OA or extended stored procedures. Anyone have an ideas?
To give some details.
1. Quartz.Net stores its time values in a bigint.
2. The value is the column is the number of ticks that have elapsed since January 1, 0001 12:00am.
3. All ticks values are UTC date time values
4. There are 10000 ticks in a millisecond.
5. For example November 24, 2010 16:30 would be 634262130000000000

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

This will do it...

DECLARE @Ticks BIGINT

SELECT @Ticks = 634262130000000000

SELECT Converted = CAST(@Ticks/864000000000.0 - 693595.0 AS DATETIME)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jedak SSCertifiable Points: 5589 More actions · Answer 2

Jeff Moden (11/25/2010)
This will do it...
DECLARE @Ticks BIGINT
SELECT @Ticks = 634262130000000000
SELECT Converted = CAST(@Ticks/864000000000.0 - 693595.0 AS DATETIME)

Thank you. Would you mind explaining what the magic numbers are in the cast statement?

Jedak SSCertifiable Points: 5589 More actions · Answer 3

Jedak (11/29/2010)
Jeff Moden (11/25/2010)
This will do it...
DECLARE @Ticks BIGINT
SELECT @Ticks = 634262130000000000
SELECT Converted = CAST(@Ticks/864000000000.0 - 693595.0 AS DATETIME)
Thank you. Would you mind explaining what the magic numbers are in the cast statement?

Nevermind, I figured it out.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

My apologies. I normally document what I did a lot better but I was in a rush. Here's how I came to those numbers from the excellent description of the problem you gave. As usual with me, the details are in the comments in the code below...

/*************************************************************************************************

To give some details.

1. Quartz.Net stores its time values in a bigint.

2. The value is the column is the number of ticks that have elapsed since January 1, 0001 12:00am.

3. All ticks values are UTC date time values

4. There are 10000 ticks in a millisecond.

5. For example November 24, 2010 16:30 would be 634262130000000000

*************************************************************************************************/

--===== Here's the first number

SELECT MillisecondsPerDayTimes10000 = DATEDIFF(ms,'19000101','19000102')*10000.0

--===== If we divide the number of ticks for November 24, 2010 16:30

-- by the number of ticks per day (the first number), we end up

-- with a "Date Serial Number" where the whole numbers represent

-- the number of days since the January 1, 0001 12:00am epoch. The

-- decimal places represent the partial day which is also known

-- as the "Decimal Time".

SELECT Epoch1DateSerial = 634262130000000000/(DATEDIFF(ms,'19000101','19000102')*10000.0)

--===== Although SQL Server's DATETIME datatype is represented by two

-- 4 byte integers, they are most correctly interpreted using a

-- float conversion thusly...

SELECT Epoch2DateSerial = CAST(CAST('November 24, 2010 16:30' AS DATETIME) AS FLOAT)

--===== Since both Date Serial Numbers are for the same date and time,

-- (November 24, 2010 16:30), simple subtraction gives us the second

-- number which is the correction factor to convert one epoch to

-- the other as a number of days.

SELECT CorrectionFactorDays =

634262130000000000/(DATEDIFF(ms,'19000101','19000102')*10000.0) --Epoch1DateSerial

- CAST(CAST('November 24, 2010 16:30' AS DATETIME) AS FLOAT) --Epoch2DateSerial

--===== Finally, we make the simple substitutions to come up with the

-- simple formula to make the conversion to produce the correct

-- Date Serial Number from the number of ticks and convert that

-- Date Serial Number to an SQL Server DATETIME datatype and

-- we're done.

DECLARE @Ticks BIGINT

SELECT @Ticks = 634262130000000000

SELECT CAST(@Ticks/864000000000.0 - 693595 AS DATETIME)

For those that don't know what an "Epoch" is...

http://www.merriam-webster.com/dictionary/epoch

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwastikMS SSC Enthusiast Points: 145 More actions · Answer 5

But in my case, there was a difference of hours.

I have added UTC time difference. Now the difference is coming in minutes.

What should I do?

Jedak SSCertifiable Points: 5589 More actions · Answer 6

SwastikMS (3/12/2014)
But in my case, there was a difference of hours.
I have added UTC time difference. Now the difference is coming in minutes.
What should I do?

First post the code you are using and some examples of the values that are have a difference. Otherwise no one will be able to help.

There can be some inaccuracies with the method Jeff posted, but I found the differences were sub second. These differences did not matter for our purposes as it was just for diagnostics and debugging. If you use datetime2 and a loop added to the date and subtracting from the tick amount until you get to zero ticks left, the accuracies seem to go away, but that requires SQL Server 2008, which at the time was not an option (and kind of still isn't for certain of our customers). The other option is CLR which will not have any inaccuracies.

Jedak

SwastikMS SSC Enthusiast Points: 145 More actions · Answer 7

I used the below statement:

e.g.,

SELECT dateadd(hh,5.30,CAST(635302120392663258/864000000000.0 - 693595.0 AS DATETIME))

It's giving me a difference of approx. 30 minutes.

When I don't add the UTC difference, the result has a huge difference.

I am using Sql Server 2012.

Thanks...

Jedak SSCertifiable Points: 5589 More actions · Answer 8

SwastikMS (3/12/2014)
I used the below statement:
e.g.,
SELECT dateadd(hh,5.30,CAST(635302120392663258/864000000000.0 - 693595.0 AS DATETIME))
It's giving me a difference of approx. 30 minutes.
When I don't add the UTC difference, the result has a huge difference.
I am using Sql Server 2012.
Thanks...

I tested it on a SQL Server 2008 instance as I don't have a SQL Server 2012 instance.

SELECT dateadd(hh,5.30,CAST(635302120392663258/864000000000.0 - 693595.0 AS DATETIME)),

CAST(635302120392663258/864000000000.0 - 693595.0 AS DATETIME)

The above produces 2014-03-12 14:07:19.263,2014-03-12 09:07:19.263

Note that if you are trying to add 5 hours and 30 minutes to get your local time, 5.30 will not work. The second parameter to dateadd is an int. Here is the relevant documentation for dateadd. http://msdn.microsoft.com/en-us/library/ms186819(SQL.105).aspx. Convert 5 hours and 30 minutes entirely to minutes and add that number as minutes.

new DateTime(635302120392663258, DateTimeKind.Utc).ToString("yyyy-MM-dd HH:mm:ss:fff")

The above in LinqPad produces 2014-03-12 09:07:19:266 which is the exact UTC time for 635302120392663258.

Jedak

SwastikMS SSC Enthusiast Points: 145 More actions · Answer 9

Thanks for your reply.

I have already tried all these ways, but the result is more varying in these cases.

Adding 5:30 is giving me a difference of only 30 mins approximately.

Jedak SSCertifiable Points: 5589 More actions · Answer 10

Then what time are you expecting?

Here is an example of adding 5 hours and 30 minutes as minutes with results from SQL 2008

SELECT dateadd(n,330,CAST(635302120392663258/864000000000.0 - 693595.0 AS DATETIME)),

CAST(635302120392663258/864000000000.0 - 693595.0 AS DATETIME)

results: 2014-03-12 14:37:19.263, 2014-03-12 09:07:19.263