CODEWITHSUNDEEP
sqldatetimesemanticsabstraction
kojiro
kojiro

Reputation: 77089

What use are SQL dates without date functions?

I've worked with various ORMs and database abstractions designed to make it easy to work with multiple databases, both relational and not. The more comprehensive solutions will usually give you access to some date functions that boil down to actual SQL (or whatever, in the case of non-SQL dbs). On the other hand, many of these abstractions don't provide direct access to SQL functions and you lose the ability to deal with dates directly. Instead, you're expected to use the upper-level language (PHP, Python, whatever) to do your date-wrangling, and finally only insert, select, what-have-you the formatted date.

So my question is this: if the SQL server never gets to do anything with the date itself, am I better off just using an int and putting epoch timestamps in it, or is there additional value to the database server "knowing" it's a date?

Upvotes: 3

Views: 145

Answers (2)

Kaf
Kaf

Reputation: 33809

Other than what @Oded said, if you never ever use any date related functions, Still there are some issues;

  • At the moment, you cannot store epoch timestamp in milliseconds into an INT field (overflows).

  • Timestamp without milliseconds will overflow INT on Tue Jan 19 2038 @ 03:14:08 GMT+0000 (GMT) as it will be greater than 2147483647.

  • BUT, Integer takes 4 bytes and Datetime takes 8 bytes. You are better off 4 bytes if you are within above two limitations.

Upvotes: 1

Oded
Oded

Reputation: 498972

If you are using dates, store them as dates.

Not only does this make it easier to translate between the database and application, but when you need to do anything based on the dates (and you will, otherwise why have dates stored at all?).

That is, when you need to sort or query using the dates, you will not need to go trough special effort to re-convert to dates.

Upvotes: 3

Related Questions