One of the handy things Oracle does with dates is allow manipulation with standard arithmetic. Want tomorrow's date? Add one. Want a week ago? Subtract seven. Postgres does something close with its INTERVAL syntax, under the explanation that we don't necessarily want to make assumptions about what is being added to a date or timestamp. But Postgres has a big secret in the fact we can arbitrarily create operators to get the behavior we desire.
No Place for Beginners or Sensitive Hearts
So what happens if we happen to know that the developers and applications targeting the database prefer the Oracle approach, and that integers should always increment or decrement a date value? Let's start with something basic like adding a integers to a date. What happens if we try this with a standard Postgres installation:
-- This works
SELECT CURRENT_DATE + INTERVAL '1 day';
?column?
------------
2017-07-24
-- This also works
SELECT CURRENT_DATE + 1;
?column?
------------
2017-07-24
Wait a minute! That isn't supposed to work, is it? Kinda. The plus and minus operators just happen to have overloads which are only valid for dates. Once we start getting a bit more complicated, things immediately cease being so convenient.
When Sentiment is Left to Chance
Let's attempt the same test with a TIMESTAMP type instead:
-- Things are fine if we cheat a bit
SELECT (CURRENT_DATE + 1)::TIMESTAMP;
timestamp
---------------------
2017-07-24 00:00:00
-- But try direct addition...
SELECT CURRENT_DATE::TIMESTAMP + 1;
ERROR: operator does not exist: timestamp without time zone + integer
LINE 1: SELECT CURRENT_DATE::TIMESTAMP + 1;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Ouch! It turns out we can skirt around the issue if we're already working with a date that we can cast to a timestamp later. This is actually fairly contrived, as we're likely handling timestamp columns or other values that are not compatible with the existing date operators.
No Need to Ask, He's a Smooth Operator
No more cheating. It's one thing to repeatedly cast values and twist them into ridiculous contortions to reach our desired result. Why not have Postgres do all the hard work instead? To create an operator, we need two things:
- A function the operator can invoke using the left and right arguments of the calculation.
- An operator definition that binds the argument types to the chosen operator.
Let's start with the function an operator declaration now:
CREATE OR REPLACE FUNCTION ts_plus_num(tzMod TIMESTAMP, nDays INTEGER)
RETURNS TIMESTAMP AS
$$
SELECT tzMod + (nDays || ' days')::INTERVAL;;
$$ LANGUAGE SQL STABLE;
CREATE OPERATOR + (
PROCEDURE = ts_plus_num,
LEFTARG = TIMESTAMP,
RIGHTARG = INTEGER
);
Now what happens if we retry our addition?
SELECT CURRENT_DATE::TIMESTAMP + 1;
timestamp
---------------------
2017-07-24 00:00:00
It's magic! If only we were done…
Face to Face, Each Classic Case
Unfortunately things aren't quite so simple. In Postgres, TIMESTAMP and TIMESTAMP WITH TIME ZONE are separate types. That means this will still fail:
SELECT CURRENT_DATE::TIMESTAMPTZ + 1;
ERROR: operator does not exist: timestamp with time zone + integer
LINE 1: SELECT CURRENT_DATE::TIMESTAMPTZ + 1;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Postgres does however, allow functional overloads. So we can simply change the types of our parameters and re-declare the function and operator definitions like this:
CREATE OR REPLACE FUNCTION ts_plus_num(tzMod TIMESTAMPTZ, nDays INTEGER)
RETURNS TIMESTAMPTZ AS
$$
SELECT tzMod + (nDays || ' days')::INTERVAL;;
$$ LANGUAGE SQL STABLE;
CREATE OPERATOR + (
PROCEDURE = ts_plus_num,
LEFTARG = TIMESTAMPTZ,
RIGHTARG = INTEGER
);
Note that in this second function and operator, the only things we changed were the argument types. Instead of TIMESTAMP, we used the shorthand TIMESTAMPTZ for a Postgres timestamp with time zone. Let's repeat our attempt at adding to a true timestamp:
SELECT CURRENT_DATE::TIMESTAMPTZ + 1;
?column?
------------------------
2017-07-24 00:00:00-05
And suddenly it works again. It's a minor inconvenience to require two functions for a single operation, but precision demands certain concessions. Two types, two functions; it's not really a big deal, considering we're essentially rewriting how Postgres handles addition in specific scenarios.
We Shadow Box and Double Cross
Oracle also allows adding fractions to increment by hours, minutes, or seconds. What happens if we try this right now?
SELECT CURRENT_DATE::TIMESTAMP + 0.5;
ERROR: operator does not exist: timestamp without time zone + numeric
LINE 1: SELECT CURRENT_DATE::TIMESTAMP + 0.5;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Well, back to the drawing board.
It turns out we can get around this a couple of ways. We could either keep adding functional overloads for every type we want to handle, or we could be a bit more creative. The NUMERIC type works for both integer and decimal use cases, so we can drop our current functions and redefine them with NUMERIC instead.
DROP FUNCTION ts_plus_num(TIMESTAMP, INTEGER) CASCADE;
DROP FUNCTION ts_plus_num(TIMESTAMPTZ, INTEGER) CASCADE;
CREATE OR REPLACE FUNCTION ts_plus_num(tzMod TIMESTAMP, nDays NUMERIC)
RETURNS TIMESTAMP AS
$$
SELECT tzMod + (nDays || ' days')::INTERVAL;;
$$ LANGUAGE SQL STABLE;
CREATE OPERATOR + (
PROCEDURE = ts_plus_num,
LEFTARG = TIMESTAMP,
RIGHTARG = NUMERIC
);
CREATE OR REPLACE FUNCTION ts_plus_num(tzMod TIMESTAMPTZ, nDays NUMERIC)
RETURNS TIMESTAMPTZ AS
$$
SELECT tzMod + (nDays || ' days')::INTERVAL;;
$$ LANGUAGE SQL STABLE;
CREATE OPERATOR + (
PROCEDURE = ts_plus_num,
LEFTARG = TIMESTAMPTZ,
RIGHTARG = NUMERIC
);
Now we have two functions and two operators using NUMERIC instead of INTEGER. With these in place, all of our previous tests should complete successfully.
SELECT CURRENT_DATE::TIMESTAMP + 1;
?column?
---------------------
2017-07-24 00:00:00
SELECT CURRENT_DATE::TIMESTAMPTZ + 1;
?column?
------------------------
2017-07-23 00:00:00-05
SELECT CURRENT_DATE::TIMESTAMP + 0.5;
?column?
---------------------
2017-07-23 12:00:00
SELECT CURRENT_DATE::TIMESTAMPTZ + 0.5;
?column?
------------------------
2017-07-23 12:00:00-05
And voila, we're finished!
No Place to be Ending But Somewhere to Start
Well, almost finished. We only overloaded the (+) operator, meaning we can only add values to dates. Technically we could add negative values, but that's not how queries will be written, and it's somewhat convoluted in any case. Yet as we've already demonstrated, implementing a couple more operator definitions is extremely simple. At this point, we merely repeat the above process using the (-) operator to produce a complete set.
In the end, this makes Postgres itself act like an extensible programming language. A C++ or Python programmer would be perfectly comfortable adding types to Postgres, and defining how those types operate within its confines. Isn't that what we expect from a database? To store our data in such a way the types are inherently compatible with basic operations? Any database engine that allows defining types should also expose mechanisms for interacting with those types. Anything less cripples user-defined types, and proves for incomplete type handling.
Many databases reflect the dubious honor of supporting CREATE TYPE without CREATE OPERATOR, but Postgres is not among them. Even among those that provide this functionality, sometimes built-in types are specifically barred from overloading as a safety measure. Postgres carries neither of these restrictions, so only our imaginations limit the full potential of operator overloading. This time we made Postgres a bit more compatible with Oracle, but that's merely the tip of the iceberg.
Then again, what else can we expect from Postgres?
He move in space with minimum waste and maximum joy