Dealing with Time Values

David posted on Friday, September 08, 2006

I'm struggling with "Time" values at present and wonder how other people have dealt with this.

My database (SQL Server 2005) stores Time values in a (nullable) date column with no date component.

In my BOs I am using a SmartDate. To allow meaningful comparisons, I have ensured that all of my Time values do not contain a date component. However, this means that 12am is the same as DateTime.MinValue, which I use to represent a Null for all my date values.

I thought about using the MaxValue to represent Nulls, but I'm nervous about not having a consistent approach toward Null representation and how this could easily lead to bugs. Especially since I'm using CodeSmith to create the BOs and they all default to using the MinValue.

Any ideas on how others have dealt with this would be greaty appreciated.

ajj3085 replied on Friday, September 08, 2006

You may want to create a SmartTime class, which will ignore any date parts. You can track empty via a boolean variable within the class, instead of comparing the current value to a min or max value, which exludes those values from use in the set.

HTH
Andy

figuerres replied on Friday, September 08, 2006

More than once I have thought that there should be 3 types:

Date -- no hours,minutes,seconds etc....

Time - with hh:mm:ss.tttt that hold a max of 24h 0s

DateTime - like we have now.

and be able to add a Date and a time to make a datetime and other related conversions.

calender apps and other date and time related stuff could really benifit IMHO

pelinville replied on Friday, September 08, 2006

I agree with another poster about the three date/time types. But untill then...

For times I store them as integers. Up to this point I only use minute precision but I can see where this will have to change and I will have to use at least second precision to begin with. I have a smartTime class that converts them (integer.minvalue is null) to a datetime value with the date = 1/1/01.

After working with this model I should have done the following for all my time stuff.

First default to at least second resolution.

Second create a UDT in the db to control the allowed values.

Third the SmartTime class should use the timespan classes in the frame work as well as the datetime classes.

David replied on Friday, September 08, 2006

Thanks for your comments.

I ended up creating a SmartTime structure stealing a fair bit from the SmartDate source code. I also created a SmartDuration (based on the TimeSpan type) as I required some different behaviour (e.g. values greater than 24hrs). It is now working really well and I was able to greatly simplify the code in my UI controls.

With regard to storing times in the database as integers, doesn't this complicate your queries?

pelinville replied on Friday, September 08, 2006

You would think it would but the smart time class goes both ways. Give it a time and it gives back and integer. Or a time range is converted to an integer range. Plug that into the query and all is good.

All my queries are called by parent objects so that functionality is readily available. Objects be smart!

But I could see a problem if human made quries that don't know the rule must be created.

For me not a problem. The database is only a storage mechanism for the objects. For reports we have a second database which is a transformation of the application DB just for reports with those values converted into datetime stuff.

figuerres replied on Saturday, September 09, 2006

I wonder.... with SQL CLR and 2005 defined types...

could we have sql return a new type ?

if not then have a SQL CLR type for time that stores an unsigned int but displays a day + time string

then have a matching .net type that does the rest of what we might want.

5,184,000.0 would be 24 hours as 60th's of a second -- that should be good time res for all but a few uses.

32 bits unsigned would give: 828 days and about 12 hours of storage so it would handle showing

days hh:mm:ss:tt

by having sql defined type handle storage and output formating -- like it does with datetime

then your .net side class would do a .Parse() on reading and a .ToSTring() on writing to sql.

seems like we could make it very easy to use.