xxxxxxxxxx
--yesterday
SELECT NOW() - INTERVAL '1 DAY';
--Unrelated: PostgreSQL also supports some interesting shortcuts:
SELECT
'yesterday'::TIMESTAMP,
'tomorrow'::TIMESTAMP,
'allballs'::TIME AS aka_midnight;
xxxxxxxxxx
SELECT
org_id,
count(accounts) AS COUNT,
((date_at) - INTERVAL '1 DAY') AS dateat
FROM
sourcetable
WHERE
date_at <= now() - INTERVAL '130 DAYS'
GROUP BY
org_id,
dateat;
xxxxxxxxxx
a simple way would be to cast the dates into timestamps and take their difference and then extract the DAY part.
if you want real difference
select extract(day from 'DATE_A'::timestamp - 'DATE_B'::timestamp);
if you want absolute difference
select abs(extract(day from 'DATE_A'::timestamp - 'DATE_B'::timestamp));
xxxxxxxxxx
Your calculation is correct for DATE types, but if your values are timestamps, you should probably use EXTRACT (or DATE_PART) to be sure to get only the difference in full days;
EXTRACT(DAY FROM MAX(joindate)-MIN(joindate)) AS DateDifference
xxxxxxxxxx
SELECT make_interval(days => 10 + 2);
SELECT make_interval(days => 1, hours => 2);
SELECT make_interval(0, 1, 0, 5, 0, 0, 0.0);