SQL: restar 1 día de una fecha de marca de tiempo
Estoy usando Datagrip para Postgresql. Tengo una tabla con un campo de fecha en formato de marca de tiempo (ex: 2016-11-01 00:00:00). Quiero poder:
- aplicar un operador matemático para restar 1 día
- filtrarlo según una ventana de tiempo de hoy-130 días
- mostrarlo sin la parte hh/mm/ss del sello (31-10-2016)
Consulta inicial actual:
select org_id, count(accounts) as count, ((date_at) - 1) as dateat
from sourcetable
where date_at <= now() - 130
group by org_id, dateat
La ((date_at)-1)cláusula de la línea 1 da como resultado:
[42883] ERROR: el operador no existe: marca de tiempo sin zona horaria - entero Sugerencia: Ningún operador coincide con el nombre proporcionado y los tipos de argumento. Es posible que necesites agregar conversiones de tipos explícitas. Posición: 69
La now()cláusula genera un mensaje similar:
[42883] ERROR: el operador no existe: marca de tiempo con zona horaria - entero Sugerencia: Ningún operador coincide con el nombre proporcionado y los tipos de argumento. Es posible que necesites agregar conversiones de tipos explícitas. Posición: ...
Las guías en línea para escribir conversiones son singularmente inútiles. Se agradece el aporte.
Utilice el INTERVALtipo para ello. P.ej:
--yesterday
SELECT NOW() - INTERVAL '1 DAY';
--Unrelated: PostgreSQL also supports some interesting shortcuts:
SELECT
'yesterday'::TIMESTAMP,
'tomorrow'::TIMESTAMP,
'allballs'::TIME AS aka_midnight;
Puedes hacer lo siguiente entonces:
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;
CONSEJOS
Consejo 1
Puede agregar varios operandos. Por ejemplo: ¿cómo llegar al último día del mes actual?
SELECT date_trunc('MONTH', CURRENT_DATE) + INTERVAL '1 MONTH - 1 DAY';
Consejo 2
También puedes crear un intervalo usando make_intervalla función, útil cuando necesitas crearlo en tiempo de ejecución (sin usar literales):
SELECT make_interval(days => 10 + 2);
SELECT make_interval(days => 1, hours => 2);
SELECT make_interval(0, 1, 0, 5, 0, 0, 0.0);
Más información:
Funciones y operadores de fecha/hora
tipo de datos-fecha y hora (valores especiales) .
Puedes convertir a TIMESTAMPa a DATE, lo que te permite restarle an INTEGER.
Por ejemplo, para obtener ayer:
now()::DATE - 1
Entonces tu consulta será:
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
