How to represent the end date in the database?

Question

I am wondering how to represent the value of finite time (positive infinity) in the database.

When we used the 32-bit time value, the obvious answer was the actual 32-bit end of time - something around the year 2038 .
Now, when we use the 64-bit time value, we cannot represent the 64-bit end time in the DATETIME field, since the 64-bit end time is billions of years .

Since SQL Server and Oracle (our two supported platforms) allow up to 9999, I thought we could just pick a “big” date in the future, like 1/1/3000.
However, as customers and our QA department will consider DB values, I want it to be obvious and not look like someone messed up their date arithmetic.

Are we just picking a date and sticking to it?

Answer 1

Use the maximum collation date, which, depending on your DBMS, is likely to be 9999-12-31. You want to do this because date range based queries will quickly become quite complicated if you try to use a “purist” approach, for example, use Null, as suggested by some commentators, or use the forever flag, as suggested by Marc B.

When you use the maximum settlement date to indicate “forever” or “until further notice” in your date ranges, this makes very simple, natural queries. This makes these queries very clear and simple:

• Find the records that are currently valid.
  ... WHERE effective_date <= @PointInTime AND expiry_date >= @PointInTime
• Find entries that apply to the next time interval.
  ... WHERE effective_date <= @StartOfRange AND expiry_date >= @EndOfRange
• Find entries that have overlapping date ranges.
  ... WHERE A.effective_date <= B.expiry_date AND B.effective_date <= A.expiry_date
• Find entries that do not expire.
  ... WHERE expiry_date = @MaxCollatingDate
• Find the time periods when the recording is not valid.
  Good, so it's not easy, but simpler , using maximum match dates for the endpoint. See this question for a good approach.

Using this approach may create some problems for some users who may find “9999-12-31” to be confused in the report or on the screen. If this is a problem for you, then drdwicox's suggestion to use translation in a user-friendly way is good. However, I would suggest that the user interface layer, not the middle tier, be the place to do this, because what may be the most reasonable or acceptable may differ depending on whether you are talking about a report or data entry form and whether the audience is internal or external. For example, in some places you may need a simple space. For others, you may need the word forever. For others, you may need a blank text box with a checkmark that says "Until further notice."

Answer 2

In PostgreSQL, the end of time is "infinity." It also supports "-infection." An infinity value is guaranteed after all other timestamps.

 create table infinite_time ( ts timestamp primary key ); insert into infinite_time values (current_timestamp), ('infinity'); select * from infinite_time order by ts; 2011-11-06 08:16:22.078 infinity 

PostgreSQL supports infinity and -infinity since at least version 8.0.

You can simulate this behavior, at least in part, using the maximum date your dbms supports. But a maximum date may not be the best choice. PostgreSQL's maximum timestamp is some time in the year 294,276, which will surely surprise some people. (I do not like to surprise users.)

 2011-11-06 08:16:21.734 294276-01-01 00:00:00 infinity 

A meaning like this is probably more useful: "9999-12-31 11: 59: 59.999."

 2011-11-06 08:16:21.734 9999-12-31 11:59:59.999 infinity 

This is not quite the maximum value in the year 9999, but the numbers are well aligned. You can wrap this value in an infinity function () and in a CREATE DOMAIN statement. If you create or maintain a database structure from source code, you can use the macro extension to extend INFINITY to a suitable value.

Answer 3

Sometimes we select a date, then set a policy according to which the date should never appear unfiltered. The most common place to enforce this policy is in the middle. We simply filter the results to change the “magic” end date to something more pleasant.

Answer 4

Do not make the date "special." Although it is unlikely that your code will be in 9999, or even in 2 ^ 63-1, look at all the pleasure that was called using "12/31/1999" just a few years ago.

If you need to signal "infinite" or "infinite" time, add the boolean / bit field to report this state.

Answer 5

The concept of “forever” or “until further notice” is an unconditional sentence.

Actually, the theory of relativity says that there is no such thing as null, so you must have any table divided into two parts: one part with rows for which the end date / time of the end is known, and another for the row for which end time is not yet known.

But (for example, with zero) dividing the tables into two parts will also cause a mess in your query. Views may to some extent correspond to read-only details, but updates (or the INSTEAD OF record on your view) will be tough no matter what can affect performance negatively no matter what it is).

Having a zero value means that “end time is not yet known” will make updating “simpler”, but read requests become messy with all the CASE ... or COALESCE ... constructs you need.

Using the theoretically correct solution mentioned by dportas gets messy whenever you want to “extract” a DATE from a DATETIME. If the DATETIME value at hand is “the end (representable) time (billions of years from the moment you say)”, then this is not just the case when you call the DATE extractor function from that DATETIME value, because you also want DATE extractor created the "end of representable DATE" for your case.

In addition, you probably do not want to display the “missing end of time” as the value 9999-12-31 in your user interface. Therefore, if you use the “real value” of the end of time in your database, you are faced with a little work, seeing that this value will not be displayed anywhere in your user interface.

Sorry for not being able to say that there is a way to stay away from all the riots. The only choice you really have is the mess you are in.