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

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()

CodePudding user response：

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;

CodePudding user response：

To preserve the leading zero, use to_char():

select to_char(ord_datetime, 'YYYY') as year, 
       to_char(ord_datetime, 'MM') as month 
  from analysis a 
       join orders o on o.ord_an = a.an_id
       join groups g on g.gr_id = a.an_group