Fix year not multiple of day/month in Hierarchical CAggs

[konskov]

Previously all intervals were converted to seconds using "epoch"
with date_part. However, this treats a year as 365.25 days to
account for leap years, leading to the unexpected situation that
a year is not a multiple of a day or a month.

Fixed by treating month-only intervals as multiples of 30 days.

Fixes #5231

[codecov]

Codecov Report

Merging #5255 (5b60343) into main (9133319) will increase coverage by 0.00%.
The diff coverage is 100.00%.

@@           Coverage Diff           @@
##             main    #5255   +/-   ##
=======================================
  Coverage   89.00%   89.00%           
=======================================
  Files         225      225           
  Lines       51838    51842    +4     
=======================================
+ Hits        46138    46144    +6     
+ Misses       5700     5698    -2     

Impacted Files	Coverage Δ
tsl/src/continuous_aggs/create.c	88.11% <100.00%> (+0.04%)	⬆️
src/loader/bgw_message_queue.c	86.36% <0.00%> (-2.85%)	⬇️
src/bgw/scheduler.c	83.88% <0.00%> (-1.28%)	⬇️
tsl/src/bgw_policy/job.c	87.45% <0.00%> (-0.05%)	⬇️
tsl/src/reorder.c	85.71% <0.00%> (+0.21%)	⬆️
tsl/src/fdw/relinfo.c	95.87% <0.00%> (+5.67%)	⬆️

---

Continue to review full report at Codecov.

Legend - Click here to learn more
Δ = absolute <relative> (impact), ø = not affected, ? = missing data
Powered by Codecov. Last update 9133319...5b60343. Read the comment docs.

[github-actions]

@RafiaSabih, @fabriziomello: please review this pull request.

Powered by pull-review

[fabriziomello]

You should also add those new tests to cagg_on_cagg_dist_ht.sql

[konskov]

Added the tests to cagg_on_cagg_dist_ht as well.

[timescale-automation]

Automated backport to 2.9.x not done: cherry-pick failed.

Git status

HEAD detached at origin/2.9.x
You are currently cherry-picking commit 6bc89802.
  (fix conflicts and run "git cherry-pick --continue")
  (use "git cherry-pick --skip" to skip this patch)
  (use "git cherry-pick --abort" to cancel the cherry-pick operation)

Changes to be committed:
	modified:   tsl/src/continuous_aggs/create.c
	modified:   tsl/test/expected/cagg_on_cagg.out
	modified:   tsl/test/expected/cagg_on_cagg_dist_ht.out
	modified:   tsl/test/sql/cagg_on_cagg.sql
	modified:   tsl/test/sql/cagg_on_cagg_dist_ht.sql

Unmerged paths:
  (use "git add <file>..." to mark resolution)
	both modified:   CHANGELOG.md

Job log

[akuzm]

Automated backport to 2.9.x not done: cherry-pick failed.

Git status

HEAD detached at akuzm/2.9.x
You are currently cherry-picking commit 6bc898021.
  (fix conflicts and run "git cherry-pick --continue")
  (use "git cherry-pick --skip" to skip this patch)
  (use "git cherry-pick --abort" to cancel the cherry-pick operation)

Changes to be committed:
	modified:   tsl/src/continuous_aggs/create.c
	modified:   tsl/test/expected/cagg_on_cagg.out
	modified:   tsl/test/expected/cagg_on_cagg_dist_ht.out
	modified:   tsl/test/sql/cagg_on_cagg.sql
	modified:   tsl/test/sql/cagg_on_cagg_dist_ht.sql

Unmerged paths:
  (use "git add <file>..." to mark resolution)
	both modified:   CHANGELOG.md

Untracked files:
  (use "git add <file>..." to include in what will be committed)
	tsl/src/remote/row_by_row_fetcher.c
	tsl/src/remote/row_by_row_fetcher.h
	tsl/test/sql/dist_remote_error-15.sql
	tsl/test/sql/dist_remote_error.text