Time - Adding minutes and seconds

  • No, no.... you were spot on. TIME columns are ok for durations of 1 day but aren't actually duration columns. I also have a hard time understanding why people would want to separate date and time even for display purposes unless they are, in fact, just trying to display duration. Certainly, I wouldn't store date and time separately.

    I might store just the date if it's a column guaranteed to only ever need to be a whole date but then there's the problem of doing nasty little conversions like the one on this thread. For example, you asked if I was suggesting the following...

    2012-12-21 05:00 + 2012-12-24 07:00

    If the second date/time is supposed to be the duration and the first date/time is the start date, then kind of but not quite. If someone worked 1 hour, 13 minutes, and 59 seconds, then any of the following would work just fine to get the EndDate...

    2012-12-21 05:00 + '01:13:59'

    2012-12-21 05:00 + '1900-01-01 01:13:59' --Admittedly, confusing, but shows how things work.

    StartDate + Duration -- Where both are data/time datatypes and the duration is stored as a result of (for example) '1900-01-01 01:13:59' .

    Yeah... I know this stuff doesn't work for any of the "new" date/time datatypes. I think that MS really and unnecessarily made it a whole lot more difficult to do such simple things as adding a simple duration to a starting date and time. I wish they would have (no pun intended) spent the time making a proper "Duration" datatype that would allow you to store a (for example) 49 hour duration as something a little easier for folks to figure out other than 1900-01-03 01:00:00. For example, the following DOESN"T currently work...

    SELECT Duration = CAST('49:00:00' AS DATETIME)

    Instead, you have to go through a bunch of hooie to parse the hours, minutes, and seconds and then DATEADD each of those back to "0" (1900-01-01).

    Same goes the other way around. Using subtraction between a start and end date/time is easy and accurate even across years. With the new date/time data types, you have to do something stupid like doing a DATEDIFF in milliseconds and then a DATEADD to "0" to reconvert it back to a date/time data type.

    --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)

  • +100% (agreement). Merry Christmas Jeff!


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Merry Christmas Dwain.

    --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)

  • Jeff Moden (12/25/2012)


    Merry Christmas Dwain.

    Let me see. Christmas morning where you are and here you are posting on the SSC forum.

    You must want that 32,000th post pretty bad!


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (12/25/2012)


    Jeff Moden (12/25/2012)


    Merry Christmas Dwain.

    Let me see. Christmas morning where you are and here you are posting on the SSC forum.

    You must want that 32,000th post pretty bad!

    Nah... number of posts is a nice badge but that's not why I post. SQL isn't only my job, it's a hobby. Some folks do Sudoku, cross word puzzles, video games, etc... I like figuring out SQL problems.

    --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)

  • Jeff Moden (12/29/2012)


    dwain.c (12/25/2012)


    Jeff Moden (12/25/2012)


    Merry Christmas Dwain.

    Let me see. Christmas morning where you are and here you are posting on the SSC forum.

    You must want that 32,000th post pretty bad!

    Nah... number of posts is a nice badge but that's not why I post. SQL isn't only my job, it's a hobby. Some folks do Sudoku, cross word puzzles, video games, etc... I like figuring out SQL problems.

    Me too! 😎

    Happy New Year to you Jeff (and everybody else on this thread)! :hehe:


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Jeff Moden (12/24/2012)...I also have a hard time understanding why people would want to separate date and time even for display purposes unless they are, in fact, just trying to display duration. ...

    Jeff, for what it's worth, I had a requirement to do a query a few weeks ago that only displayed the time part (and formatted as AM/PM too). The spec required that the date part NOT be displayed.

    The output was for a golf tournament signup schedule and the end-user needed a column to display tee times. These were actual times of course and not durations. The day date was on the page showing the tee times for that day. I'd think this might apply as well to any scheduling situation such as at a doctor's office, etc.

    In my case, the "date" values coming from the application source for that column were in the form "10:35","14:22", etc. And the user's SQL version was 2005 so the TIME datatype was unavailable! I discovered though that the DATETIME datatype accepts input such as "14:22" as is and merely converts it to "1900-01-01 14:22:00.000". I considered trying to put in the actual date, but since there was this implicit conversion and the day date was stored in another date column (bad design? yeah!), I just used it as is.

    Examples:

    --SQL 2005

    DECLARE @TeeTime DATETIME

    SET @TeeTime = '14:22'

    SELECT @TeeTime AS TeeTime

    --> Output: 1900-01-01 14:22:00.000

    --SQL 2008

    DECLARE @TeeTime1 TIME

    SET @TeeTime1 = '14:22'

    SELECT @TeeTime1 AS TeeTime

    --> Output: 14:22:00.0000000

    Getting durations:

    DECLARE @Time1 TIME

    SET @Time1 = '14:22'

    DECLARE @Time2 TIME

    SET @Time2 = '14:28'

    SELECT @Time2-@Time1 AS TimeDuration

    --> Output: error: 'Operand data type time is invalid for subtract operator.'

    DECLARE @Time3 DATETIME

    SET @Time3 = '14:22'

    DECLARE @Time4 DATETIME

    SET @Time4 = '14:28'

    SELECT CAST(@Time4-@Time3 AS TIME) AS TimeDuration

    --> Output: 00:06:00.0000000

    Adding time to get a new time:

    DECLARE

    @Time5 TIME

    ,@TimeInterval1 TIME

    SET @Time5 = '14:22'

    SET @TimeInterval1 = '00:06'

    SELECT @Time5+@TimeInterval1 AS NextTime

    --> Output: error: 'Operand data type time is invalid for add operator.'

    DECLARE

    @Time6 DATETIME

    ,@TimeInterval2 DATETIME

    SET @Time6 = '14:22'

    SET @TimeInterval2 = '00:06'

    SELECT CAST(@Time6+@TimeInterval2 AS TIME) AS NextTime

    --> Output: 14:28:00.0000000

     

  • Lynn Pettis (1/27/2010)


    Like this?

    declare @ TimeVal time,

    @ TotalTime time; -- space added between @ and variable name to allow code to post

    set @TimeVal = '08:05:44.0000000';

    set @TotalTime = '00:13:00.0000000'

    select @TimeVal,

    @TotalTime,

    dateadd(ss, ((DATEPART(HOUR,@TotalTime) * 60) + DATEPART(MINUTE,@TotalTime) * 60) + DATEPART(SECOND,@TotalTime), @TimeVal),

    @TimeVal + @TotalTime;

    i'm also doing something similar as to what you did here above but in my case i would like to know from you if it is possible to replace

    @TimeVal = '08:05:44.0000000'; with @TimeVal = [RefreshIntervalColumn]

    Because in my case is every records interval time is different, how do i go about it....

    this is what im using DATEADD(s, RefreshIntervalSeconds, GETDATE())

  • Steven Willis (1/2/2013)


    The output was for a golf tournament signup schedule and the end-user needed a column to display tee times. These were actual times of course and not durations. The day date was on the page showing the tee times for that day. I'd think this might apply as well to any scheduling situation such as at a doctor's office, etc.

    From the part of you statement I highlighted above it's obvious you still need to know on which day any particular time will be used.

    Time is still bound to date, and it's totally irrelevant if disconnected.

    What you are describing is a presentation issue, and it must be dealt with on UI, not in databse.

    I do not see you are using TIME datatype to solve any other issue than presentation format.

    And DATYTIME is still easier to use here.

    "Date" portion of any datetime value defines if this time to be displayed on the page for the selected date.

    When you display some events for a day you include all date-time values which fall in between of beginning of the day and end of the day.

    When you display events for an afternoon you include all date-time events between midday of the day and end of the day.

    Now, try to do it with separate date and time! You'll have to bring date and time together into a datetime value and work it out from there.

    And the format of the "time" portion displayed on the page is better defined by using CONVERT to string data types (varchar, nvarchar, etc.) rather than to TIME (I'm pretty sure users won't be happy to see on UI all those trailing zeros showed in your examples).

    DECLARE @TeeTime DATETIME

    SET @TeeTime = '14:22'

    SELECT CONVERT(char(8), @TeeTime, 8) AS TeeTime

    --> Output: 14:22:00

    SELECT CONVERT(char(5), @TeeTime, 8) AS TeeTime

    --> Output: 14:22

    Very nice display, much better than from using implicit conversions from TIME data type.

    Same logic applies to inserting a time.

    When setting up a time you have you date selected, and you effectively are setting date-time, not time only.

    Bind date and time parts of the event on the way from UI to database as save it as it should be saved - datetime value.

    You mentioned "doctor's office" as an example. I wonder - how many times did you hear about a doctor's appointment set up for a time without specifying a date?

    Would you be happy to check on doctor's office every day to find if the appointment time you've got is for today actually?

    :hehe:

    _____________
    Code for TallyGenerator

  • Sergiy, you make some good points. However, in my original post I wasn't particularly concerned with the excess of zeroes because like you said, it's better that the UI handle that formatting function and in this application it does.

    As for the time being bound to the date...well, no, not in this case. As I pointed out in the OP the data I'm getting from the application is actually in the form "10:35","14:22", etc. The column of times is part of a set of data for just one particular day so that date is displayed elsewhere and is not connected to the tee time column (though it could be concatenated into a datetime if it was necessary which in this case it wasn't).

    The only reason I even needed to use a date/time-related datatype conversion at all was for sorting purposes and for calculating a duration--and that there are major differences between SQL2005 and SQL2008 when trying to do that. Pointing out these differences was really all I was trying to demonstrate.

    But believe me that I'm not trying to be defensive or snippy. Feedback is always appreciated. Iron sharpens iron. 😉

     

  • Steven Willis (3/4/2013)


    As for the time being bound to the date...well, no, not in this case. As I pointed out in the OP the data I'm getting from the application is actually in the form "10:35","14:22", etc.

    Oh, really?

    There is no any "ConnectDate" in the same row?

    And you cannot tell on which day that "ConnectTime2" has happened?

    Sorry, I find it very hard to believe.

    Because when Verizon charges a customer for the calls they summarize all call durations for a month, so they have to know on which day any particular connection was established and on which day it it was ended.

    So please, do not tell anyone that the application stores only times with no dates. Because it's simply - not true.

    _____________
    Code for TallyGenerator

  • Sergiy (3/5/2013)


    Steven Willis (3/4/2013)


    As for the time being bound to the date...well, no, not in this case. As I pointed out in the OP the data I'm getting from the application is actually in the form "10:35","14:22", etc.

    Oh, really?

    There is no any "ConnectDate" in the same row?

    And you cannot tell on which day that "ConnectTime2" has happened?

    Sorry, I find it very hard to believe.

    Because when Verizon charges a customer for the calls they summarize all call durations for a month, so they have to know on which day any particular connection was established and on which day it it was ended.

    So please, do not tell anyone that the application stores only times with no dates. Because it's simply - not true.

    Whatever...

    These values "10:35","14:22", etc. are from an HTML form and they are posted as strings generated by a hard-coded HTML dropdown with the 5 character pseudo-"time" values as an option item in the select input. So they are just numeric-looking character strings until someone (me) does something with them. The list of "times" is not dynamically generated as the HTML form is just a static hard-coded form.

    Now I wrote in my post above that I know the date these values apply to, but that date (also a string since this is HTML we are talking about) needs conversion also and I COULD IF I WANTED TO concatenate the date and the times to create a well-formed datetime value but as you have correctly surmised the date itself IS stored in another column already. As for the "time" column all I wanted to do was give the "times" back to the application in a manner that they would sort as time and not in ASCII sequence and allow me to determine durations between the values. I just didn't need to bother with the date-part nor did I care about the date-part since all of the time values in a particular batch ALWAYS belong to the same day. (Golfers don't have tee times that cross midnight into the next day. Maybe if the course was in the arctic I'd have to worry about that.)

    The post was originally made to show that there are rare cases like this where the date part doesn't matter. If that wasn't the case why would Microsoft have bothered creating a TIME datatype? I don't know what your issue is. I made an observation concerning a situation I encountered and if you never face such an issue then I guess the whole point is moot. I don't want to argue about it.

     

  • Steven Willis (3/5/2013)


    Sergiy (3/5/2013)


    Whatever...

    🙂

    These values "10:35","14:22", etc. are from an HTML form and they are posted as strings generated by a hard-coded HTML dropdown with the 5 character pseudo-"time" values as an option item in the select input. So they are just numeric-looking character strings until someone (me) does something with them. The list of "times" is not dynamically generated as the HTML form is just a static hard-coded form.

    So, there is no actually a time value sent from application.

    It's a string which can be parsed to a time value sent along with another string which can be parsed to a date value.

    It's purely you choice to store them separately, not any kind of requirement coming from the business case.

    Poor choice, I totally agree with Jeff here:

    Jeff Moden (12/24/2012)


    I also have a hard time understanding why people would want to separate date and time even for display purposes unless they are, in fact, just trying to display duration. Certainly, I wouldn't store date and time separately.

    Now I wrote in my post above that I know the date these values apply to, but that date (also a string since this is HTML we are talking about) needs conversion also and I COULD IF I WANTED TO concatenate the date and the times to create a well-formed datetime value but as you have correctly surmised the date itself IS stored in another column already.

    Yes, as I said - it was your choice.

    As for the "time" column all I wanted to do was give the "times" back to the application in a manner that they would sort as time and not in ASCII sequence and allow me to determine durations between the values.

    As I showed in my post, simple use of CONVERT function will allow to do just that.

    CONVERT is actually better as it gives better control over the formatting of output strings.

    I just didn't need to bother with the date-part nor did I care about the date-part since all of the time values in a particular batch ALWAYS belong to the same day. (Golfers don't have tee times that cross midnight into the next day. Maybe if the course was in the arctic I'd have to worry about that.)

    You still need to know to WHICH DAY any particular tee break applies.

    So, you actually do need to bother with the date-part.

    Otherwise you would not need to store it in the database.

    The post was originally made to show that there are rare cases like this where the date part doesn't matter.

    Turns out - your example does not show such a case.

    If that wasn't the case why would Microsoft have bothered creating a TIME datatype?

    🙂

    It's not the only moronic feature MS added to the product.

    There must be a reason why MS designers named them "F... me nodes".

    I don't know what your issue is. I made an observation concerning a situation I encountered and if you never face such an issue then I guess the whole point is moot. I don't want to argue about it.

    I have no issues with that.

    Apparently you have. :hehe:

    Because it's you who posted the problem which caused only by your belief that you've got a case when separating date and time in a database is a right thing to do.

    What was the solution to you problem?

    Right, concatenating date and time back together.

    Only "advantage" MS provided to you by introducing TIME data type is that you're gonna do concatenation every time you run a query to display the schedule or produce a report instead of doing it once when saving the data in the database.

    I'm not gonna use (hopefully :-P) you application, so I don't have an issue with that.

    As for your users - they'll have to put up with sluggish performance of the application.

    But they'll learn.

    _____________
    Code for TallyGenerator

Viewing 13 posts - 16 through 27 (of 27 total)

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