SQL converting time difference to int

Question

I type select (signup_date - CURRENT_DATE) from table

I get a result like { "days": -76, "hours": -3 }

How do I transform it to int? So that I can compare it to other numbers. For example, if signup_date - CURRENT_DATE > 5, then do something

Also, what format is that? { "days": -76, "hours": -3 }

"How do I transform it to int? So that I can compare it to other numbers." -- Do it the other way. Cast your integers to intervals. You can compare intervals with intervals in Postgres. Something like `( || ' seconds')::interval` will do such a cast, if the number represents seconds. But you can use any other unit as well. — sticky bit, Mar 21 '21 at 22:31
Declare the exact data type of your input, what your desired integer number shall represent, and your version of Postgres. — Erwin Brandstetter, Mar 21 '21 at 22:41

Erwin Brandstetter · Answer 1 · 2021-03-21T22:50:21.463

If signup_date as type date like the name suggests, the expression would indeed yield an integer signifying the number of days (date - date → integer):

SELECT signup_date - CURRENT_DATE;

But if signup_date is type timestamp or timestamptz, you get an interval.

Then you can extract all kinds of integer numbers with extract(). You just have to define what you need exactly.

See:

score 0 · Answer 2 · answered Mar 21 '21 at 23:40

If you want to select rows, then I would recommend:

select . . .
from table t
where signup_date > current_date + interval '5 day';

It seems really weird to have future signup dates, so I suspect you want:

select . . .
from table t
where signup_date > current_date - interval '5 day';

If you want to assign a column a value you can use case. For instance:

select (case when signup_date > current-date - interval '5 day'
             then 'recent' else 'old'
        end)
from table;

SQL converting time difference to int

2 Answers2