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:
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.
This question is related to
sql
postgresql
casting
Use the INTERVAL type to it. E.g:
--yesterday
SELECT NOW() - INTERVAL '1 DAY';
--Unrelated to the question, but PostgreSQL also supports some shortcuts:
SELECT 'yesterday'::TIMESTAMP, 'tomorrow'::TIMESTAMP, 'allballs'::TIME;
Then you can do the following on your query:
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;
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';
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);
Source: Stackoverflow.com