postgresql – SQL: Subtracting 1 day from a timestamp date

postgresql – SQL: Subtracting 1 day from a timestamp date

Use the INTERVAL type to it. E.g:

--yesterday
SELECT NOW() - INTERVAL 1 DAY;

--Unrelated: PostgreSQL also supports some interesting shortcuts:
SELECT 
    yesterday::TIMESTAMP, 
    tomorrow::TIMESTAMP, 
    allballs::TIME AS aka_midnight;

You can do the following then:

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;

TIPS

Tip 1

You can append multiple operands. E.g.: how to get last day of current month?

SELECT date_trunc(MONTH, CURRENT_DATE) + INTERVAL 1 MONTH - 1 DAY;

Tip 2

You can also create an interval using make_interval function, useful when you need to create it at runtime (not using literals):

SELECT make_interval(days => 10 + 2);
SELECT make_interval(days => 1, hours => 2);
SELECT make_interval(0, 1, 0, 5, 0, 0, 0.0);

More info:

Date/Time Functions and Operators

datatype-datetime (Especial values).

You can cast a TIMESTAMP to a DATE, which allows you to subtract an INTEGER from it.

For instance, to get yesterday:

now()::DATE - 1

So your query will become:

SELECT org_id, date_at::DATE - 1 AS dateat, COUNT(accounts) AS count
FROM sourcetable 
WHERE date_at <= NOW()::DATE - 130
GROUP BY 1, 2

postgresql – SQL: Subtracting 1 day from a timestamp date