I am making a query
sql
on ruby-on-rails
on PostgreSQL
where I check if user input is between hora_entrada
and hora_entrada + (hora_entrada + limite_banco_horas)
and same for hora_fim
, in a certain dia
. However, if the hora_fim + limite_banco_horas
exceeds the hours of that day, the filter no longer brings the pessoas
of the day reported. I need to limit the comparison up to 23:59
, if soma com limite_banco_horas
exceeds this value, 23:59.
Example:
SELECT "dias_trabalhados".*
FROM "dias_trabalhados"
INNER JOIN "presencas" ON "presencas"."id" = "dias_trabalhados"."presenca_id"
INNER JOIN "jornada_trabalhos" ON "jornada_trabalhos"."id" = "dias_trabalhados"."jornada_trabalho_id"
LEFT JOIN "pessoas" ON "pessoas"."id" = "presencas"."pessoa_id"
LEFT JOIN "liberacao_bancos_hora" ON "liberacao_bancos_hora"."pessoa_id" = "pessoas"."id" AND "liberacao_bancos_hora"."data" = '2017-09-22'
WHERE "presencas"."ano" = 2017
AND "presencas"."mes" = 9 AND "dias_trabalhados"."dia" = 22
AND (
(
"liberacao_bancos_hora"."limite" IS NOT NULL
AND (
OR (
("jornada_trabalhos"."hora_entrada" - ("liberacao_bancos_hora"."limite" ||' hours')::interval) >= '20:00'
AND "jornada_trabalhos"."hora_entrada" <= '20:00'
)
OR (
"jornada_trabalhos"."hora_saida" >= '22:00'
AND ("jornada_trabalhos"."hora_saida" + ("liberacao_bancos_hora"."limite" ||' hours')::interval) <= '22:00'
)
)
)
))
In this example, my query checks in the dias_trabalhados
table all pessoas
that have limite_bancos_horas
registered on day 22/09/2017
, between 20:00
and 22:00
.
Assume that% w / w% of% w / w% is w / w%, and w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w ww w w w w w ww w w w w w w.
And then I need to limit my filter in my query so that if hora_saida
exceeds 23:59, I set the comparison time to pessoa
.
No 19:00
is there a function for this, or how do I limit it to the end of the day?