Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Converting Quartz.Net time columns to datetime Expand / Collapse
Author
Message
Posted Wednesday, November 24, 2010 6:31 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:41 AM
Points: 1,889, Visits: 1,001
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

Post #1026285
Posted Thursday, November 25, 2010 12:18 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 35,216, Visits: 31,670
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1026342
Posted Monday, November 29, 2010 7:54 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:41 AM
Points: 1,889, Visits: 1,001
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?
Post #1027350
Posted Monday, November 29, 2010 8:42 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:41 AM
Points: 1,889, Visits: 1,001
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.
Post #1027384
Posted Monday, November 29, 2010 5:29 PM This worked for the OP Answer marked as solution


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 35,216, Visits: 31,670
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1027684
Posted Wednesday, March 12, 2014 4:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 10:47 AM
Points: 3, 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?
Post #1550140
Posted Wednesday, March 12, 2014 8:15 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:41 AM
Points: 1,889, Visits: 1,001
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
Post #1550256
Posted Wednesday, March 12, 2014 10:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 10:47 AM
Points: 3, 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...
Post #1550345
Posted Wednesday, March 12, 2014 11:05 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:41 AM
Points: 1,889, Visits: 1,001
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
Post #1550352
Posted Wednesday, March 12, 2014 10:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 10:47 AM
Points: 3, 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.
Post #1550536
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse