SQL Server Performance

Best way to store hours and minutes

Discussion in 'Getting Started' started by Jancouver, Jun 24, 2008.

  1. Jancouver New Member

    I have a field for hours and minutes. What would be the best way of storing for exaple 72 hours? I'm using smalldatetime however I'm having problems when the value is more than 24 hours. Another example is 26 hours and 30 minutes. I also need to be able to search the data field same as date/time. Any suggestions?
    Thanks!
  2. tosc New Member

    Hi Jancouver,
    what about this:
    SET NOCOUNT ONCREATE TABLE #temp_table ( hm VARCHAR(5))INSERT #temp_table VALUES('20:30')INSERT #temp_table VALUES('5:01')INSERT #temp_table VALUES('12:59')INSERT #temp_table VALUES('7:20')SET NOCOUNT OFFSELECT CONVERT(VARCHAR, SUM(DATEDIFF(ss, '1900-01-01 00:00', CONVERT(DATETIME, hm)))/3600) + ':' + CONVERT(VARCHAR, (SUM(DATEDIFF(ss, '1900-01-01 00:00', CONVERT(DATETIME, hm)))%3600)/60) FROM #temp_tableDROP TABLE #temp_table-------------------------------------------------45:50(1 Zeile(n) betroffen)
  3. FrankKalis Moderator

    [quote user="tosc"]
    Hi Jancouver,
    what about this:
    SET NOCOUNT ONCREATE TABLE #temp_table ( hm VARCHAR(5))INSERT #temp_table VALUES('20:30')INSERT #temp_table VALUES('5:01')INSERT #temp_table VALUES('12:59')INSERT #temp_table VALUES('7:20')SET NOCOUNT OFFSELECT CONVERT(VARCHAR, SUM(DATEDIFF(ss, '1900-01-01 00:00', CONVERT(DATETIME, hm)))/3600) + ':' + CONVERT(VARCHAR, (SUM(DATEDIFF(ss, '1900-01-01 00:00', CONVERT(DATETIME, hm)))%3600)/60) FROM #temp_tableDROP TABLE #temp_table-------------------------------------------------45:50(1 Zeile(n) betroffen)
    [/quote]
    Hm, I can't help, but this looks familiar to me [;)]
    Anyway, if you are only concerned about storing hours and minutes one (small)int column might be sufficient. The math to derive hours and minutes from a value stored there, is trivial, it requires less storage space than a varchar(5) and for representation purposes you still can use Toscs' approach.
  4. tosc New Member

    <P mce_keep="true">[quote user="FrankKalis"] <P>Hm, I can't help, but this looks familiar to me <IMG alt=Wink src="http://sql-server-performance.com/Community/emoticons/emotion-5.gif">&nbsp;</P><P>[/quote]</P><P>I'm sure, this looks to you <A href="http://www.insidesql.org/beitraege/datetime/zeitwerte-addieren">http://www.insidesql.org/beitraege/datetime/zeitwerte-addieren</A>&nbsp;[Y]</P>
  5. TommCatt New Member

    Not sure what you are trying to do, but here is a simple way. Use an int, shift hours two places to the left and add minutes. This makes it human readable, so 72 hours and 15 minutes make the integer value 7215. To separate back to hours/minutes: for minutes take modulo 100, for hours divide by 100.
    @Value = @hrs * 100 + @mins;
    @Mins = @Value % 100; @Hrs = @Value / 100;
    This, of course, assumes minutes will always be in the range 0 to 59.

  6. moh_hassan20 New Member

    solution1:
    you say "I'm having problems when the value is more than 24 hours. Another example is 26 hours and 30 minutes"
    that means , that you can not use the data field same as date/time , bec format is hh:mm:ss , and 26 hour is more than one day.
    So as suggested by tosc, to use varchar , you can use convert(varchar, getdate(), 8) to get varchar data in format hh:mm:ss
    solution2:
    Also you can store both hour , and minutes in two different integer fields , and use datetime functions to do your process.
    Be sure that more than 24 hour take you to next day , as datetime , and that is not what you need.
    You must study carefully what the process computation and search criteria you plan to use, in order to select the best solution , and to get best performance in search.
  7. Jancouver New Member

    Thanks to all for your advice.
    I guess I will go with one of those options (either varchar or smallint) and will convert the values on SELECT.
    Thanks again
    Jan

Share This Page