PostgreSQL compare time between two hours, and if you exceed the hours of the day, limit until 23:59

0

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?

    
asked by anonymous 09.11.2017 / 17:35

0 answers