How to store length of time in DB

What's the best way to store things like "7 years 4 mon开发者_JAVA技巧ths" (for example, years of experience) in the database. Float?

Store a BIGINT number of seconds or ticks.

In your specific example (years of experience), it would be better to store the start date and calculate the amount of time when you read the database.

Since SQL-server 2005 lacks the time-only data-types, I would use an integer type, of appropriate size to hold the count of "some time interval" - seconds, milliseconds, etc. I would do all the translations (except comparisons) in the app tier; no reason for the DB to worry about them except as an int/bigint/etc

Use long [read bigint for SQLServer] to represent time diff converted to seconds

As this field is a variable, the best way to approach this is to store the (estimated) date of the event.

The time until now should be calculated at run-time.

Edit: If needed, you can also store the end date to use in the run-time calculation.

It depends, if you store the time period - i.e. The Start date and end date, this becomes a derivable value, and you can calculate everytime.

If you are going to be storing 7.4 to simulate a year.month then you are limited in your granularity. You could never display weeks, what would happen if a value for 7.14 was added?

Store it as is. I.e. a string 7 years 4 months. Why? Well lets think from the other end - what is 7 year (or 4 months)? On the surface one would think that it is a time interval, but it is not. Neither month nor year have static number of time units (days) in them, so the actual value would be different depending on the starting point for your interval.

Thanks for all the minuses, but this is in fact the only answer that addresses the question as is. The 7 years 4 months is not a value that is serializable without starting point. Therefore presentation == value.

I don't think you really need precision to the second if you're storing something like years of experience. I would just store the number in years and store it as a float.

So "7 years 4 months" would be equal = 7 + 4/12 = 7.333..