Select only the days of a Timestamp

Asked

Viewed 43 times

2

I need to select only the days of a date subtraction, and if possible convert this number to Integer, is this possible?

    SELECT 
    (select count(numeroserie) as  porcentagemTotal from public.lotesretornosuprimento where laudo = 'Garantia' and dataretornogarantia is not null),
    (select count(defeito) as porcentagemDef from public.lotesretornosuprimento t4 where t4.defeito = t2.defeito and t4.suprimento = t2.suprimento and laudo = 'Garantia' and dataretornogarantia is not null group by defeito, suprimento),
    (select count(fornecedor) as  porcentagemForn from public.lotesretornosuprimento t1 where t1.fornecedor = t2.fornecedor and laudo = 'Garantia' and dataretornogarantia is not null group by fornecedor),
    (select count(suprimento) as porcentagemSup from public.lotesretornosuprimento t3 where t3.suprimento = t2.suprimento and laudo = 'Garantia' and dataretornogarantia is not null group by suprimento),
    //Nessa linha
    (select SUM(dataretornogarantia - datasaidagarantia) as dataTotal from public.lotesretornosuprimento t5 where t5.suprimento = t2.suprimento and laudo = 'Garantia' and dataretornogarantia is not null group by suprimento),
    numeroserie, 
    suprimento, 
    fornecedor,  
    defeito,
    datasaidagarantia,
    dataretornogarantia
    FROM 
    public.lotesretornosuprimento t2 
    where 
    laudo = 'Garantia' and dataretornogarantia is not null
    order by fornecedor,suprimento,defeito,numeroserie
postgresql

1 answer

2


You can use DATE_PART(), where the structure would be DATE_PART('day', dataFim - dataIni).

SELECT 
    (select count(numeroserie) as  porcentagemTotal from public.lotesretornosuprimento where laudo = 'Garantia' and dataretornogarantia is not null),
    (select count(defeito) as porcentagemDef from public.lotesretornosuprimento t4 where t4.defeito = t2.defeito and t4.suprimento = t2.suprimento and laudo = 'Garantia' and dataretornogarantia is not null group by defeito, suprimento),
    (select count(fornecedor) as  porcentagemForn from public.lotesretornosuprimento t1 where t1.fornecedor = t2.fornecedor and laudo = 'Garantia' and dataretornogarantia is not null group by fornecedor),
    (select count(suprimento) as porcentagemSup from public.lotesretornosuprimento t3 where t3.suprimento = t2.suprimento and laudo = 'Garantia' and dataretornogarantia is not null group by suprimento),
    //Nessa linha
    (select SUM(DATE_PART('day', dataretornogarantia - datasaidagarantia)) as dataTotal from public.lotesretornosuprimento t5 where t5.suprimento = t2.suprimento and laudo = 'Garantia' and dataretornogarantia is not null group by suprimento),
    numeroserie, 
    suprimento, 
    fornecedor,  
    defeito,
    datasaidagarantia,
    dataretornogarantia
FROM public.lotesretornosuprimento t2 
where laudo = 'Garantia'
  and dataretornogarantia is not null
order by fornecedor, suprimento, defeito, numeroserie

  • Dai to convert to integer just do the Cast()?

  • I did some tests with the return and did not need explicit conversion to the SUM(), worked well

  • 1

    Thanks for the Jersey

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.