## Time - Adding minutes and seconds

 Author Message dwain.c SSC-Forever Group: General Forum Members Points: 44397 Visits: 6431 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)! My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!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?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables Steven Willis SSCertifiable Group: General Forum Members Points: 5333 Visits: 1721 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 2005DECLARE @TeeTime DATETIMESET @TeeTime = '14:22'SELECT @TeeTime AS TeeTime--> Output: 1900-01-01 14:22:00.000``--SQL 2008DECLARE @TeeTime1 TIMESET @TeeTime1 = '14:22'SELECT @TeeTime1 AS TeeTime--> Output: 14:22:00.0000000`Getting durations:`DECLARE @Time1 TIMESET @Time1 = '14:22'DECLARE @Time2 TIMESET @Time2 = '14:28'SELECT @Time2-@Time1 AS TimeDuration--> Output: error: 'Operand data type time is invalid for subtract operator.'``DECLARE @Time3 DATETIMESET @Time3 = '14:22'DECLARE @Time4 DATETIMESET @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 TIMESET @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` I_Slater Old Hand Group: General Forum Members Points: 318 Visits: 29 [quote]Lynn Pettis (1/27/2010)Like this?`declare @ TimeVal time, @ TotalTime time; -- space added between @ and variable name to allow code to postset @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()) Sergiy SSC Guru Group: General Forum Members Points: 59524 Visits: 12981 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 DATETIMESET @TeeTime = '14:22'SELECT CONVERT(char(8), @TeeTime, 8) AS TeeTime--> Output: 14:22:00SELECT 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? Steven Willis SSCertifiable Group: General Forum Members Points: 5333 Visits: 1721 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. ;-) Sergiy SSC Guru Group: General Forum Members Points: 59524 Visits: 12981 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. Steven Willis SSCertifiable Group: General Forum Members Points: 5333 Visits: 1721 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. Sergiy SSC Guru Group: General Forum Members Points: 59524 Visits: 12981 Steven Willis (3/5/2013)[quote]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. 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.