Restar horas de la función ahora ()

Solución:

Respuesta para timestamp

Necesita comprender la naturaleza de los tipos de datos timestamp (timestamp without time zone) y timestamptz (timestamp with time zone). Si no lo hace, lea esto primero:

• Ignorar las zonas horarias por completo en Rails y PostgreSQL

los AT TIME ZONE constructo transforma un timestamp para timestamptz, que es casi con certeza el movimiento equivocado para tu caso:

where eventtime at time zone 'CET' between '2015-06-16 06:00:00'
                                       and '2015-06-17 06:00:00'

Primero, mata el rendimiento. Aplicando AT TIME ZONE a la columna eventtime hace la expresión no sargable. Postgres no puede usar índices simples en eventtime. Pero incluso sin índice, las expresiones sargables son más baratas. Ajuste los valores de filtro en lugar de manipular cada valor de fila.
usted podría compensar con un índice de expresión coincidente, pero probablemente sea solo un malentendido y sea incorrecto de todos modos.

¿Qué pasa en esa expresión?

1. AT TIME ZONE 'CET' transforma el timestamp valor eventtime para timestamptz agregando la diferencia horaria de su zona horaria actual. Cuando se usa una zona horaria nombre (no es un desplazamiento numérico ni una abreviatura), esto también tiene en cuenta las reglas del horario de verano (horario de verano), por lo que obtiene un desplazamiento diferente para las marcas de tiempo de “invierno”. Básicamente, obtienes la respuesta a la pregunta:

   ¿Cuál es la marca de tiempo UTC correspondiente para la marca de tiempo dada en la zona horaria dada?

   Cuando exhibiendo el resultado para el usuario se formatea como marca de tiempo local con el desplazamiento de tiempo correspondiente a la zona horaria actual de la sesión. (Puede o no ser el mismo que se usa en la expresión).

2. Los literales de cadena en el lado derecho no tienen ningún tipo de datos, por lo que el tipo se deriva de la asignación en la expresión. Ya que eso es timestamptz ahora, ambos son lanzados a timestamptz, asumiendo la zona horaria actual de la sesión.

   ¿Cuál es la marca de tiempo UTC correspondiente para la marca de tiempo dada para la configuración de la zona horaria de la sesión actual?

   El desplazamiento puede variar con las reglas de DST.

Larga historia corta, si tu siempre operar con la misma zona horaria: CET o 'Europe/Berlin' – Lo mismo para las marcas de tiempo actuales, pero no para las históricas o (posiblemente) futuras, simplemente puede cortar el cruft.

los segundo problema con la expresión: BETWEEN casi siempre está mal con timestamp valores. Ver:

• Optimizar BETWEEN declaración de fecha
• Encuentre rangos de fechas superpuestos en PostgreSQL

SELECT date_trunc('hour', eventtime) AS hour
     , count(DISTINCT serialnumber)  AS ct  -- sure you need distinct?
FROM   t_el_eventlog
WHERE  eventtime >= now()::date - interval '18 hours'
AND    eventtime <  now()::date + interval '6 hours'
AND    sourceid  =  44  -- don't quote the numeric literal
GROUP  BY 1
ORDER  BY 1;

now() es la implementación de Postgres del estándar SQL CURRENT_TIMESTAMP. Ambos regresan timestamptz (no timestamp!). Puede usar cualquiera de los dos.
now()::date es equivalente a CURRENT_DATE. Ambos dependen de la configuración de la zona horaria actual.

Deberías tener un índice de la forma:

CREATE INDEX foo ON t_el_eventlog(sourceid, eventtime)

O, para permitir escaneos de solo índice:

CREATE INDEX foo2 ON t_el_eventlog(sourceid, eventtime, serialnumber)

Si opera en diferentes zonas horarias, las cosas se complican y debe usar timestamptz para todo.

Alternativa para timestamptz

Antes de la actualización de la pregunta, parecía que las zonas horarias eran importantes. Cuando se trata de diferentes zonas horarias, “hoy dia” es una dependencia funcional de la zona horaria actual. La gente tiende a olvidar eso.

Para trabajar con la configuración de la zona horaria actual de la sesión, utilice la misma consulta anterior. Si se ejecuta en una zona horaria diferente, los resultados son incorrectos en la actualidad. (También se aplica a lo anterior).

Para garantizar un resultado correcto para una zona horaria determinada (‘Europa / Berlín’ en su caso) independientemente de la configuración de la zona horaria actual de la sesión, utilice esta expresión en su lugar:

    ((now() AT TIME ZONE 'Europe/Berlin')::date - interval '18 hours')
            AT TIME ZONE 'Europe/Berlin'  -- 2nd time to convert back

Tenga en cuenta que el AT TIME ZONE devoluciones de construcción timestamp por timestamptz entrada y viceversa.

Como se mencionó al principio, todos los detalles sangrientos aquí:

• Ignorar las zonas horarias por completo en Rails y PostgreSQL

Tu puedes usar CURRENT_DATE:

 select date_trunc('hour', t_el_eventlog.eventtime at time zone 'CET') as hours,
        count(distinct t_el_eventlog.serialnumber) as count
 from t_el_eventlog
 where eventtime at time zone 'CET' between CURRENT_DATE + interval '6 hour' and
                                            CURRENT_DATE + interval '30 hour' and
       sourceid = '44'
 group by hours
 order by hours asc;

EDITAR:

El comentario de Erwin es sobre el pregunta no esta respuesta. Utilizando between para fecha / hora es una mala idea. Supongo que esto debería repetirse en cada pregunta que haga esto. Pero el problema es que los valores de fecha / hora que son límites entre días se cuentan dos veces.

La lógica correcta es:

 select date_trunc('hour', t_el_eventlog.eventtime at time zone 'CET') as hours,
        count(distinct t_el_eventlog.serialnumber) as count
 from t_el_eventlog
 where eventtime at time zone 'CET' >= CURRENT_DATE + interval '6 hour' and
       eventtime at time zone 'CET' < CURRENT_DATE + interval '30 hour' and
       sourceid = '44'
 group by hours
 order by hours asc;

Tenga en cuenta el “<" para el segundo límite. Aquí hay un buen blog sobre este tema. Aunque Aaron se centra en SQL Server, las advertencias (y algunas de las soluciones) también se aplican a otras bases de datos.

---

---

Utiliza Nuestro Buscador