SQL: Subtracting 1 day from a timestamp date

Question

I am using Datagrip for Postgresql. I have a table with a date field in timestamp format (ex: 2016-11-01 00:00:00). I want to be able to:

1. apply a mathematical operator to subtract 1 day
2. filter it based on a time window of today-130 days
3. display it without the hh/mm/ss part of the stamp (2016-10-31)

Current starting query:

select org_id, count(accounts) as count, ((date_at) - 1) as dateat 
from sourcetable 
where  date_at <= now() - 130
group by org_id, dateat

The ((date_at)-1) clause on line 1 results in:

[42883] ERROR: operator does not exist: timestamp without time zone - integer Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 69

The now() clause spawns a similar message:

[42883] ERROR: operator does not exist: timestamp with time zone - integer Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: ...

Online guides to type casts are singularly unhelpful. Input is appreciated.

Solution 1

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).

Solution 2

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

Comments

[wim]

For those who were wondering, "allballs" is a literal for midnight, because it looks like 00:00:00.

[vikasing]

Another tip: If you have stored the days in a db column (lets say num_of_days), you can use SELECT NOW() - INTERVAL '1 DAY' * num_of_days;