strftime('%i') ISO Calendar Format

(1.2) By Keith Medcalf (kmedcalf) on 2022-08-03 22:51:08 edited from 1.1 [link] [source]

Patch for strftime function to add a new format specifier (%i) which will output in ISO Datetime Format (yyyy-Www-d where yyyy is the ISO Year, ww is the ISO Week (01 thru 53), d is the ISO day (Mon=1...Sun=7).

      case 'i': {
        DateTime y = x;
        int DayOfYearNumber, Jan1Weekday, Weekday, YearNumber, WeekNumber;

        y.validJD = 0;
        y.M = 1;
        y.D = 1;
        computeJD(&y);
        DayOfYearNumber = (int)((x.iJD-y.iJD+43200000)/86400000) + 1;
        Jan1Weekday = (((((y.iJD+129600000)/86400000) % 7) + 6) % 7) + 1;
        Weekday =     (((((x.iJD+129600000)/86400000) % 7) + 6) % 7) + 1;
        if ((DayOfYearNumber <= (8 - Jan1Weekday)) && (Jan1Weekday > 4))
        {
            YearNumber = x.Y - 1;
            WeekNumber = ((Jan1Weekday == 5) || (Jan1Weekday == 6 && isLeapYear(x.Y - 1))) ? 53 : 52;
        }
        else if (((365 + isLeapYear(x.Y)) - DayOfYearNumber) < (4 - Weekday))
        {
            YearNumber = x.Y + 1;
            WeekNumber = 1;
        }
        else
        {
            YearNumber = x.Y;
            WeekNumber = ((DayOfYearNumber + (7 - Weekday) + (Jan1Weekday - 1)) / 7) - (Jan1Weekday > 4);
        }
        sqlite3_str_appendf(&sRes,"%04d-W%02d-%d", YearNumber, WeekNumber, Weekday);
        break;
      }

Edited to remove the timezone handling so it will work in the default (naive) date.c

Also, the isLeapYear function is needed:

static inline int isLeapYear(int Y)
{
    return (((Y % 4 == 0)  &&  (Y % 100 != 0)) || (Y % 400 == 0));
}

(2) By MBL (UserMBL) on 2022-08-04 07:34:06 in reply to 1.2 [source]

This is great ! I often looked for such a support function for evenly distributed group by expressions based on date timeline accross each year.

Question: What is the recommended best, easiest and most efficient way then in SQLite3 to translate the ISO 8601 Calendar format¹ back into gregorian rsp. julian date?

^{^} The ISO week date system is effectively a leap week calendar system that is part of the ISO 8601 date and time standard since 1988

(3) By Harald Hanche-Olsen (hanche) on 2022-08-04 07:43:41 in reply to 1.2 [link] [source]

For cross reference purposes, see also my own ISO week number patch, adding conversion %u, %V, and %G. These are standard in other sprintf() implementations.

The two patches take somewhat different approaches. Mine does not require working out if it's a leap year or not.