Database: Storing Dates as Numeric Values

views:

answers:

Database: Storing Dates as Numeric Values

I'm considering storing some date values as ints. i.e 201003150900

Excepting the fact that I lose any timezone information, is there anything else I should be concerned about with his solution? Any queries using this column would be simple 'where after or before' type lookups. i.e Where datefield is less than 201103000000 (before March next year).

currently the app is using MSSQL2005. Any pointers to pitfalls appreciated.

+1 A:

Why wouldn't you use proper UNIX timestamps? They're just ints too, but they're not nearly as wide as 201103000000.

Ben S 2010-03-15 01:21:17

+1 for using an established standard instead of inventing your own. Also: some operations -- like finding the number of seconds between two dates -- become trivial.

Frank Farmer 2010-03-15 01:23:43

And some operations -- such as adding months/years -- become nearly impossible without casting it back to a date/datetime and doing all the translation back and forth. The internal representation is already a number; why roll your own and have to write your own libraries against it?

Adam Musch 2010-03-15 15:25:11

+3 A:

Using a proper datetime datatype will give you more efficient storage (smalldatetime consumes 4 bytes) and indexing, and will give you semantics that will be easier to develop against. You'd have to come up with a compelling argument to not use them.

Michael Petrotta 2010-03-15 01:24:11

Didn't know that presumed an int had to be smaller. Thanks.

Chin 2010-03-15 01:35:46

Both `smalldatetime` and `int` consume four bytes, but the example date you listed in your question can't be represented in an int - it's too big. Glad I could help.

Michael Petrotta 2010-03-15 01:38:31

right. thank you

Chin 2010-03-15 01:44:41

+1 A:

Just use the DATETIME or SMALLDATETIME datatypes they are more flexible.

hallie 2010-03-15 01:29:07

+1 A:

The only reason to do it the way you suggest is so that you have a time dimension member name for a business intelligence tool. If that is what you intend to use it for, then it makes sense.

Otherwise, use the built-in time types as others have pointed out.

Sam at TVentures 2010-03-15 01:41:53

ansaurus

tags:

views:

answers:

Database: Storing Dates as Numeric Values

related questions