Best way to store time (hh:mm) in a database

Question

You could store it as an integer of the number of minutes past midnight:

eg.

0 = 00:00 
60 = 01:00
252 = 04:12

You would however need to write some code to reconstitute the time, but that shouldn't be tricky.

If you are using SQL Server 2008+, consider the TIME datatype. SQLTeam article with more usage examples.

DATETIME start DATETIME end

I implore you to use two DATETIME values instead, labelled something like event_start and event_end.

Time is a complex business

Most of the world has now adopted the denery based metric system for most measurements, rightly or wrongly. This is good overall, because at least we can all agree that a g, is a ml, is a cubic cm. At least approximately so. The metric system has many flaws, but at least it's internationally consistently flawed.

With time however, we have; 1000 milliseconds in a second, 60 seconds to a minute, 60 minutes to an hour, 12 hours for each half a day, approximately 30 days per month which vary by the month and even year in question, each country has its time offset from others, the way time is formatted in each country vary.

It's a lot to digest, but the long and short of it is impossible for such a complex scenario to have a simple solution.

Some corners can be cut, but there are those where it is wiser not to

Although the top answer here suggests that you store an integer of minutes past midnight might seem perfectly reasonable, I have learned to avoid doing so the hard way.

The reasons to implement two DATETIME values are for an increase in accuracy, resolution and feedback.

These are all very handy for when the design produces undesirable results.

Am I storing more data than required?

It might initially appear like more information is being stored than I require, but there is a good reason to take this hit.

Storing this extra information almost always ends up saving me time and effort in the long-run, because I inevitably find that when somebody is told how long something took, they'll additionally want to know when and where the event took place too.

It's a huge planet

In the past, I have been guilty of ignoring that there are other countries on this planet aside from my own. It seemed like a good idea at the time, but this has ALWAYS resulted in problems, headaches and wasted time later on down the line. ALWAYS consider all time zones.

C#

A DateTime renders nicely to a string in C#. The ToString(string Format) method is compact and easy to read.

E.g.

new TimeSpan(EventStart.Ticks - EventEnd.Ticks).ToString("h'h 'm'm 's's'")

SQL server

Also if you're reading your database seperate to your application interface, then dateTimes are pleasnat to read at a glance and performing calculations on them are straightforward.

E.g.

SELECT DATEDIFF(MINUTE, event_start, event_end)

ISO8601 date standard

If using SQLite then you don't have this, so instead use a Text field and store it in ISO8601 format eg.

"2013-01-27T12:30:00+0000"

Notes:

• This uses 24 hour clock*

• The time offset (or +0000) part of the ISO8601 maps directly to longitude value of a GPS coordiate (not taking into account daylight saving or countrywide).

E.g.

TimeOffset=(±Longitude.24)/360 

...where ± refers to east or west direction.

It is therefore worth considering if it would be worth storing longitude, latitude and altitude along with the data. This will vary in application.

• ISO8601 is an international format.

• The wiki is very good for further details at http://en.wikipedia.org/wiki/ISO_8601.

• The date and time is stored in international time and the offset is recorded depending on where in the world the time was stored.

In my experience there is always a need to store the full date and time, regardless of whether I think there is when I begin the project. ISO8601 is a very good, futureproof way of doing it.

Additional advice for free

It is also worth grouping events together like a chain. E.g. if recording a race, the whole event could be grouped by racer, race_circuit, circuit_checkpoints and circuit_laps.

In my experience, it is also wise to identify who stored the record. Either as a seperate table populated via trigger or as an additional column within the original table.

The more you put in, the more you get out

I completely understand the desire to be as economical with space as possible, but I would rarely do so at the expense of losing information.

A rule of thumb with databases is as the title says, a database can only tell you as much as it has data for, and it can be very costly to go back through historical data, filling in gaps.

The solution is to get it correct first time. This is certainly easier said than done, but you should now have a deeper insight of effective database design and subsequently stand a much improved chance of getting it right the first time.

The better your initial design, the less costly the repairs will be later on.

I only say all this, because if I could go back in time then it is what I'd tell myself when I got there.

Just store a regular datetime and ignore everything else. Why spend extra time writing code that loads an int, manipulates it, and converts it into a datetime, when you could just load a datetime?

since you didn't mention it bit if you are on SQL Server 2008 you can use the time datatype otherwise use minutes since midnight

SQL Server actually stores time as fractions of a day. For example, 1 whole day = value of 1. 12 hours is a value of 0.5.

If you want to store the time value without utilizing a DATETIME type, storing the time in a decimal form would suit that need, while also making conversion to a DATETIME simple.

For example:

SELECT CAST(0.5 AS DATETIME)
--1900-01-01 12:00:00.000

Storing the value as a DECIMAL(9,9) would consume 5 bytes. However, if precision to not of utmost importance, a REAL would consume only 4 bytes. In either case, aggregate calculation (i.e. mean time) can be easily calculated on numeric values, but not on Data/Time types.

I would convert them to an integer (HH*3600 + MM*60), and store it that way. Small storage size, and still easy enough to work with.