none
SSIS and Time with milliseconds

    Question

  • I've been working on a project to ensure that accross our entire data warehouse everything is at the same accuracy level as far as time - migrating everything to use the full hh:miTongue Tieds.mmm.  Some places were using hh:miTongue Tieds:mmm (colon instead of decimal point) and many places not using milliseconds.

     

    The SQL server portion went essentially without issue - however SSIS is not cooperating.  For example I have data I am importing from a file that is in format: hh:miTongue Tieds (no milliseconds) that I need to compare to data from SQL (now containing full milliseconds) - matching on time ranges.  Previously this was done by converting both to the "database time" datatype in SSIS and comparing.  Now when converting the cTime to database time SSIS fails and complains "The value could not be converted because of a potential loss of data".  

     

    I don't want to lop off the milliseconds becuase that could create rounding errors.

     

    I don't like it but the only option I can think of is keeping everything in string and comparing the strings... then I run into the issue of making sure to convert all sources of data into the exact same  hh:miTongue Tieds:mmm format in text.

     

    Is there an easier way?  I know some people use "milliseconds since midnight" and so forth but that would require major reworking of the data warehouse and many packages.  

    Tuesday, May 22, 2007 10:40 PM

Answers

  • Hi Chris,

     

    If you want to use milliseconds DT_DBTIMESTAMP is the type to use. DT_DBTIME only goes up to seconds. See this link http://msdn2.microsoft.com/en-us/library/ms141036.aspx for the description of the current types supported by SSIS. We will consider your suggestion about having a time with fraction of seconds support for the next versions of SSIS. Until than DT_DBTIMESTAMP is your best bet.

     

    Thanks,

    Silviu  

    Wednesday, May 23, 2007 1:17 AM

All replies

  • Argh... stupid smileys.... is there a way to turn them off in a post?
    Tuesday, May 22, 2007 10:41 PM
  • Another thing I could do is add an arbitrary date to all places comparing time.  Its also a pain becuase you have to add it before the comparison, change datatype to db timestamp, then convert back to text and be sure to strip off the arbitrary date.
    Tuesday, May 22, 2007 11:00 PM
  • I realize that this isn't exactly answering your question, but I'd like to pose a few questions back at you:

    • Since your effort is designed to "ensure that accross our entire data warehouse everything is at the same accuracy level as far as time," does it make sense to use the smallest possible level of granularity, and just make up the miliseconds portion for sources that do not natively contain it?
    • Is it an option to use the lowest common deniminator - selecting the smallest level of granularity (probably seconds) that is common to all data sources?

    Obviously I don't know anything about the specifics of your project or your data, but storing miliseconds (even if they are 000) for records sourced from systems that do not track at this level of detail seems like inventing significant digits beyond the capabilities of your measuring equipment. I would question the validity of analysis that operates at the milisecond level while taking as inputs data sourced from a system that does not natively track at this level.

     

    (With this said, please let me add that I have never worked on a data warehouse project were we tracked anything at a sub-second grain, so it may be that I'm simply missing the point. ;D )

     

    WIth this said, matching on ranges is probably the way to go - it's what I would probably try next were I in your role. Can you please share information on the data types of the various fields you're using, and the operations you're performing on them to get the "potential loss" error you mentioned?

     

    Tuesday, May 22, 2007 11:50 PM
  • Financial market analysis...  (you think its bad with milliseconds Wink people are complaining that SQL is only accurate to the 3 millisecond level).

     

    Milliseconds are needed for many things including sorting trades to trace the flow of them in order of execution (whether they are uptrending etc) and for measuring how long between one system signals a buy and another system actually does the buy.  Those are two examples - there are many more.

     

    We frequently store time in a char(12) field.  Adding the trailing 0s where needed allows better "apples to apples" comparison between different sources of data - after all if you threw the values into datetime it would do the same (add trailing 0s).

     

    The problem with rounding is... is there a way to round the time in SSIS? Say you have time of 10:21:02.997... it comes in as text - as far as I know rounding that to a second is not something I can easily support without writing a custom script.  Using left(time,8) will give you incorrect results.  Even using SQL's convert function from type 121 to 120 just hacks off the .997 instead of converting up to 03 for the seconds.  Where this becomes important is range lookups... if you wanted > :02 this would fall out becuase it would appear as :02.

     

    Wednesday, May 23, 2007 12:38 AM
  • Thankfully all our range lookups are at the second level... So what I am doing now is going through the existing packages and one by one analyzing how they use time and what the granularity level is, and adjusting appropriately -  in many cases removing milliseconds from some sources, doing the comparison lookups etc, then tacking the .000 back on as it goes into SQL.  Totally feels like hacking to me and I hate doing it but it will produce correct results for packages where only second accuracy is needed. 

     

    All this would be easy if SSIS had the db time datatype contain milliseconds... seems nuts that the dbTimestamp datatype has milliseconds but not dbTime.

     

    Microsoft has said they were working on some datetime stuff with Katmai - here is to hoping that have a Time datatype with millisecond accuracy and they include that in SSIS as well.  I've worked a number of large internet retailers and everywhere I haved worked has had issues that would have been made easier if seperate date and time fields could be used. Smile

    Wednesday, May 23, 2007 12:49 AM
  • Hi Chris,

     

    If you want to use milliseconds DT_DBTIMESTAMP is the type to use. DT_DBTIME only goes up to seconds. See this link http://msdn2.microsoft.com/en-us/library/ms141036.aspx for the description of the current types supported by SSIS. We will consider your suggestion about having a time with fraction of seconds support for the next versions of SSIS. Until than DT_DBTIMESTAMP is your best bet.

     

    Thanks,

    Silviu  

    Wednesday, May 23, 2007 1:17 AM