date - Storing a trial duration in years, months, days, hours in SQL Server -


update: clear future viewers, accepted answer not propose different or better approach given in question (it uses "total number of days" (or hours) column). discussion in comments, after initial confusion, resulted there's no way cover both ease of use , precision, using single inexact column in "days" approach went with, , accepted answer represents that. cheers!

our products have licenses, , licenses might trials, having end time starting point when first used. trial period consists of number of years, months, days and, ideally, hours well.

we wanted store period in sql server database, struggling. there doesn't seem data type this, based on this documentation.

  • datetimeoffset not real "offset" date , offset utc (please correct me if i'm wrong), doesn't seem support concepts "0 months".

  • storing "total number of seconds" or "total number of hours" susceptible time changes around globe. hard @ value (as human) , have notion of how long trial is.

  • other alternatives, such storing string "1y2m15d5h" (1 year, 2 months, 15 days , 5 hours. extreme example) render queries impossible, such searching trials duration longer 1 year (there might specified 14m, more year).

possible solutions are:

  1. adding 4 columns (years, months, days, hours). make queries more complex wanted.

  2. forgetting support durations in hours (probably uncommon case) , using "total number of days" instead. unfortunately, can't accurately represent year, because of leap years (365 days might not full year).

do have other possible solutions this? readable, queryable format time/date offset? couldn't find concrete answer after searching web.

as stated myself , others in comments, simplify duration values store either in hours or days, ignore longer months , leap years sake of simplicity.

this solution stores duration in days, keep simple queries ui , has computed value hours used in calculation of end date, although may not need both columns, i'm presenting them option:

create table #trials (     customer nvarchar(20),     starttime datetime,     trialdays int,     trialhours as(trialdays*24),     endtime datetime )  insert #trials (     customer,     starttime,     trialdays,     endtime ) values (   n'bob', getdate(), 1, null), -- 1 day (   n'dave', getdate(), 7, null), -- 7 days (   n'jon', getdate(), 30, null), -- 30 days (   n'tom', getdate(), 90, null), -- 90 days (   n'jim', getdate(), 365, null) -- 365 days (approximation of year)  select * #trials t  update #trials set endtime = dateadd(hour, trialhours, starttime)  select * #trials t  drop table #trials

produces:

customer    starttime               trialdays   trialhours  endtime bob         2017-09-11 10:50:30.940 1           24          2017-09-12 10:50:30.940 dave        2017-09-11 10:50:30.940 7           168         2017-09-18 10:50:30.940 jon         2017-09-11 10:50:30.940 30          720         2017-10-11 10:50:30.940 tom         2017-09-11 10:50:30.940 90          2160        2017-12-10 10:50:30.940 jim         2017-09-11 10:50:30.940 365         8760        2018-09-11 10:50:30.940

Comments

Post a Comment