Date calculation

Hi. I have a table created like this:

CREATE TABLE dv.data (
  vid bytea NOT NULL,
  providerid smallint NOT NULL,
  creationdate timestamp with time zone NOT NULL,
  payload bytea NOT NULL,
  duration smallint NOT NULL
);

There, DURATION is a value in days after which an entry is considered expired (if not 0/Zero). Such entries should get deleted. Technically, I want to find out if the current timestamp NOW() is bigger than CREATIONDATE + DURATION days.

I tried several hacks with INTERVAL, but I can not make the DURATION column to work as a days value to calculate with.

Can someone point me to the right direction?

Maybe I show you what I did in the meantime:

SELECT * FROM dv.data WHERE DURATION > 0 AND CAST(NOW() - CREATIONDATE AS INT) > DURATION * 86400

But it looks not very elegant to me…

Also, neither DURATION nor CREATIONDATE are indexed. This will trigger a full table scan, right?

I think INTERVAL will work correctly for your use case. Check out this small demo:

demo@127.0.0.1:26257/defaultdb> create table a (a timestamptz, d interval);
CREATE TABLE

Time: 2ms total (execution 2ms / network 0ms)

demo@127.0.0.1:26257/defaultdb> insert into a values(now(), '1 day');
INSERT 1

Time: 2ms total (execution 2ms / network 0ms)

demo@127.0.0.1:26257/defaultdb> select a,d,a-d from a;
                   a                  |   d   |              ?column?
--------------------------------------+-------+--------------------------------------
  2021-04-14 14:02:47.601263+00:00:00 | 1 day | 2021-04-13 14:02:47.601263+00:00:00
(1 row)

Time: 1ms total (execution 1ms / network 0ms)

Is this what you are looking for?

And yes, unless you have an index, you will get a full table scan.

Thanks, but I can not change the column type. It is INT and has to be used for calculation. I like to do something like this:

SELECT * FROM dv.data WHERE DURATION > 0 AND NOW() > CREATIONDATE + INTERVAL 'DURATION days'

But I can not use column DURATION like this in INTERVAL.

Oh, I see. How about this? There might be a less hacky way to do it, but I’m not sure:

demo@127.0.0.1:26257/defaultdb> create table a (a timestamptz, d int);
CREATE TABLE

Time: 2ms total (execution 2ms / network 0ms)

demo@127.0.0.1:26257/defaultdb> insert into a values(now(), '1');
INSERT 1

Time: 2ms total (execution 2ms / network 0ms)

demo@127.0.0.1:26257/defaultdb> select a, d, concat(d::text, ' days')::interval, a-concat(d::text, ' days')::interval from a;
                   a                  | d | concat |              ?column?
--------------------------------------+---+--------+--------------------------------------
  2021-04-15 12:40:51.775731+00:00:00 | 1 | 1 day  | 2021-04-14 12:40:51.775731+00:00:00
(1 row)

Time: 1ms total (execution 1ms / network 0ms)

Great. The CONCAT() with implicit casting was the solution. I now do it like this:

SELECT * FROM dv.data WHERE DURATION > 0 AND NOW() > CREATIONDATE + CONCAT(DURATION::text, ' days')::INTERVAL

Thanks!

But I just wonder about the cost of the two variants:
... CAST(NOW() - CREATIONDATE AS INT) > DURATION * 86400
vs
... NOW() > CREATIONDATE + CONCAT(DURATION::text, ' days')::INTERVAL

I think that the second one has more cost because of the concat and string parsing while the numeric one is just substraction and multiplication.

What do you think? Or should I do a new topic on this question?