Timestamp Math in Postgres

20 05 2009

Postgres allows us to add or subtract integers to a DATE type. So ‘2009-01-01’ + 1 = ‘2009-01-02’. But what we can’t do is add NUMERIC to DATE or add anything to TIMESTAMP. So ‘2009-01-01’ + 1.25 and ‘2009-01-01 12:30:00’ + 1 both fail.

I use this feature a lot on Oracle. So lets add that functionality to Postgres.

CREATE OR REPLACE FUNCTION timestamp_pl_numeric(
timestamp,
numeric
) RETURNS timestamp AS
$$
SELECT $1 + ($2 || ' days')::interval;
$$
LANGUAGE 'sql' IMMUTABLE;


CREATE OR REPLACE FUNCTION timestamp_mi_numeric(
timestamp,
numeric
) RETURNS timestamp AS
$$
SELECT $1 - ($2 || ' days')::interval;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OPERATOR + (
LEFTARG = timestamp,
RIGHTARG = numeric,
PROCEDURE = timestamp_pl_numeric
);


CREATE OPERATOR - (
LEFTARG = timestamp,
RIGHTARG = numeric,
PROCEDURE = timestamp_mi_numeric
);

Advertisements

Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: