CODEWITHSUNDEEP
sqldatabasedatetime
matt-in-the-hat
matt-in-the-hat

Reputation: 95

Storing Times and Dates in a Database back end

What are the pros and cons for storing a date in a SQL database as a Date datatype versus storing the year, month, day, etc in separate columns using an integer data type? I noticed that the database back end for an application where I work separates all dates into columns of integers for days, months, years. Why would the developers have used this schema?

Upvotes: 0

Views: 93

Answers (2)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95751

Some cons . . .

  • It's a lot more complicated to keep bad dates out of the database. Feb 29, 2014 is not a valid date, but Feb 29, 2016, is.
  • Date arithmetic is more complicated.
  • It's not unusual for a date or a timestamp to be part of a compound key. Splitting up a date value into multiple columns makes foreign key references much more work.

Upvotes: 1

Lugaru
Lugaru

Reputation: 1460

The main idea of using Date data type is that you can comfortably use DB commands like that: GIVE ME records WHERE date from 01-10-2014 08:30 UTC to 04-10-2014 02:30 UTC. In DB system need to look and compare only one field before returning result otherwise system needs to check several columns before returning the result.

I would not recommend you to use few fields for displaying dates. Use classic. :) Such realisation like in your description can be explained only that app doing some special operations with dates.

Upvotes: 1

Related Questions