7

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?

Infinite Recursion
  • 6,511
  • 28
  • 39
  • 51
IAbstract
  • 19,551
  • 15
  • 98
  • 146
  • 2
    datetime is there for a reason: use it. – Mitch Wheat Mar 04 '11 at 15:22
  • DateTime is there for convenience, IMO. I am simply considering saving DateTime values as ticks in a database. Retrieving a long from the database gives me what I need to create a DateTime from the number of ticks. I don't see a problem with it. – IAbstract Mar 04 '11 at 15:24
  • 2
    How are any of the alleged problems with `datetime` solved by using `long`? – Kirk Woll Mar 04 '11 at 15:24
  • Ticks do provide greater precision and will work across multiple dbms – Trubs Jun 03 '20 at 04:50

4 Answers4

10

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.

cusimar9
  • 5,185
  • 4
  • 24
  • 30
5

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.

vcsjones
  • 138,677
  • 31
  • 291
  • 286
3

In some cases, yes. Sql stores datetimes at a lower precision than DateTime. This means that a value persisted and retrieved may have a slightly different DateTime value from the original object, which might cause some issues with ordering or comparison.

mcintyre321
  • 12,996
  • 8
  • 66
  • 103
2

No, use a datetime column.

There always seem to be problems when working with null DateTime values

If your column is nullable, and you for some reason have difficulties when the type of the column is datetime you will also have problems when the type of the column is long.

... invalid DateTimes

How do you get invalid datetimes into a datetime column? One of purposes of using the datetime column to begin with is that sort of validation happens before the data is allowed to be inserted. Much more likely you'll get invalid datetimes when using a naked long.

Finally, using a long means that viewing your database via simple SQL (select * from table) will produce illegible results.

Kirk Woll
  • 76,112
  • 22
  • 180
  • 195
  • there appear to be differences between what SqlServer, MySql, and .Net consider to be invalid DateTime values... – IAbstract Mar 04 '11 at 15:33
  • @IAbstract, indeed -- it is always helpful to be specific in your question. Are you asking about a particular database? – Kirk Woll Mar 04 '11 at 15:33
  • At the time I had this question I was pulling data from SQL Server & MySQL. I don't recall all specifics but DateTime was in play from all 3 platforms (.Net incl.) Thus storing the DateTime as a long would be "universal". I believe I had the authority and access to modify only one of the databases that would have made my life easier on the .Net side of things (as an intermediary). Otherwise, I wasn't concerned about legibility of long values in a *DateTime* column as I could, if necessary, use a date function/proc to show the date according to the respective platform. – IAbstract Dec 14 '17 at 13:10