Recover larger and shorter hours conditioned on dates

Question

Inside a table containing the columns data_inicio, data_fim, hora_inicio, hora_fim need to identify within a set of records the following occurrences: the lowest start date, the highest end date, the lowest start time and the highest end time.

The image below lists a set of records in which I want to check the lowest start_date, highest end_date, lowest start_time, highest end_time:

Based on the records shown in the image above, the lowest start date is simple to achieve, simply use the operator MIN. The same occurs to get the highest end date. I only need to use the operator MAX to get it. To calculate the highest start time and the lowest end time I cannot use the same approach as can be seen in the sql result below.

SQL:

select min(data_inicio), max(data_fim), min(hora_inicio), max(hora_fim)
from minha_tabela
where codigo = 2

Upshot:

To calculate, both the shorter start time and the longer end time need to take into account more than simply the column itself. Simply use the operator MIN and MAX does not solve. I need to take into account also the columns of dates. Looking at the first image we can notice that the lowest start time is '1000', as the lowest date is '04/05'. The biggest final time is '1900', as the biggest final date is '20/05'.

Is there any way to implement this type of query simply through SQL? If so, how?

This example is in SQL Fiddle.

Answer 1

First concatenate the date and time columns to transform the string into date:

to_date(data_inicio || ' ' || hora_inicio,'dd-mm-yy hh24mi'))

After that just take the smallest and longest date:

min(to_date(data_inicio || ' ' || hora_inicio,'dd-mm-yy hh24mi')),
max(to_date(data_fim || ' ' || hora_fim,'dd-mm-yy hh24mi'))

And to separate again in just date and/or time:

to_char(min_inicio,'dd/mm/yyyy') menor_data,
to_char(min_inicio,'hh24:mi:ss') menor_hora,
to_char(max_fim,'dd/mm/yyyy') maior_data,
to_char(max_fim,'hh24:mi:ss') maior_hora

See example working on SQL Fiddle.

Answer 2

Another alternative, a little more verbose, to solve this problem is the use of a temporary table. Initially we recovered this table only with the minimum start date and maximum end date information.

with limites as (
   select min(data_inicio) data_minima, max(data_fim) data_maxima
   from minha_tabela
   where codigo = 2
)

Next we join this table with the table searched to recover the minimum time and the maximum time. This is necessary due to the conditionality of time to date fields as described in the question. This junction is necessary, as we can have more than an equal date for both the minimum value and the maximum value. As we can see in the image below, we have three values for May 3rd and two for May 30th:

To the minute start time we would have:

(
  select min(hora_inicio) from minha_tabela
  inner join limites on limites.data_minima = data_inicio
  where codigo = 2
) as hora_minima

To the maximum end time we would have:

(
  select max(hora_fim) from minha_tabela
  inner join limites on limites.data_maxima = data_fim
  where codigo = 2
) as hora_maxima

SQL with the solution complete would look like this:

with limites as (
    select min(data_inicio) data_minima, max(data_fim) data_maxima
    from minha_tabela
    where codigo = 2
)
select (
  select data_minima from limites
) as data_minima,
(
  select data_maxima from limites
) as data_maxima,
(
  select min(hora_inicio) from minha_tabela
  inner join limites on limites.data_minima = data_inicio
  where codigo = 2
) as hora_minima,
(
  select max(hora_fim) from minha_tabela
  inner join limites on limites.data_maxima = data_fim
  where codigo = 2
) as hora_maxima
from dual

Upshot:

Answer 3

If I understand the problem, only grouping by code can solve:

select min(data_inicio), max(data_fim), min(hora_inicio), max(hora_fim)
from minha_tabela
where codigo = 2
group by codigo