0

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. :(

0

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

  • Guy... the inicio in when entrada < inicio then inicio shows a syntax error of column "inicio" does not exist – HufflepuffBR Nov 19 '15 at 15:54
  • @HufflepuffBR: the "stand alone" version of the statement can't work as it relies on the parameters that you pass into the function. I just put it there to make it clear that a single statement is enough. If you want to test it without the function you need to replace the parameters with some actual values. – a_horse_with_no_name Nov 19 '15 at 17:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.