SQLite Forum

Week numbers in queries
Login
I am reviving this old thread to announce that I wrote a patch that implements the %V, %u and %G directives from (unix) `strftime()`. Briefly, %V is the week number as ISO 8601 defines it, %G is the corresponding year (since a week is considered to belong to the year that holds most of its days), and %u is the weekday with 1=Monday, 7=Sunday.

Without further ado, here is the patch:

```
Index: src/date.c
==================================================================
--- src/date.c
+++ src/date.c
@@ -989,17 +989,20 @@
 **
 ** Return a string described by FORMAT.  Conversions as follows:
 **
 **   %d  day of month
 **   %f  ** fractional seconds  SS.SSS
+**   %G  year 0000-9999 of current week's Thursday
 **   %H  hour 00-24
 **   %j  day of year 000-366
 **   %J  ** julian day number
 **   %m  month 01-12
 **   %M  minute 00-59
 **   %s  seconds since 1970-01-01
 **   %S  seconds 00-59
+**   %u  day of week 1-7  monday==1
+**   %V  week of year 01-53  week belongs to year with most of its days
 **   %w  day of week 0-6  sunday==0
 **   %W  week of year 00-53
 **   %Y  year 0000-9999
 **   %%  %
 */
@@ -1025,13 +1028,15 @@
         case 'd':
         case 'H':
         case 'm':
         case 'M':
         case 'S':
+        case 'V':
         case 'W':
           n++;
           /* fall thru */
+        case 'u':
         case 'w':
         case '%':
           break;
         case 'f':
           n += 8;
@@ -1038,10 +1043,11 @@
           break;
         case 'j':
           n += 3;
           break;
         case 'Y':
+        case 'G':
           n += 8;
           break;
         case 's':
         case 'J':
           n += 50;
@@ -1117,13 +1123,42 @@
           sqlite3Int64ToText(iS, &z[j]);
           j += sqlite3Strlen30(&z[j]);
           break;
         }
         case 'S':  sqlite3_snprintf(3,&z[j],"%02d",(int)x.s); j+=2; break;
+        case 'u': {
+          z[j++] = (char)(((x.iJD+43200000)/86400000) % 7) + '1';
+          break;
+        }
         case 'w': {
           z[j++] = (char)(((x.iJD+129600000)/86400000) % 7) + '0';
           break;
+        }
+        case 'V':  /* Fall thru */
+        case 'G': {
+          DateTime y = x;
+          computeJD(&y);
+          y.validYMD = 0;
+          /* Adjust date to Thursday this week:
+             The number in parentheses is 0 for Monday, 3 for Thursday */
+          y.iJD += (3 - (((y.iJD+43200000)/86400000) % 7))*86400000;
+          computeYMD(&y);
+          if( zFmt[i]=='G' ){
+            sqlite3_snprintf(5,&z[j],"%04d",y.Y); j+=sqlite3Strlen30(&z[j]);
+          }else{
+            /* borrowed code from case 'j' */
+            sqlite3_int64 tJD = y.iJD;
+            int nDay;
+            y.validJD = 0;
+            y.M = 1;
+            y.D = 1;
+            computeJD(&y);
+            nDay = (int)((tJD-y.iJD+43200000)/86400000);
+            sqlite3_snprintf(3, &z[j],"%02d",nDay/7+1);
+            j += 2;
+          }
+          break;
         }
         case 'Y': {
           sqlite3_snprintf(5,&z[j],"%04d",x.Y); j+=sqlite3Strlen30(&z[j]);
           break;
         }

```

This is unencumbered code. I wrote it from scratch, only drawing inspiration (and copying/modifying snippets) from the sqlite source. Anyone, including the sqlite core team, is allowed to use it as they see fit. I am willing to sign papers to that effect. But of course, even though I have tested the code and believe it to be correct, I do not accept liability if there is a bug. Use at your own risk.

I would be happy if code resembling this were to make it into the official sqlite source. ISO week numbers are used all over the place in Europe, after all.

**Edited to add:** If anyone would like to review the code, any comments are welcome. And if anything about the code is unclear, please ask. I have some tests generated with GNU date, if anyone is interested. But I have not tried to write test cases fitting into the sqlite test framework, since I have no idea how.