Should I store DateTimes as a Long (Ticks) in a database?

Question

Could life be made easier by saving DateTime values as a long instead? There always seem to be problems when working with null DateTime values, whether storing or retrieving - null DateTimes, invalid DateTimes, etc. are always a pain to work with.

Would it be advisable to simply work with a long data type since you can always create a DateTime from the ticks?

Edit: I work with SqlServer and MySql. SqlDateTime is a .net derivation of DateTime. There are differences between all 3 platforms of what a valid DateTime is. How do you handle these differences?

Answer 1

I guess that's down to personal preference. I always work with datetime types and don't have any bother with them.

If you store them as longs though semantically they are no longer dates. If you ever wanted to do a query to select all accounts added on a Friday (say) you would have to jump through several hoops to work that out.

Answer 2

I can't think of any outstanding reason personally, databases support DateTime's themselves, and storing them as a long you may end up shooting yourself in the foot. Let's say you need to be able to run a query "Get me all rows between 3 AM and 6 PM" - if you store them as ticks, you will need to convert back to a DateTime in the database.

Storing them as ticks may impede many other operations, such as grouping, sorting, filtering, etc.

If you have nuances with DateTimes in the database, such as TimeZone, it's strongly recommended to normalize the DateTime to a specific timezone, such as UTC. A lot of the problems that teams face with DateTime in the database is often due to unsanitary input, like not normalizing the TimeZone. Storing it as ticks will still have the same problem.