SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Converting Quartz.Net time columns to datetime


Converting Quartz.Net time columns to datetime

Author
Message
Jedak
Jedak
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2681 Visits: 1123
My company has a product that utilizes the Quartz.Net 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85426 Visits: 41078
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jedak
Jedak
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2681 Visits: 1123
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
Jedak
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2681 Visits: 1123
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
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85426 Visits: 41078
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
SwastikMS
SwastikMS
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 14
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
Jedak
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2681 Visits: 1123
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
SwastikMS
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 14
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
Jedak
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2681 Visits: 1123
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
SwastikMS
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 14
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search