Select only the days of a Timestamp

Question

Select only the days of a Timestamp

Navigation

#1 by (2 votes)

2

I need to select only the days of a subtraction of dates, and if possible convert that number to Integer , is that 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

asked by anonymous 21.02.2018 / 14:53

1 answer

How to create a numerical system? Change the color of a navigation page

score 2 · Accepted Answer

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