You've probably been in the same situation: there's a class of objects which have a specific date before* which they should not be considered "active." But how should you represent "active since forever?" PostgreSQL or Python, for example, have very definite but different earliest possible datetime values.

Let's say I have an alarm limit which applies to historical readings since year 2000:

    'limit': 5,
    'active_since': datetime.datetime(2000, 1, 1),

This is very clear and easy to work with. But how would you create a limit which is active at all times before year 2000? Neither PostgreSQL nor Python has any way to create a datetime value arbitrarily far in the past. For example, in Python:

>>> import datetime
>>> datetime.datetime(-1, 1, 1)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ValueError: year -1 is out of range

Since I can't have a datetime object representing infinitely in the past, the start of the universe, or even 1 BCE, what should I do instead?

Some possibilities:

  1. Overload the language's equivalent of NULL.
  2. Just use the earliest possible datetime which can be represented in all parts of the system.
  3. Add a boolean field.
  4. Add an "active before" field.

However, all of these have their problems:


    'limit': 5,
    'active_since': None,
  • Risks semantic overloading. It's typically used to represent "unknown," "undefined" or "not applicable," and is already problematic for being unclear. Taking it to mean specifically "earliest possible datetime" means that you might need some other way to represent other special meanings. And you'd have to be careful that this special datetime value is documented, since it's likely that you will have other nullable ones which do not mean "earliest possible datetime."
  • Complicates checks such as having to order with NULLs first or having to if value is None: ….

Earliest representable datetime:

    'limit': 5,
    'active_since': datetime.datetime(1, 1, 1),
  • Should make for the simplest naive implementation. for example SELECT statements can just check against the current datetime or other columns without worrying about any special values.
  • Makes testing awkward, especially when combining languages where the earliest representable datetime in the languages are not the same. For example, I insert into PostgreSQL using Django, and my Django REST Framework serializer translates None into '-4713-01-01' when inserting. datetime.datetime(-4713, 1, 1) is out of range, so presumably Django translates that back into datetime.datetime(1, 1, 1). Now my test specifies None, my database says 4713 BCE, and the response says 1-01-01. Very confusing. Instead, min/maxing the datetime across all the languages and frameworks in use (which might also include JavaScript and more) should give a more sensible pipeline of what humans would consider identical values, but it's still arbitrary.
  • It could change between versions of the same software. This is not purely academic - PostgreSQL 11's max timestamp is less than PostgreSQL 8's.
  • It might not be far enough in the past. For example, astronomical or historical data may go back to long before 1 CE, never mind 1970 CE. However, at this point a datetime field is unusable anyway.
  • There might not be an easy way to find out what the actual earliest value is. Python 3 for example has no constant for this value. It does have datetime.MINYEAR, but I don't know if it can represent datetime.datetime(datetime.MINYEAR, 1, 1) for every time zone.

Separate boolean field:

    'limit': 5,
    'active_since': None,
    'active_since_forever': True,
  • At least as complicated to implement checks as with NULL.
  • Have to avoid or define the meaning of having a non-NULL datetime but the boolean field set to indicate that it's the earliest possible datetime.
  • Reading the schema or code it may not be obvious whether or how this field is related to the datetime field if they are part of a larger object.
  • Normalizing the above feels really awkward - there'll just be a table with {ID: 1, since_forever: False}, and{ID: 2, since_forever: True}.

"Active before" field:

    'limit': 5,
    'active_before': datetime.datetime(2000, 1, 1),
    'active_since': None,
  • Has to be kept equal to the minimum "active after" value.
  • Have to avoid or define what it means to have more than one of these being non-NULL.
  • Have to avoid or define what it means if both "active before" and "active after" are specified.

Are there any other options which do not have big drawbacks?

* This discussion should apply equally to upper limits, but it's easier to write it without constantly duplicating this detail.

  • I re-read the whole question three times and I still don't know what are you trying to say. Can you provide some more examples of what exactly are you trying to represent? – Euphoric Jan 13 at 21:36
  • Could you clarify whether the events and alarm limits are all stored in the database or if they come from different sources? – Blrfl Jan 13 at 22:11
  • It entirely depends on how you plan to use they value, what computations and calculations you make with it. If it is only ever compared to the current time to check validity, then I would recommend simply using whatever the current time is when you assign the value. – Dave M Jan 13 at 22:37
  • @Blrfl The database is just for illustration purposes. The problem is generic across languages, frameworks and storage engines. In practice, this question was motivated by a system where events and alarms are stored in the database and they come from manual and automated sources. – l0b0 Jan 13 at 22:38

Whatever your RDBMS' mintime is.

The RDBMS is (almost) always the source of truth for such things, and tends to be the piece of the system that is least adaptable to playing nice with others. And since (almost) all of your others systems interact with the RDBMS (either directly or indirectly), they'll support that choice. If the RDBMS' mintime changes for some reason, then they'll provide some standardish mechanism for migration.

Depending on what your system is doing, unix epoch or 1/1/1900 are popular choices.

I've seen null used in some cases, and it does have a lot of the limitations you mention. I tend to avoid it due to general SQL null behavior badness, but depending on what you need, it can also be a viable option - especially if you expect most of your data to be null. That case decreases the likelihood of people forgetting to handle it properly.

  • My RDBMS's minimum time is not representable in Python. – l0b0 Jan 13 at 22:01
  • 1
    @l0b0 consider mapping that value to the lowest representable Python value in your SELECT statement – Morgen Jan 13 at 23:55
  • That would mean that API responses would not agree with database contents, which I hope and expect would not be acceptable in a production system. – l0b0 Jan 14 at 1:41
  • @l0b0 - if your python bindings already refuse to return mintime from the db, you already have a scenario that is "not acceptable in a production system". – Telastyn Jan 14 at 3:03
  • @Telastyn In my case (and probably for most production systems), since I don't need anything close to the database mintime it's fine. Lots of production systems use PostgreSQL with Python. And this is not a bug in the Python bindings, it's a limitation of Python's built-in datetime. – l0b0 Jan 14 at 3:09

(From a comment)

The problem is generic across languages, frameworks and storage engines.

It is, but if the problem is made so generic that the number of languages/frameworks/whatever pushes the number of permutations to infinity, there's no good answer. At some point, you have to bound it with something practical, such as your example:

In practice, this question was motivated by a system where events and alarms are stored in the database and they come from manual and automated sources.

This is something limited enough to work with. You have three problems:

First is how to represent the data. Pretty much every language supports some concept of null, which makes it a choice that isn't likely to cause technical problems. It's easy to write range classes that understand that a lower-bound null means before all time and upper-bound null means after all time. I don't see the risk of semantic overloading being as much of an issue as you do since null typically denotes no value and is appropriate when you're trying to express no beginning or no end. If not applicable (no value and there shouldn't be one) or unknown (there might be a value but we don't know for sure) are required as additional states, that's a completely separate can of worms. The way to avoid semantic problems is to document exactly what the values in your structure mean. That way, nobody has to guess and anyone writing software to use them can write it to your spec.

Second is how to store the data in the database. The best thing to do here is take advantage of what the database offers natively for representing these things.* I'll explain why in a minute. Based on what's described in the question, you might have tables that look like this (I'll use PostgreSQL):

CREATE TABLE foo_limit (limit_value INTEGER, times TSTZRANGE);
                    foo INTEGER);

When applications have your standard structure in hand and want to populate the foo_limit table, there's nothing wrong with requiring that nulls in the lower bound be added to a range as -infinity and nulls in the upper bound be added as infinity. Again, the key is documenting your system's expectations:

INSERT INTO foo_limit (limit_value, times) VALUES
    (4, TSTZRANGE('-infinity',  '1990-01-01', '[)')),
    (5, TSTZRANGE('1990-01-01', '2000-01-01', '[)')),
    (6, TSTZRANGE('2000-01-01', 'infinity',   '[)'));

You could, in fact, put less burden on your application developers by centralizing that business logic in a stored function that understands the rules and produces the right range value for the database. The equivalent to the above might look like this:

        lower_bound TIMESTAMP WITH TIME ZONE,
        upper_bound TIMESTAMP WITH TIME ZONE
AS $$
        COALESCE(lower_bound, '-infinity'::TIMESTAMP WITH TIME ZONE),
        COALESCE(upper_bound, 'infinity'::TIMESTAMP WITH TIME ZONE),
$$ LANGUAGE plpgsql;

INSERT INTO foo_limit (limit_value, times) VALUES
    (4, system_standard_range(NULL,         '1990-01-01')),
    (5, system_standard_range('1990-01-01', '2000-01-01')),
    (6, system_standard_range('2000-01-01', NULL        ));

This makes applications completely ignorant that the database sees those values differently. You'd also want companion functions to pull application-standard bounds out of the ranges so they can be returned to clients in that format as a pair of columns.

Third is where you work with the data. The reason I suggest using the database's native representations for these things is that DBMSes (relational or otherwise) are are awfully good at dealing with their contents quickly and efficiently. This lets you work with the data close to its source. I've run into lots of people who've fallen into the trap of thinking they can only use the database as a high-capacity flat file and wind up querying large amounts of data and whittling it down in the client. Not only is that a waste of compute cycles, it also re-invents one of the wheels already invented by the DBMS.

Designing the database schema for the kind of questions you're asking with queries takes advantage of what the DBMS has to offer. For example, "get all the events where column foo is above the limit for that date and show me the limit as well" boils down to a join:

SELECT event.*, foo_limit.limit_value AS upper_limit
FROM event
JOIN foo_limit on event.happened @> foo_limit.times
WHERE event.foo > foo_limit.limit_value;

That gets your application the answer it's really after with no code to write, and you'll notice that at no time does it have to care about how the time ranges are represented internally.

*PostgreSQL has had -infinity and infinity values that test before and after all timestamps for pretty close to 20 years. They work just fine with the built-in range types and operators.

  • system_standard_range(NULL, '1990-01-01') runs into the same problem as the "active before" field. Otherwise a cool solution. – l0b0 Jan 14 at 1:36
  • @l0b0 I'm not seeing the problem. The range [-infinity, 1990-01-01) represents all time before 1990, [1990-01-01, infinity) represents all time afterward and [1990-01-01, 2000-01-01) represents a specific span. Using a null as a substitute for infinity on either end, what can't you represent? – Blrfl Jan 14 at 12:44
  • The problems are spelled out in my question, the main one being that the end of the range '[h]as to be kept equal to the minimum "active after" value.' – l0b0 Jan 14 at 19:56
  • @l0b0 The question asks for a way to represent unbounded time ranges across languages without running afoul of differing minimum/maximum representable values in each, which this does. If you have an additional constraint that multiple records be contiguous and non-overlapping, this model works just fine. How those constraints would be defined and implemented is a different topic and would be fodder for a different question. – Blrfl Jan 14 at 21:13

If it is really a problem (you business logic is that peculiar about dates for a given object and you need special values not offered by the type), you could define an enum like

enum RichDate { Unknown, AsSpecified, SinceForever, TillNever, 
    WhileAttending, WhateverCustomerWants, ToBeDetermined, ... }

Only for AsSpecified will the separate date value have meaning. The enum can be extended at will so whatever special "date" you may need, you can add it any time.

Then again, if this looks like a solution to your problem you may want to reconsider your model.

Edit: The presented problem boils down to trying to avoid magic numbers or more generally in-band signaling. The enum does just that, in an extensible way.

  • No, an enum would be a much more general solution than I need. – l0b0 Jan 14 at 1:41

There is a saying that goes "all models are wrong, some are useful". Ultimately, the goal of software is to provide a "useful" model on top of the underlying system. It really doesn't matter how you represent a concept internally, as long as you have a decent abstraction that is not tied to any specific framework.

Define the interface the way you like it and document its contract. Then any implementation-specific limitations or quirks are hidden behind the interface.

  • 1
    While true, this isn't particularly helpful. I have already implemented a solution and documented it to my colleagues' satisfaction, but I had hoped someone had come up with something better than a hack. – l0b0 Jan 14 at 5:21
  • Why do you consider your current solution a hack? It's only a hack when you know of a better solution but have to take a shortcut due to external constraints. You've already thought through the problem and described several solutions, if you haven't already picked an obvious winner, it's usually an indication that any one is good enough. – casablanca Jan 16 at 4:16

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.