Best practice to store - How many days, minute and second ?

Question

Best practice to store - How many days, minute and second ?

Adelia

SSC-Addicted

Points: 422
More actions
October 31, 2021 at 10:17 am
Go to Answer
#3946398
Hi,
I plan to create HelpDesk Database. One of the table will store - Response Time & Resolution Time. It in 24 hours. The value as following,
1. 2 Days, 14 Hours, 30 minute
2. 0 Days, 19 Hours, 0 minute
3. 1 Days, 23 Hours, 15 minute
4. And so on
How to plan accurate datatype?
- This topic was modified 3 years ago by Adelia.
Grant Fritchey

SSC Guru

Points: 398882
More actions
November 1, 2021 at 11:44 am
Answer
#3946654

Strong suggestion, don't store the response time. Store the start and stop times and then calculate the response times when you retrieve the data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Viewing 15 posts - 1 through 15 (of 25 total)

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

Steve Collins SSCrazy Points: 2155 More actions · Answer 1

How to plan data types? It's simple: use the minimum necessary size given your anticipated precision requirements. Aside: there are edge cases having to do with all sorts of exceptions but with SQL that's always the case. The SQL Server Docs are pretty good when it comes to factual details. For date and time there are only 6 data types. The greater the accuracy you need the more storage the type requires. Imo Help Desk response and resolution times could be stored as SMALLDATETIME which is accurate to the minute

Date and Time Data Types and Functions (Transact-SQL)

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 2

This will shock most but I've had to climb this hill many times in the past. These are "durations". Don't store them as date and time datatypes. Before I can tell you more on how to make this and all the future aggregations and analysis of these durations super fast with a super small footprint, I need to know what the column names and datatypes for the original start and stop columns that you use to calculate the response and resolution times. And, yeah... almost all of your duration calculations for reporting can be made without any temporal math until display time, if you do it right and for very little space.

The best thing to do would be to post the CREATE TABLE statement where ALL of this is originally stored.

--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 SSC Guru Points: 1004313 More actions · Answer 3

Grant Fritchey wrote:

Strong suggestion, don't store the response time. Store the start and stop times and then calculate the response times when you retrieve the data.

I have to ask, what is the advantage you're thinking about when you make this recommendation compared to storing the result of the calculation that will never change?

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

Jeff Moden wrote:

Grant Fritchey wrote:
Strong suggestion, don't store the response time. Store the start and stop times and then calculate the response times when you retrieve the data.
I have to ask, what is the advantage you're thinking about when you make this recommendation compared to storing the result of the calculation that will never change?

I've seen people convert the results to text or a value, then need to format it separately, but they didn't store the start & stop time, just the start time and the end result as text. If you have the start & stop, you'll always be able to get the duration, no matter what happens in terms of format, etc.

Heck, store both, but I'd absolutely have the start & stop.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ratbak Ten Centuries Points: 1358 More actions · Answer 5

One would hope response time wouldn't change... though I've seen companies where even that might not be true.

I could see resolution time changing (e.g., help desk declares it resolved, user argues/proves it wasn't). One could reasonably argue that's a bad design and only the submitter (or a manager/proxy) should be able to define a ticket as resolved (but I believe such systems exist). Or that a new ticket should be created when resolution is challenged -- though I would argue that approach hides/obfuscates the facts, giving an impression of faster average resolution... not that anybody would every do that deliberately 🙂

Given minutes precision shown, an integer ResolutionMinutes column would seem to suffice for storing the datediff.

Let the front end format the output.

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 6

Grant Fritchey wrote:

Jeff Moden wrote:
Grant Fritchey wrote:
Strong suggestion, don't store the response time. Store the start and stop times and then calculate the response times when you retrieve the data.
I have to ask, what is the advantage you're thinking about when you make this recommendation compared to storing the result of the calculation that will never change?
I've seen people convert the results to text or a value, then need to format it separately, but they didn't store the start & stop time, just the start time and the end result as text. If you have the start & stop, you'll always be able to get the duration, no matter what happens in terms of format, etc.
Heck, store both, but I'd absolutely have the start & stop.

Ah... got it and I totally agree. Start, stop, and duration. Best of all worlds for just about anything anyone could ask. And I totally agree... if you can only store 2 of the 3, store the start/stop dates/times. Everything is possible from that.

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

ScottPletcher SSC Guru Points: 101091 More actions · Answer 7

I'd say it depends on how often you need to show the resolution duration. That's a rather complex calc to do repeatedly. And presumably the Resolution Time would only be set once and not be constantly changing.

Therefore, I'd likely calc and store the durations permanently, using a trigger. Use a smallint to store the days, and tinyint for the hours and seconds, like so: Resolution_Days smallint NULL, Resolution_Hours tinyint NULL, and Resolution_Minutes tinyint NULL.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 8

ratbak wrote:

One would hope response time wouldn't change... though I've seen companies where even that might not be true.
I could see resolution time changing (e.g., help desk declares it resolved, user argues/proves it wasn't). One could reasonably argue that's a bad design and only the submitter (or a manager/proxy) should be able to define a ticket as resolved (but I believe such systems exist). Or that a new ticket should be created when resolution is challenged -- though I would argue that approach hides/obfuscates the facts, giving an impression of faster average resolution... not that anybody would every do that deliberately 🙂
Given minutes precision shown, an integer ResolutionMinutes column would seem to suffice for storing the datediff.
Let the front end format the output.

Storing minutes sounds like a great idea but you can end up dealing with some really large numbers for aggregates that way. It also destroys the ability to use a finer resolution if a new requirement comes up.

Also, there are some times where no front end involved or reporting app is involved. For example, sometimes there's a requirement to send a file using a particular format.

--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 SSC Guru Points: 1004313 More actions · Answer 9

ScottPletcher wrote:

I'd say it depends on how often you need to show the resolution duration. That's a rather complex calc to do repeatedly..

I have to disagree. If you use the correct datatype, it's not a complex calculation at all. It's simple subtraction and, no... I'm not talking about converting to minutes or seconds, which would make it all more complex everywhere else. That's why I asked the op about datatypes.

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

ScottPletcher SSC Guru Points: 101091 More actions · Answer 10

Jeff Moden wrote:

ScottPletcher wrote:
I'd say it depends on how often you need to show the resolution duration. That's a rather complex calc to do repeatedly..
I have to disagree. If you use the correct datatype, it's not a complex calculation at all. It's simple subtraction and, no... I'm not talking about converting to minutes or seconds, which would make it all more complex everywhere else. That's why I asked the op about datatypes.

Computing days, hours and minutes separately will require separate calcs (at least off the total minutes). I would not do these calcs repeatedly if the duration needed to be displayed frequently. The space required to store them is so small I'd just store them instead.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 11

ScottPletcher wrote:

Jeff Moden wrote:
ScottPletcher wrote:
I'd say it depends on how often you need to show the resolution duration. That's a rather complex calc to do repeatedly..
I have to disagree. If you use the correct datatype, it's not a complex calculation at all. It's simple subtraction and, no... I'm not talking about converting to minutes or seconds, which would make it all more complex everywhere else. That's why I asked the op about datatypes.
Computing days, hours and minutes separately will require separate calcs (at least off the total minutes). I would not do these calcs repeatedly if the duration needed to be displayed frequently. The space required to store them is so small I'd just store them instead.

Oh... you'll get no argument from me that the duration should be saved in ONE separate column. I was taken exception to someone saying that it's a "rather complex calc". It's not, especially if you used the DATETIME datatype and wait until the end of any aggregation to display the result in whatever format you need.

I also would NOT store the duration in minutes or seconds, either. Even that can require way too much complexity and minutes might be way too coarse a measurement. Like Granny used to say, "Mind the pennies and the dollars with take care of themselves.

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

Adelia SSC-Addicted Points: 422 More actions · Answer 12

Adelia

SSC-Addicted

Points: 422

November 2, 2021 at 9:42 am

#3946988

Thanks to all

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 13

Adelia wrote:

Thanks to all

Adelia,

Don't go yet. I asked you some questions about datatypes that you've not answered yet. If you let me know that the datatypes of the start and end date/time columns are, I can probably give you a very small bit of code to do everything you currently need with some bulletproofing for the future.

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