Fossil SCM

Bug fixes on boundary cases the the byday and byweek reports.

drh 2026-06-30 18:50 UTC trunk
Commit 407e6c63ae06eb2c230a2bbeda07add7f2f692802c9f4a494c8a40d9fe2fdf64
Parent d530589c3fd7766…
1 file changed +23 -15
~ src/statrep.c
M src/statrep.c
+23 -15
--- src/statrep.c
+++ src/statrep.c
@@ -681,10 +681,12 @@
681681
 int rowCount = 0;
682682
 int total;
683683
 int iCurrentWeek; /* Current week number */
684684
 double rNowFraction = 0.0; /* Fraction of current week that has
685685
 ** passed */
686
+ double rStartOfYear = 0.0; /* Start of year */
687
+ double rEndOfYear = 0.0; /* End of the year */
686688
687689
 stats_report_init_view();
688690
 style_submenu_sql("y", "Year:",
689691
 "WITH RECURSIVE a(b) AS ("
690692
 " SELECT substr(date('now'),1,4) UNION ALL"
@@ -695,13 +697,16 @@
695697
 style_submenu_checkbox("sa", "Show-All", 0, 0);
696698
 if( zYear==0 || strlen(zYear)!=4 ){
697699
 zYear = db_text("1970","SELECT substr(date('now'),1,4);");
698700
 }
699701
 zLimit = db_text("1971-01-01",
700
- "SELECT min(date('%q-01-01','+1 year','-1 day'),date())",
702
+ "SELECT min(date('%q-01-01','+1 year','-1 day','weekday 6'),"
703
+ "date('now','weekday 6'))",
701704
 zYear
702705
 );
706
+ rStartOfYear = db_double(0.0,"SELECT julianday('%q-01-01')",zYear);
707
+ rEndOfYear = db_double(0.0,"SELECT julianday('%q-12-31 23:59:59.999')",zYear);
703708
 db_multi_exec(
704709
 "CREATE TEMP TABLE wkdata(wk,n);\n"
705710
 "WITH RECURSIVE c(wkn) AS (\n"
706711
 " VALUES(0)\n"
707712
 " UNION ALL\n"
@@ -709,18 +714,20 @@
709714
 " WHERE date('%q-01-01',format('%%+d days',wkn*7+7))<=%Q\n"
710715
 ")\n"
711716
 "INSERT INTO wkdata(wk,n)\n"
712717
 " SELECT c.wkn, coalesce(x.n,0)\n"
713718
 " FROM c LEFT JOIN (\n"
714
- " SELECT 0+strftime('%%W',mtime) AS w,\n"
715
- " count(*) AS n \n"
716
- " FROM v_reports\n"
717
- " WHERE mtime BETWEEN julianday('%q-01-01 00:00:00')\n"
718
- " AND julianday(%Q)\n"
719
- " AND ifnull(coalesce(euser,user,'')=%Q,1)\n"
720
- " GROUP BY w) AS x ON c.wkn=x.w;\n",
719
+ " SELECT 0+strftime('%%W',min(max(%!.16g,mtime),%!.16g)) AS w,\n"
720
+ " count(*) AS n \n"
721
+ " FROM v_reports\n"
722
+ " WHERE mtime BETWEEN julianday('%q-01-01','weekday 6','-6 days')\n"
723
+ " AND julianday('%q 23:59:59.999','weekday 6')\n"
724
+ " AND ifnull(coalesce(euser,user,'')=%Q,1)\n"
725
+ " GROUP BY w\n"
726
+ " ) AS x ON c.wkn=x.w;\n",
721727
 zYear, zLimit,
728
+ rStartOfYear, rEndOfYear,
722729
 zYear, zLimit, zUserName
723730
 );
724731
 cgi_printf("<br>\n");
725732
 @ <h1>Timeline events (%h(stats_report_label_for_type()))
726733
 @ for the calendar weeks of %h(zYear)
@@ -844,25 +851,26 @@
844851
 zYear
845852
 );
846853
 db_multi_exec(
847854
 "CREATE TEMP TABLE daydata(dn,isodate,dow,n);\n"
848855
 "WITH RECURSIVE c(daynum, isodate, dow) AS (\n"
849
- " VALUES(0,'%q-01-01',0+strftime('%%w','%q-01-01'))\n"
856
+ " VALUES(0,date('%q-01-01'),0+strftime('%%w','%q-01-01'))\n"
850857
 " UNION ALL\n"
851858
 " SELECT daynum+1, date(isodate,'+1 day'), (dow+1)%%7\n"
852859
 " FROM c\n"
853860
 " WHERE isodate<%Q\n"
854861
 ")\n"
855862
 "INSERT INTO daydata(dn,isodate,dow,n)\n"
856863
 " SELECT c.daynum, c.isodate, c.dow, coalesce(x.n,0)\n"
857864
 " FROM c LEFT JOIN (\n"
858
- " SELECT date(mtime) AS edate, count(*) AS n\n"
859
- " FROM v_reports\n"
860
- " WHERE mtime BETWEEN julianday('%q-01-01 00:00:00')\n"
861
- " AND julianday(%Q)\n"
862
- " AND ifnull(coalesce(euser,user,'')=%Q,1)\n"
863
- " GROUP BY edate) AS x ON c.isodate=x.edate;\n",
865
+ " SELECT date(mtime) AS edate, count(*) AS n\n"
866
+ " FROM v_reports\n"
867
+ " WHERE mtime BETWEEN julianday('%q-01-01')\n"
868
+ " AND julianday('%q 23:59:59.999')\n"
869
+ " AND ifnull(coalesce(euser,user,'')=%Q,1)\n"
870
+ " GROUP BY edate\n"
871
+ " ) AS x ON c.isodate=x.edate;\n",
864872
 zYear, zYear,
865873
 zLimit,
866874
 zYear,
867875
 zLimit,
868876
 zUserName
869877
--- src/statrep.c
+++ src/statrep.c
@@ -681,10 +681,12 @@
681 int rowCount = 0;
682 int total;
683 int iCurrentWeek; /* Current week number */
684 double rNowFraction = 0.0; /* Fraction of current week that has
685 ** passed */
 
 
686
687 stats_report_init_view();
688 style_submenu_sql("y", "Year:",
689 "WITH RECURSIVE a(b) AS ("
690 " SELECT substr(date('now'),1,4) UNION ALL"
@@ -695,13 +697,16 @@
695 style_submenu_checkbox("sa", "Show-All", 0, 0);
696 if( zYear==0 || strlen(zYear)!=4 ){
697 zYear = db_text("1970","SELECT substr(date('now'),1,4);");
698 }
699 zLimit = db_text("1971-01-01",
700 "SELECT min(date('%q-01-01','+1 year','-1 day'),date())",
 
701 zYear
702 );
 
 
703 db_multi_exec(
704 "CREATE TEMP TABLE wkdata(wk,n);\n"
705 "WITH RECURSIVE c(wkn) AS (\n"
706 " VALUES(0)\n"
707 " UNION ALL\n"
@@ -709,18 +714,20 @@
709 " WHERE date('%q-01-01',format('%%+d days',wkn*7+7))<=%Q\n"
710 ")\n"
711 "INSERT INTO wkdata(wk,n)\n"
712 " SELECT c.wkn, coalesce(x.n,0)\n"
713 " FROM c LEFT JOIN (\n"
714 " SELECT 0+strftime('%%W',mtime) AS w,\n"
715 " count(*) AS n \n"
716 " FROM v_reports\n"
717 " WHERE mtime BETWEEN julianday('%q-01-01 00:00:00')\n"
718 " AND julianday(%Q)\n"
719 " AND ifnull(coalesce(euser,user,'')=%Q,1)\n"
720 " GROUP BY w) AS x ON c.wkn=x.w;\n",
 
721 zYear, zLimit,
 
722 zYear, zLimit, zUserName
723 );
724 cgi_printf("<br>\n");
725 @ <h1>Timeline events (%h(stats_report_label_for_type()))
726 @ for the calendar weeks of %h(zYear)
@@ -844,25 +851,26 @@
844 zYear
845 );
846 db_multi_exec(
847 "CREATE TEMP TABLE daydata(dn,isodate,dow,n);\n"
848 "WITH RECURSIVE c(daynum, isodate, dow) AS (\n"
849 " VALUES(0,'%q-01-01',0+strftime('%%w','%q-01-01'))\n"
850 " UNION ALL\n"
851 " SELECT daynum+1, date(isodate,'+1 day'), (dow+1)%%7\n"
852 " FROM c\n"
853 " WHERE isodate<%Q\n"
854 ")\n"
855 "INSERT INTO daydata(dn,isodate,dow,n)\n"
856 " SELECT c.daynum, c.isodate, c.dow, coalesce(x.n,0)\n"
857 " FROM c LEFT JOIN (\n"
858 " SELECT date(mtime) AS edate, count(*) AS n\n"
859 " FROM v_reports\n"
860 " WHERE mtime BETWEEN julianday('%q-01-01 00:00:00')\n"
861 " AND julianday(%Q)\n"
862 " AND ifnull(coalesce(euser,user,'')=%Q,1)\n"
863 " GROUP BY edate) AS x ON c.isodate=x.edate;\n",
 
864 zYear, zYear,
865 zLimit,
866 zYear,
867 zLimit,
868 zUserName
869
--- src/statrep.c
+++ src/statrep.c
@@ -681,10 +681,12 @@
681 int rowCount = 0;
682 int total;
683 int iCurrentWeek; /* Current week number */
684 double rNowFraction = 0.0; /* Fraction of current week that has
685 ** passed */
686 double rStartOfYear = 0.0; /* Start of year */
687 double rEndOfYear = 0.0; /* End of the year */
688
689 stats_report_init_view();
690 style_submenu_sql("y", "Year:",
691 "WITH RECURSIVE a(b) AS ("
692 " SELECT substr(date('now'),1,4) UNION ALL"
@@ -695,13 +697,16 @@
697 style_submenu_checkbox("sa", "Show-All", 0, 0);
698 if( zYear==0 || strlen(zYear)!=4 ){
699 zYear = db_text("1970","SELECT substr(date('now'),1,4);");
700 }
701 zLimit = db_text("1971-01-01",
702 "SELECT min(date('%q-01-01','+1 year','-1 day','weekday 6'),"
703 "date('now','weekday 6'))",
704 zYear
705 );
706 rStartOfYear = db_double(0.0,"SELECT julianday('%q-01-01')",zYear);
707 rEndOfYear = db_double(0.0,"SELECT julianday('%q-12-31 23:59:59.999')",zYear);
708 db_multi_exec(
709 "CREATE TEMP TABLE wkdata(wk,n);\n"
710 "WITH RECURSIVE c(wkn) AS (\n"
711 " VALUES(0)\n"
712 " UNION ALL\n"
@@ -709,18 +714,20 @@
714 " WHERE date('%q-01-01',format('%%+d days',wkn*7+7))<=%Q\n"
715 ")\n"
716 "INSERT INTO wkdata(wk,n)\n"
717 " SELECT c.wkn, coalesce(x.n,0)\n"
718 " FROM c LEFT JOIN (\n"
719 " SELECT 0+strftime('%%W',min(max(%!.16g,mtime),%!.16g)) AS w,\n"
720 " count(*) AS n \n"
721 " FROM v_reports\n"
722 " WHERE mtime BETWEEN julianday('%q-01-01','weekday 6','-6 days')\n"
723 " AND julianday('%q 23:59:59.999','weekday 6')\n"
724 " AND ifnull(coalesce(euser,user,'')=%Q,1)\n"
725 " GROUP BY w\n"
726 " ) AS x ON c.wkn=x.w;\n",
727 zYear, zLimit,
728 rStartOfYear, rEndOfYear,
729 zYear, zLimit, zUserName
730 );
731 cgi_printf("<br>\n");
732 @ <h1>Timeline events (%h(stats_report_label_for_type()))
733 @ for the calendar weeks of %h(zYear)
@@ -844,25 +851,26 @@
851 zYear
852 );
853 db_multi_exec(
854 "CREATE TEMP TABLE daydata(dn,isodate,dow,n);\n"
855 "WITH RECURSIVE c(daynum, isodate, dow) AS (\n"
856 " VALUES(0,date('%q-01-01'),0+strftime('%%w','%q-01-01'))\n"
857 " UNION ALL\n"
858 " SELECT daynum+1, date(isodate,'+1 day'), (dow+1)%%7\n"
859 " FROM c\n"
860 " WHERE isodate<%Q\n"
861 ")\n"
862 "INSERT INTO daydata(dn,isodate,dow,n)\n"
863 " SELECT c.daynum, c.isodate, c.dow, coalesce(x.n,0)\n"
864 " FROM c LEFT JOIN (\n"
865 " SELECT date(mtime) AS edate, count(*) AS n\n"
866 " FROM v_reports\n"
867 " WHERE mtime BETWEEN julianday('%q-01-01')\n"
868 " AND julianday('%q 23:59:59.999')\n"
869 " AND ifnull(coalesce(euser,user,'')=%Q,1)\n"
870 " GROUP BY edate\n"
871 " ) AS x ON c.isodate=x.edate;\n",
872 zYear, zYear,
873 zLimit,
874 zYear,
875 zLimit,
876 zUserName
877

Keyboard Shortcuts

Open search /
Next entry (timeline) j
Previous entry (timeline) k
Open focused entry Enter
Show this help ?
Toggle theme Top nav button