SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Time - Adding minutes and seconds


Time - Adding minutes and seconds

Author
Message
Nayeem Mansoori
Nayeem Mansoori
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 45
We sincerely thank you..
It Saves a lot of Efforts of mine.:-)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218301 Visits: 41995
neevmansoori (12/22/2012)
We sincerely thank you..
It Saves a lot of Efforts of mine.:-)


Yeah, but as old as this post is, I'm not sure that it answers what needs to be done for display purposes if the following data is present...

Connect Time2 totalTime2
23:00:00.0000000 01:00:00.0000000

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18111 Visits: 6431
Jeff Moden (12/22/2012)
neevmansoori (12/22/2012)
We sincerely thank you..
It Saves a lot of Efforts of mine.:-)


Yeah, but as old as this post is, I'm not sure that it answers what needs to be done for display purposes if the following data is present...

Connect Time2 totalTime2
23:00:00.0000000 01:00:00.0000000


I think what Jeff is alluding to without saying it is the TIME data type is a "time of day" and not a "time duration." Adding them together implies that at least one of the values is a "time duration." How can you add 4PM + 6PM?

Perhaps though, it has some meaning in another multiverse.


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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218301 Visits: 41995
What I'm actually trying to get people to realize is that using JUST the time for this type of stuff (EndTime = StartTime + Duration) is the wrong thing to do unless it's ok to "wrap the clock" after midnight. If you have a duration that exceeds 24 hours starting just before midnight of one day, then you really need to show that the call or whatever duration your trying to portray ended two days later just after midnight.

In other words, both StartTime and EndTime must show the date AND the time for both.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18111 Visits: 6431
Jeff Moden (12/23/2012)
What I'm actually trying to get people to realize is that using JUST the time for this type of stuff (EndTime = StartTime + Duration) is the wrong thing to do unless it's ok to "wrap the clock" after midnight. If you have a duration that exceeds 24 hours starting just before midnight of one day, then you really need to show that the call or whatever duration your trying to portray ended two days later just after midnight.

In other words, both StartTime and EndTime must show the date AND the time for both.


Of course. Sorry Jeff, I didn't mean to speak for you.

But of interest, what is the meaning in the physical world of the following addition?

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

If your answer is that you're adding the number of days between Jan 1, 1900 and Dec 12, 2012 (+ 7 hours) to the date Jan 21, 2012 (at 5AM), I'm with you. I'm just saying it's a kludge (albeit a widely used one). Because MS hasn't been so kind to provide a specific type for a date/time interval.

Personally, my preference is to use the DATETIME to hold the date and an INT (or BIGINT) to hold the minutes, or if need be seconds, to represent the interval. Ignoring for the moment the issues you might encounter using BIGINT.


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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218301 Visits: 41995
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18111 Visits: 6431
+100% (agreement). Merry Christmas Jeff!


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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218301 Visits: 41995
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18111 Visits: 6431
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!

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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218301 Visits: 41995
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search