Search code examples
postgresqlcasting

how to convert month float 1.0 to 01 after extracting and casting in postgresql?


in postgresql i extracted month from datetime column, and it shows as 1.0 not 01, 2.0 and not 02.

SELECT 

cast(extract(year from ord_datetime) as int ) as year, 
extract(month from ord_datetime) as month 

FROM analysis a inner join orders o on o.ord_an = a.an_id
inner join groups g on g.gr_id = a.an_group

expected answer is:

year    month    group    sum    
-----------------------------
2018    01       1        16     
2018    02       1        31     
2018    03       1        38     
2018    04       1        53 

my answer is:

year    ord_datetime           month    gr_id    ord_id    an_id    
----------------------------------------------------------------
2018    2018-09-13 00:00:00    9.0      8        1         9        
2019    2019-05-04 00:00:00    5.0      10       2         5   

month columns should be 09,05

i need to extract month from ord_datetime as-is , perhaps without using extract()


Solution

  • The Postgresql EXTRACT function is documented as returning a double-precision float. You're already using CAST to convert the year to an integer. Do the same with month.

    SELECT 
    
    cast(extract(year from ord_datetime) as int ) as year, 
    cast(extract(month from ord_datetime) as int ) as month 
    
    FROM analysis a inner join orders o on o.ord_an = a.an_id
    inner join groups g on g.gr_id = a.an_group;