Convert integer years or months into days in SQL impala

I have two columns; both have integer values. One Representing years, and the other representing months. My goal is to perform calculations in days (integer), so I have to convert both to calendar days, to achieve that, taking in consideration that we have years with both 365 and 366 days.

Example in pseudo code:

Select Convert(years_int) to days, Convert(months int) to days
from table.


Real Example:
if --> Years = 1 and Months = 12
1) Convert both to days to compare them: Years = 365 days; Months = 365 days

After conversion : (Years = Months) Returns TRUE.

The problem is when we have years = 10 (for example), we must take in account the fact that at least two of them have 366 days. The same with Months - we have 30 and 31 days. So I need to compensate that fact to get the most accurate possible value in days.

Thanks in advance

Solution

From integers to timestamp can be done in PostgreSQL. I do not have impala, but hopefully below script will help you getting this done using impala:

with 
 year as (select 2022 as y union select 2023),
 month as (select generate_series(1,12) as m),
 day as(select generate_series(1,31) as d )
select y,m,d,dt from (
   select 
      y,m,d,
      to_date(ds,'YYYYMMDD')+(((d-1)::char(2))||' day')::interval dt
   from (  select 
              *, 
              y::char(4)|| right('0'||m::char(2),2) || right('0'||0::char(2),2) as ds 
           from year,month,day
        ) x
) y
where extract(year from dt)=y and extract(month from dt)=m
order by dt
;

see: DBFIDDLE

Used functions in this query and, a way, to convert them to imapala (remember I do not use that tool/language/dialect)

function	impala alternative
to_date(a,b)	This will convert the string `a` to a date using the format `b`. Using impala you can use CAST(expression AS type FORMAT pattern)
y::char(4)	Cast y to a char(4), Using imala you can use: CAST(expression AS type)
right(a,b)	Use: right()
`\\`	Use: concat()
generate_series(a,b)	This generates a serie of numbers from a to (an inclusing) b. A SQL altervative is to write `SELECT 1 as x union SELECT 2 union SELECT 3`, which generates the same series as `generate_series(1,3)` in PostgreSQL
extract(year from a)	Get the year from the datetime field `a`, see YEAR()

One special case is this one to_date(ds,'YYYYMMDD')+(((d-1)::char(2))||' day')::interval

This will convert ds (with datatype CHAR(8)) to a date, and then add (using +) a number of days (like: '4 day')
Because I included all days until 31, this will fail in Februari, April, June, September, November because those months do not have 31 days. This is corrected by the WHERE clause in the end (where extract(year from dt)=y and extract(month from dt)=m)