How to represent end-of-time in a database?

Question

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

When we were using a 32-bit time value, the obvious answer was the actual 32-bit end of time - something near the year 2038.
Now that we're using a 64-bit time value, we can't represent the 64-bit end of time in a DATETIME field, since 64-bit end of time is billions of years from now.

Since SQL Server and Oracle (our two supported platforms) both allow years up to 9999, I was thinking that we could just pick some "big" future date like 1/1/3000.
However, since customers and our QA department will both be looking at the DB values, I want it to be obvious and not appear like someone messed up their date arithmetic.

Do we just pick a date and stick to it?

Answer 1

Use the max collating date, which, depending on your DBMS, is likely going to be 9999-12-31. You want to do this because queries based on date ranges will quickly become miserably complex if you try to take a "purist" approach like using Null, as suggested by some commenters or using a forever flag, as suggested by Marc B.

When you use max collating date to mean "forever" or "until further notice" in your date ranges, it makes for very simple, natural queries. It makes these kind of queries very clear and simple:

• Find me records that are in effect as of a given point in time.
  ... WHERE effective_date <= @PointInTime AND expiry_date >= @PointInTime
• Find me records that are in effect over the following time range.
  ... WHERE effective_date <= @StartOfRange AND expiry_date >= @EndOfRange
• Find me records that have overlapping date ranges.
  ... WHERE A.effective_date <= B.expiry_date AND B.effective_date <= A.expiry_date
• Find me records that have no expiry.
  ... WHERE expiry_date = @MaxCollatingDate
• Find me time periods where no record is in effect.
  OK, so this one isn't simple, but it's simpler using max collating dates for the end point. See: this question for a good approach.

Using this approach can create a bit of an issue for some users, who might find "9999-12-31" to be confusing in a report or on a screen. If this is going to be a problem for you then drdwicox's suggestion of using a translation to a user-friendly value is good. However, I would suggest that the user interface layer, not the middle tier, is the place to do this, since what may be the most sensible or palatable may differ, depending on whether you are talking about a report or a data entry form and whether the audience is internal or external. For example, some places what you might want is a simple blank. Others you might want the word "forever". Others you may want an empty text box with a check box that says "Until Further Notice".

Answer 2

In PostgreSQL, the end of time is 'infinity'. It also supports '-infinity'. The value 'infinity' is guaranteed to be later than 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 has supported 'infinity' and '-infinity' since at least version 8.0.

You can mimic this behavior, in part at least, by using the maximum date your dbms supports. But the maximum date might not be the best choice. PostgreSQL's maximum timestamp is some time in the year 294,276, which is sure to surprise some people. (I don't like to surprise users.)

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

A value 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

That's not quite the maximum value in the year 9999, but the digits align nicely. You can wrap that value in an infinity() function and in a CREATE DOMAIN statement. If you build or maintain your database structure from source code, you can use macro expansion to expand INFINITY to a suitable value.

Answer 3

We sometimes pick a date, then establish a policy that the date must never appear unfiltered. The most common place to enforce that policy is in the middle tier. We just filter the results to change the "magic" end-of-time date to something more palatable.

Answer 4

Representing the notion of "until eternity" or "until further notice" is an iffy proposition.

Relational theory proper says that there is no such thing as null, so you're obliged to have whatever table it is split in two: one part with the rows for which the end date/end time is known, and another for the rows for which the end time is not yet known.

But (like having a null) splitting the tables in two will make a mess of your query writing too. Views can somewhat accommodate the read-only parts, but updates (or writing the INSTEAD OF on your view) will be tough no matter what, and likely to affect performance negatively no matter what at that).

Having the null represent "end time not yet known" will make updating a bit "easier", but the read queries get messy with all the CASE ... or COALESCE ... constructs you'll need.

Using the theoretically correct solution mentioned by dportas gets messy in all those cases where you want to "extract" a DATE from a DATETIME. If the DATETIME value at hand is "the end of (representable) time (billions of years from now as you say)", then this is not just a simple case of invoking the DATE extractor function on that DATETIME value, because you'd also want that DATE extractor to produce the "end of representable DATEs" for your case.

Plus, you probably do not want to show "absent end of time" as being a value 9999-12-31 in your user interface. So if you use the "real value" of the end of time in your database, you're facing a bit of work seeing to it that that value won't appear in your UI anywhere.

Sorry for not being able to say that there's a way to stay out of all messes. The only choice you really have is which mess to end up in.