What would the schema for the monthly reporting table look like?

Question

PostgreSQL

Hello!

There are several tables in postgresql that contain orders, sales, etc. by day. The dates in these tables are like 2022-02-06.
It is supposed to make a table that will contain the calculated data from several tables by months and also some data will be calculated in the code using formulas and recorded in this table.

For example, there will be values: the number of orders per month, net profit, etc. In the future, depending on these data, users will have different functionality. You will also need to show data for each user in different periods.

What is the best way to make columns for dates in such a table? A separate column for the month and year, or is it better to make 2 columns startDate and endDate in which there will be the start date of the period and the end date (2022-02-01-2022-02-28)?

For the month and year example:

id (uuid) | orderedTotal (int) | soldAmountTotal (decimal) | month (int) | year (int)

For the startDate and endDate example:

id (uuid) | orderedTotal (int) | soldAmountTotal (decimal) | startDate (date) | endDate (date)

Answer 1

create table aggregate_montly (
    user_id bigint not null references users(id),
    month date not null check (date_trunc('month', date_period) = date_period),
    orders_count bigint not null default 0,
    -- прочие предаггрегированные данные
    primary key (user_id, month)
);

Always write the date as the first of the month, the check constraint ensures that you do not mistakenly write the data for February 8 instead of the month.
id thrown out for meaninglessness. Especially uuid.

Answer 2

We have done, year, type of period, period from the beginning of the year. Now the period type is day, week, month, quarter, year.