18

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

  • 3
    That largely depends on which database system you are using. For what its worth: Oracle chose to do this as one column (as a DATETIME datatype), in which case, using their built in support is certainly going to be superior than storing that information in 2 columns as NUMBER datatypes (even if you only need 1 part for a given query... the date or the time). – Kris Johnston Sep 20 '16 at 14:37
  • 5
    For SQL Server one case where splitting out can be preferred is for grouping by date. A stream aggregate will be able to be used without a sort for the composite index on date,time with group by date but not for an index on datetime with group by cast(datetime as date) even though it would supply the desired order. – Martin Smith Sep 20 '16 at 19:13
  • 1
    Do note that any math on Time values requires knowing the date and timezone - e.g. the distance between two times depends on wether that day contains a DST event, some days have 23 or 25 hours, and leap seconds also exist. – Peteris Sep 21 '16 at 6:31
23

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:

enter image description here

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;

enter image description here

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.

11

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.

  • In RFC 3339 there is a convention for recording "unknown local offset". I don't think it quite covers the use case of "unknown time", but it's close. The next section "unqualified local time" is even closer, but again it's not quite enough. – geneorama Jan 4 at 19:45
  • Yes, I'm staring down the barrel of refactoring my schema because of this right now. Take a car rental situation. To pick a car up from a rental company - the company needs to be open; so you specify a date and time for the pickup. However, many have keydrop boxes; so you drop off after hours. So if the location is closed on Sundays; there is a drop-off date; but not a time. Storing a 0 value (e.g. 12am) won't work because some location are open until midnight, which is a valid value in other situations. – Reece Oct 10 at 16:50
5

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.
  • 1
    @a_horse_with_no_name has a point here. I think the "Extracting timestamp from datetimestamp is not a problem" should be rephrased as "Extracting time from timestamp is not a problem". "Timestamp" usually means both date and time (and usually timezone). – ypercubeᵀᴹ Feb 25 '18 at 9:32
  • Yes, agree @ypercubeᵀᴹ. Timestamp usually means both date and time. I explicitly mentioned DateTimeStamp word, so anyone can understand that we are talking about date and time both. But you are also correct. Modified the answer. – Ashwini Mohan Mar 11 '18 at 6:10
3

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.

1

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).

  • 2
    Yes there is: timestamp that's what the SQL standard defines. Storing timestamps as strings is a very bad advice – a_horse_with_no_name Feb 25 '18 at 8:49
0

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.

  • 2
    "do a bunch of gimmicky conversions in the background that seem to be ... not clear" - which DBMS are you talking about? For a timestamp column no "gimmicky conversions" happen (at the database layer) and for timestamp with time zone this is well documented and explained in the manuals (at least for Oracle and Postgres) – a_horse_with_no_name Feb 25 '18 at 8:52
  • 1
    "Pretty much all major programming languages have libraries of standard functions to work with Unix time." And yet you throw out all the libraries and functions about dates, datetimes and timestamps that SQL / DBMS have, with your choice of using bigint ... – ypercubeᵀᴹ Feb 25 '18 at 9:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.