How to store date-time to optimize search in DB and reduce data duplication?
Asked
Active
Viewed 83 times
0
-
I'm thinking in separate date and time and put index to each. since the time is manage hh:mm:ss and date is like MM/DD/YYYY – Hugo H. Cardet Aug 21 '15 at 15:02
-
Why not just use a datetime datatype? That is what they are for. – Sean Lange Aug 21 '15 at 15:11
1 Answers
0
Searching through integers are very efficient in SQL. You could store the date as a UNIX timestamp in an int column. Depending on what language you are accessing the database with, there are probably many functions you can use to convert a human readable date or a date object to a Unix timestamp which you can use in your query. More info here .
Jay S.
- 1,318
- 11
- 29
-
There are a number of challenges with this type of approach. You have to constantly convert dates to the UNIX time which is a pain. The datetime datatype is really the best option in sql server. – Sean Lange Aug 21 '15 at 15:12
-
Sean Lange is not wrong. It all depends on which kind of database and which language is accessing the database. – Jay S. Aug 21 '15 at 15:16
-
Well this is tagged with sql server so we have to assume that is correct. The biggest challenge with the UNIX timestamp is doing datemath is painful and ends up being nonSARGable because you end up wrapping the column in functions to turn it back into a datetime value. – Sean Lange Aug 21 '15 at 15:19