Are there any good reasons for keeping date and time in separate columns?

Question

I'm trying to understand our software vendor's decision to keep date and time in separate columns. For example, when the row was created or updated. Both time and date are DateTime columns. We are using SQL Server 2005.

The database holds our ERP system's data and I believe the largest tables contain about ~3 million rows. Most of the tables are roughly between 100 000 - 1 000 000 rows.

I would personally by default choose a single DateTime for a single timestamp. This would allow easier time difference calculations and the date and time parts could be easily extracted from the timestamp. It would also consume less space.

Is separating date and time bad practice or is there something very brilliant in this design I don't understand?

Answer 1

My guess is that it's a legacy support thing (i.e back in version 1 of their software, they had date and time in seperate fields and they've been forced to support it because it always worked that way).

That said, there's nothing brilliant about seperating date and time, so there's nothing you're missing.

(The other option is that your ERP system developers didn't know that a datetime field can store a date and a time, but far more likely is that they had a business requirement to make it work the same as prior versions.)

Answer 2

Generally, it depends on the usage. If many queries are joining sub-elements together then consider instead storing them together and then take on the overhead of having to split them apart on those rarer occasions. Of course, splitting is sometimes more complex (or impossible): consider a person_full_name attribute from which you need to extract person_family_name. That said, down casting a temporal value is trivial so I would generally prefer storing date and time together.

Also consider storing both together AND apart, with constraints (and perhaps triggers) to ensure they don't go out of sync: this way you take on the hit of splitting/joining at the point of update rather than at the time of querying the data.

Answer 3

I can't see a lot of use for this in an operational system. In an analytic system you might want a separate date dimension with a grain of 'days' so the date links to reporting rollups of date, and perhaps a 'time' column if you want to do analysis by time-of-day for some reason.

You can also present date and time as calculated columns based on a core datetime column.

Answer 4

To add to ConcernedOfTunbridgeWe's comment, there is a querying performance advantage if you have a DateDimension and a TimeDimension table. Consider the fact table has both a dateDimensionKey and TimeDimensionKey. If you want to find the number of something between 8 am to 5 pm, you can simply do

SELECT COUNT(1) FROM FactTable f Inner Join TimeDimension t on f.TimeDimensionKey = t.TimeDimension WHERE t.Hour BETWEEN 8 AND 17.

If you have index added to the TimeDimensionKey, you will only need an Index Seek for the result on your fact table to search data

Without TimeDimension table, you will have to do something like the following,

SELECT COUNT(1) FROM FactTable f WHERE DatePart(mintue, f.Date) BETWEEN 8 AND 17

This will probably requires a table scan because the database engine need to calculate the result for every rows before it can figure out what data can be return.