Postgresql – First field from the return table is being eaten up

plpgsqlpostgresqlstored-procedures

Another problem with some PostgreSQL stored procedure code…

Same from this question:

I have this function

drop function uptimes(inicio date, fim date);
create or replace function uptimes(inicio date, fim date) 
    returns table (id integer, entrada timestamp with time zone,
    saida timestamp with time zone, erro boolean, uptime interval) as
$$
begin
    create temporary table localdata (id_local integer,
      entrada_local timestamp with time zone,
      saida_local timestamp with time zone, erro_local boolean) on commit drop ;
    insert into localdata select * from uptime u
    WHERE u.saida>=inicio and u.entrada<=fim order by u.entrada ;

    alter table localdata add uptime_local interval;
    update localdata set saida_local=fim where saida_local>fim;
    update localdata set entrada_local=inicio where entrada_local<inicio;
    update localdata set uptime_local=saida_local-entrada_local;
    return query select * from localdata;
end;
$$
language plpgsql;

The idea is to fetch some data from a table and treat it by replacing the entrada field for any field that is lower than the inicio parameter by the inicio.

But with it, my query returns

  id  |        entrada         |         saida          | erro |     uptime     
------+------------------------+------------------------+------+----------------
 1538 | 2015-10-14 05:02:15-03 | 2015-10-14 05:04:57-03 | f    | 00:02:42
...

and commenting the 

    update localdata set entrada=inicio where entrada<inicio;

they returns

  id  |        entrada         |         saida          | erro |     uptime     
------+------------------------+------------------------+------+----------------
 1537 | 2015-10-12 07:04:15-03 | 2015-10-14 05:01:56-03 | f    | 1 day 21:57:41
 1538 | 2015-10-14 05:02:15-03 | 2015-10-14 05:04:57-03 | f    | 00:02:42
...

What I want is to replace 2015-10-12 07:04:15-03 to 2015-10-14 00:00:00, that is passed as parameter in inicio

Any suggestions? Maybe I passed some logic error. 🙁

Best Answer

The short answer: use date_trunc('day', ...) to set the time part of a timestamp to 00:00:00

The long answer: the whole function is needlessly complicated. There is no need to create a table each time and then modify the columns or even update that table.

This can all be done with a single select statement.

If I understood everything correctly, the query you are looking for is something like this: 

with adjusted as (
  select id, 
         case 
            when entrada < inicio then inicio 
            else entrada 
         end as entrada, 
         case 
           when saida > fim then fim 
           else saida
         end as saida, 
         erro
  from uptime u
  WHERE u.saida >= inicio 
    and u.entrada <= fim 
)
select id, 
       date_trunc('day', entrada) as entrada
       date_trunc('day', saida) as saida
       erro
       saida - entrada as updatime
from adjusted
order by u.entrada;

Note that I "truncated" the columns entrada and saida in the final select which means that the calculation of the uptime does include the time part.

So the whole function can be simplified to:

create or replace function uptimes(inicio date, fim date) 
    returns table (id integer, entrada timestamp with time zone,
                   saida timestamp with time zone, erro boolean, uptime interval) 
as
$$    
    with adjusted as (
      select id, 
             case 
                when entrada < inicio then inicio 
                else entrada 
             end as entrada, 
             case 
               when saida > fim then fim 
               else saida
             end as saida, 
             erro
      from uptime u
      WHERE u.saida >= inicio 
        and u.entrada <= fim 
    )
    select id, 
           date_trunc('day', entrada) as entrada
           date_trunc('day', saida) as saida
           erro
           saida - entrada as updatime
    from adjusted
    order by u.entrada;
$$
language sql;

It is also a good idea to use some kind of prefix for the parameter names, so that they are easier distinguishable from the column names in the query. e.g. p_inicio instead of just inicio

Related Question