Preferred way to store DateTime

Question

We can store Date and Time information in a couple of ways. What is the best approach for storing DateTime information?

Storing Date and Time in 2 separate columns or one column using DateTime?

Can you explain why that approach is better?

^{(Link to MySQL docs for reference, the question is general, not specific to MySQL)
Date and Time types: Date and Time}

Answer 1

Storing the data in a single column is the preferred way, since they are inextricably linked. A point in time is a single piece of information, not two.

A common way of storing date/time data, employed "behind the scenes" by many products, is by converting it into a decimal value where the "date" is the integer portion of the decimal value, and the "time" is the fractional value. So, 1900-01-01 00:00:00 is stored as 0.0 and September 20th, 2016 9:34:00 is stored as 42631.39861. 42631 is the number of days since 1900-01-01. .39861 is the portion of time elapsed since midnight. Don't use a decimal type directly to do this, use an explicit date/time type; my point here is just an illustration.

Storing the data in two separate columns means you'll need to combine both column values any time you want to see if a given point in time is earlier or later than the stored value.

If you store the values separately, you'll invariably run into "bugs" that are difficult to detect. Take for instance the following:

IF OBJECT_ID('tempdb..#DT') IS NOT NULL
DROP TABLE #DT;
CREATE TABLE #DT
(
    dt_value DATETIME NOT NULL
    , d_value DATE NOT NULL
    , t_value TIME(0) NOT NULL
);


DECLARE @d DATETIME = '2016-09-20 09:34:00';

INSERT INTO #DT (dt_value, d_value, t_value)
SELECT @d, CONVERT(DATE, @d), CONVERT(TIME(0), @d);

SET @d = '2016-09-20 11:34:00';

INSERT INTO #DT (dt_value, d_value, t_value)
SELECT @d, CONVERT(DATE, @d), CONVERT(TIME(0), @d);

/* show all rows with a date after 2016-07-01 11:00 am */
SELECT *
FROM #DT dt
WHERE dt.dt_value >= '2016-07-01 11:00:00';

/* show all rows with a date after 2016-07-01 11:00 am */
SELECT *
FROM #DT dt
WHERE dt.d_value >= CONVERT(DATE, '2016-07-01')
    AND dt.t_value >= CONVERT(TIME(0), '11:00:00');

In the above code, we're creating a test table, populating it with two values, then performing a simple query against that data. The first SELECT returns both rows, however the second SELECT only returns a single row, which may not be the desired outcome:

The correct way to filter a date/time range where the values are in discrete columns, as pointed out by @ypercube in comments, is:

WHERE dt.d_value > CONVERT(DATE, '2016-07-01') /* note there is no time component here */
    OR (
        dt.d_value = CONVERT(DATE, '2016-07-01') 
        AND dt.t_value >= CONVERT(TIME(0), '11:00:00')
    )

If you need the time component separated for analysis purposes, you could consider adding a calculated, persisted, column for the time portion of the value:

ALTER TABLE #DT
ADD dt_value_time AS CONVERT(TIME(0), dt_value) PERSISTED;

SELECT *
FROM #dt;

The persisted column could then be indexed allowing for fast sorts, etc, by time-of-day.

If you are considering splitting the date and time into two fields for display purposes, you should realize that formatting should be done at the client, not the server.

Answer 2

I'm going to provide a dissenting opinion to the other answers.

If both the date and time components are required together i.e. an entry is invalid if it contains one but not the other (or is NULL in one but not the other), then storing it in a single column makes sense for the reasons given in other answers.

However, it may be the case that one or both components are individually optional. In that case it would be incorrect to store it in a single column. Doing so would force you to represent NULL values in an arbitrary way e.g. storing the time as 00:00:00.

Here are a couple of examples:

• You are recording vehicle journeys for mileage tax deductions. Knowing the exact time of the journey would be useful but if an employee didn't note it down and has forgotten, the date should still be recorded by itself (required date, optional time).

• You are conducting a survey to find out what time people eat their lunch, and you ask participants to complete a form with their a sample of their lunch times, including dates. Some don't bother filling in the date, and you don't want to discard the data since it's the times you really care about (optional date, required time).

See this related question for alternative approaches.

Answer 3

I'll always prefer to store that as a single column unless there is some specific business/application demand. Below are my points -

• Extracting time from timestamp is not a problem
• Why to add extra column just for time if we can store both together
• To avoid add Date and Time each time whenever you are querying.

Answer 4

In SQL Server it is best to store DataTime as one field. If you create an index on DataTime column it can be used as Date search and as DateTime search. Therefore if you need to limit all records that exist for the specific date, you can still use the index without having to do anything special. If you need to query for time portion you will not be able to use the same index and therefore if you have a business case where you care more about the time of the day than DateTime, you should store it separately as you will need to create an index on it and improve performance.

Answer 5

Indeed, that's a pity there is no standard cross-DBMS type for this (like INT and VARCHAR are for integers and string values). The 2 cross-database approaches I have met so far are using VARCHAR/CHAR columns to store DataTime values as strings formatted according to the ISO 8601 (more convenient, human-readable) standard and using BIGINT to store them as POSIX timestamps (stored more efficiently, faster, easier to manipulate mathematically).

Answer 6

After reading a bunch of stuff, UTC Unix time in BIGINT seems to be the optimal solution. TZDB timesone ID in VARCHAR for time zone storage if needed. A few arguments:

1. TIMESTAMP and DATETIME do a bunch of gimmicky conversions in the background that seem to be complex and not clear. Server switches from local time to UTC or to server time and back, sometimes, or not. A bunch of hidden overhead for every function.

2. BIGINT (8kb) is at least as light or lighter than DECIMAL required for xxxxxx.xxxxxx format storage, which is practically stored as two INTs + something by MySQL. And it's enough to store centuries ahead.

3. Pretty much all major programming languages have libraries of standard functions to work with Unix time.

4. Math operations with BIGINT should be as fast or faster than anything else on any hardware pretty much.

Of course all of the above is relevant for big, international projects. For something small, going with the default format of the chosen framework seems to be good enough.