Stream: general

Topic: Fiscal period format

James A Rosen (Jan 20 2025 at 04:56):

I need to express "fiscal {month,quarter,year}" compactly, e.g. in a database column or HTTP endpoint. I've done some literature research. Looker uses fiscal_month_offset, which seems as good a standard as any. But there's nothing on storage/transfer formats. I'm considering these:

# [u-...] inspired by Temporal API
2024[u-fmo=-7]    = 2023-06-01 - 2024-05-31
2025Q2[u-fmo=+3]  = 2025-07-01 - 2026-09-30
2040M10[u-fmo=-5] = 2040-05-01 - 2040-05-31

# I picked ~ somewhat arbitrarily as a delimiter
FY2024~-7   = 2023-06-01 - 2024-05-31
FY2025Q2~+3 = 2025-07-01 - 2026-09-30
2040M10~-5  = 2040-05-01 - 2040-05-31

# The {...} sets off the month offset visually
2024{-7}    = 2023-06-01 - 2024-05-31
2025Q2{3}   = 2025-07-01 - 2026-09-30
2040M10{-5} = 2040-05-01 - 2040-05-31

Does anyone have a favorite? Another idea?

Nabeel S (Jan 20 2025 at 05:02):

I'm tempted to say: just store the start and end dates in your database, and use code to convert to and from the fiscal year format.

James A Rosen (Jan 20 2025 at 18:15):

I certainly could do that. One key downside is size.

I can store fiscal_year and fiscal_offset in two smallints for 4 bytes. I can store fiscal_year, fiscal_year_start, and fiscal_year_end in one smallint and two Dates for 10 bytes. On a table with 100,000,000 records, that's an extra 600MB at minimum -- more, depending on how well I play column Tetris. (I could omit fiscal_year, but by not storing it, I can't partition on it.)

I'm going to have to convert one way or the other in queries that need to compare fiscal and calendar periods.

If I store (fiscal_year, fiscal_month_offset), I need to convert to query by calendar year

select *
from events
where fiscal_year >= calendar_year_to_fiscal(?, ?)
and fiscal_year <= calendar_year_to_fiscal(?, ?);

Whereas if I store (calendar_start, calendar_end), I need to convert to query by fiscal year:

select *
from events
where calendar_start >= fiscal_year_start(?, ?)
and calendar_end <= fiscal_year_end(?, ?);

Nabeel S (Jan 20 2025 at 22:03):

If regular dates will make your life easier, and if the only downside of two dates is size, I would say double check if that's really a downside at all:

• 10 bytes might feel like a lot, but a) what's the size of the rest of the data in the column? and b) Is 100M rows really something you're going to have?
• DATE takes 3 bytes vs. the 5 bytes of DATETIME, but again I wonder if that consideration is over-optimization.

James A Rosen (Jan 20 2025 at 23:51):

100M rows really something you're going to have?

Probably within a few months. Definitely within 12 months.

DATE takes 3 bytes vs. the 5 bytes of DATETIME

On Postgres, timestamp is 8B, timestamp with time zone is 8B, and date is 4B.

Nabeel S (Jan 21 2025 at 00:42):

I still don't think saving 300MB is going to be your main concern on a 100GB database :-)

James A Rosen (Jan 21 2025 at 01:27):

Fair. Thank you for taking the time to make a suggestion. I appreciate the assist.

Last updated: Apr 03 2025 at 23:38 UTC