In PostgreSQL, select fiscal year month format as a date column

1
votes

I have a Postgres database that has a column for the month an event occurred in. This is formatted as a TEXT column type and has the fiscal year preceding the month name as shown below:

fiscal
-------
20-Jul
20-Aug
20-Jan
20-Apr
20-Feb

Say the fiscal in this case starts in Apr 2019 (and refers to the 2019-2020 fiscal year), how would a select query show this as a DATE column type? (assuming just using the first day of the month as the day)

So like below

fiscal    real_date
--------------------
20-Jul    2019-Jul-1
20-Aug    2019-Aug-1
20-Jan    2020-Jan-1
20-Apr    2019-Apr-1
20-Feb    2020-Feb-1
2
sqlpostgresqlbusiness-intelligence
Do you perhaps mean 20-Jul should become 2020-Jul-01? Not 2019. – Andrew
@Andrew, no, because fiscal year "2020" means the year run from April 2019 to March 2020. So because it's July of "fiscal year 2020", than means the actual month is July of 2019. – Luke
Sorry, reading fail... – Andrew

2 Answers

1
votes

This looks horrible, but it seems to work so far...

select

case when extract('month' from (to_date(<your column>,'yy-mon'))) > 4
  then (to_date(<your column>,'yy-mon')) + interval '-1' year
else (to_date(<your column>,'yy-mon'))
              end
from
<your table>

Fiddle example

2
votes

I think this implements the logic you want:

select (case when extract(month from to_date(substr(fm, 4, 3), 'MON')) >= 4
             then to_date('20' || fm, 'YYYY-MON')
             else to_date('20' || fm, 'YYYY-MON') + interval '1 year'
        end)
from (values ('20-JUL'), ('19-FEB')) v(fm);

Postgres happens to allow you to convert a month to a date. You get a value way far back in time. But I don't think there are any issues because the purpose is only to convert the string to a number.