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

Question

Post reply

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

November 10, 2021 at 3:54 am

This was removed by the editor as SPAM
November 10, 2021 at 3:55 am

This was removed by the editor as SPAM
November 11, 2021 at 2:39 pm

This was removed by the editor as SPAM
November 20, 2021 at 1:33 pm

This was removed by the editor as SPAM

Viewing 11 posts - 16 through 25 (of 25 total)

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

Ken McKelvey SSCoach Points: 18484 More actions · Answer 1

If you are planning for large amounts of data you might be interested in some 'light' reading:

https://www.itprotoday.com/sql-server/interval-queries-sql-server

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

Ken McKelvey wrote:

If you are planning for large amounts of data you might be interested in some 'light' reading:
https://www.itprotoday.com/sql-server/interval-queries-sql-server

Itzik's article is fantastic but I hope that doesn't scare off the OP, who is just trying to figure out a duration based on a start and end date and then display it in a certain fashion. 😀

--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)
Intro to Tally Tables and Functions

ratbak SSC Eights! Points: 939 More actions · Answer 3

I'd love to see a generic framework/example for this, even if the OP has left the building.

I know your preference for datetime over datetime2, but would also be interested in what changes if datetime2 is used (if that's not asking too much).

Thanks, Jeff!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

ratbak wrote:

I'd love to see a generic framework/example for this, even if the OP has left the building.
I know your preference for datetime over datetime2, but would also be interested in what changes if datetime2 is used (if that's not asking too much).
Thanks, Jeff!

To be honest, I'd convert the DATETIME2 to DATETIME on the fly. In 99.99% of all cases, having a duration resolution of 3.3 milliseconds is still overkill but makes things really simple. I'd also convert the resulting duration (a DATETIME) to the underlying decimal date/time serial number to save it in a column, which will make any and all aggregations and other duration-related math simple. You just need to format the output to suit yourself in the end.

To describe the basic framework of using DATETIME2, you'd do something like a DATEDIFF_BIG() on seconds the then do all the other nasty stuff that people do to convert it. Of, you could just add the seconds to the SQLServer Base Date (0 or 19000101) and do things from there.

--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)
Intro to Tally Tables and Functions

Adelia SSC-Addicted Points: 407 More actions · Answer 5

Jeff Moden wrote:

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.

Hi Jeff,

I designed like this,

05112021-001

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

Adelia wrote:

Jeff Moden wrote:
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.
Hi Jeff,
I designed like this,

Ok... what are you using from that table to calculate those values? And are you using those values only for display purposes or do you use them for some analytical purpose down the road? I'm asking because there's no way that I'd store the response or resolution times that way but need the information I asked for to perhaps show you a better way.

--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)
Intro to Tally Tables and Functions

kuopaz Default port Points: 1440 More actions · Answer 7

As said above, if possible I would store start and end datetime rather than duration. I've had a similar helpdesk case - you store a calculated duration and then someone comes along and asks for weekends to be excluded for some customers, or to slice a time that spans over months.