SQLite Forum

Week numbers in queries
Login

Week numbers in queries

(1) By Superfar on 2020-05-01 22:20:11 [link] [source]

Hi All
I am trying to build a query that returns all records from a specific week number. I could not get it to work, so I have narrowed it down to this:

SELECT strftime('%W', StartTime, 'unixepoch', 'localtime') 
FROM Records 
WHERE  strftime('%W', StartTime, 'unixepoch', 'localtime') <> 17;

which should return the week number of all records *not* started in week 17. However, it matches 5 records and all 5 returns a week number of 17! Looks like the strftime returns 17 in the SELECT part and something else in the WHERE part. I don't believe that so chances are I am doing something stupid here. Can anyone please tell me how to do this right?

Br
Morten

(2) By jose isaias cabrera (jicman) on 2020-05-02 00:32:04 in reply to 1 [link] [source]

Hi.

Take a look at this: 8:20:46.21>sqlite3 SQLite version 3.30.1 2019-10-10 20:19:45 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> SELECT strftime('%W','2019-01-01'); 00 sqlite> SELECT strftime('%W','2019-01-02'); 00 sqlite> SELECT strftime('%W','2019-01-03'); 00 sqlite> SELECT strftime('%W','2019-01-04'); 00 sqlite> SELECT strftime('%W','2019-01-05'); 00 sqlite> SELECT strftime('%W','2019-01-06'); 00 sqlite> SELECT strftime('%W','2019-01-07'); 01 sqlite> SELECT strftime('%W','2020-01-01'); 00 sqlite> SELECT strftime('%W','2020-01-02'); 00 sqlite> SELECT strftime('%W','2020-01-03'); 00 sqlite> SELECT strftime('%W','2020-01-04'); 00 sqlite> SELECT strftime('%W','2020-01-05'); 00 sqlite> SELECT strftime('%W','2020-01-06'); 01 sqlite> SELECT strftime('%W','2020-01-06');

It may be that what you think week 17 is , may not be calculated as week 17. It depends a lot on how you want to define a 'week' and what you are going to use it for. I had this problem once, and some of the folks here kindly guided me correctly. Play around with the dates, and you'll see some holes. I hope this helps.

josé

(3) By dab on 2020-05-02 02:38:36 in reply to 1 [link] [source]

strftime() returns a string (i.e. text)

select '17' <> 17;

either cast or convert to integer

where  (0+strftime('%W', StartTime,'unixepoch', 'localtime')) <> 17

(4) By Superfar on 2020-05-02 10:07:31 in reply to 3 [link] [source]

Yes! I knew I was doing something stupid. I am a seasoned firmware-programmer but a complete rookie with databases, and the type-system is a little different from what I am used to in C/C++. Thank you so much :-)

(5) By L Carl (lcarlp) on 2020-05-02 14:44:38 in reply to 4 [link] [source]

Not stupid at all! I have a lot of experience with other SQL engines and SQLite has surprised me a number of times with this kind of thing. Otoh, I’ve never used a system that did not have some surprising and seemingly unusual behavior.

(14) By anonymous on 2021-04-18 08:08:43 in reply to 3 [link] [source]

Hi there. Just wanted to give a shoutout to you. I'm writing a PHP app, and using sqlite as my database. My app is one long PHP file, around 4k lines. I have 1 db file, and 1 html/css/js file for my website. It's so cool to have the entire app contained in just 3 files.

Anyways, your post solved an hours-long issue that I couldn't resolve which was surrounded around dates and the CASE statement in sqlite.

(6) By luuk on 2020-05-03 08:28:45 in reply to 1 [link] [source]

Is there a reason why '%V' is not recognized by SQLite ?

%V The ISO 8601 week number (see NOTES) of the current year as a decimal number, range 01 to 53, where week 1 is the first week that has at least 4 days in the new year. See also %U and %W. (Calculated from tm_year, tm_yday, and tm_wday.) (SU)

%w The day of the week as a decimal, range 0 to 6, Sunday being 0. See also %u. (Calculated from tm_wday.)

%W The week number of the current year as a decimal number, range 00 to 53, starting with the first Monday as the first day of week 01. (Calculated from tm_yday and tm_wday.)

(7.1) By Keith Medcalf (kmedcalf) on 2020-05-03 15:08:10 edited from 7.0 in reply to 6 [link] [source]

No one has written the code to compute that yet. Feel free to submit a patch. https://sqlite.org/copyright.html

(There is no %V in the documentation, and the %w and %W are considerably different from your quoted text, so clearly you are quoting from something else that calls something strftime. You should complain to the "things of the same name should do the same thing in the same way" guy, but he has been over at Microsoft trying to explain the concept to them for the last 40 years...)

(10) By Aloys (aloys) on 2020-05-04 07:41:11 in reply to 7.1 [link] [source]

My guess is that the OP quoted from strftime man page or a similar source.

Maybe SQLite could adopt Arnold Robbins' strftime. The code is in the public domain. So there wouldn't be a license issue.

(13.1) By Harald Hanche-Olsen (hanche) on 2021-03-20 13:10:02 edited from 13.0 in reply to 7.1 [link] [source]

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.

(15) By Mark Lawrence (mark) on 2021-04-27 07:45:31 in reply to 13.1 [link] [source]

Thanks for that Harald. I have needed a few of these over the years and it has always been a pain that SQLite hasn't supported them.

I can only encourage the developers to incorporate these for easier international date presentations.

(16) By Harald Hanche-Olsen (hanche) on 2021-10-15 16:41:52 in reply to 13.1 [source]

The patch I posted here was broken by the refactoring in checkin e548e9299d. Here is a new patch, in case anyone is interested.

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
 **   %%  %
 */
@@ -1079,15 +1082,44 @@
         break;
       }
       case 'S': {
         sqlite3_str_appendf(&sRes,"%02d",(int)x.s);
         break;
+      }
+      case 'u': {
+        sqlite3_str_appendchar(&sRes, 1,
+                       (char)(((x.iJD+43200000)/86400000) % 7) + '1');
+        break;
       }
       case 'w': {
         sqlite3_str_appendchar(&sRes, 1,
                        (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_str_appendf(&sRes,"%04d",y.Y);
+        }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_str_appendf(&sRes,"%02d",nDay/7+1);
+        }
+        break;
       }
       case 'Y': {
         sqlite3_str_appendf(&sRes,"%04d",x.Y);
         break;
       }

(8) By dab on 2020-05-04 02:02:16 in reply to 6 [link] [source]

%z (timezone offset) is also missing.

(9) By luuk on 2020-05-04 06:19:53 in reply to 8 [link] [source]

In the docs it says:

" Universal Coordinated Time (UTC) is used."

(https://www.sqlite.org/lang_datefunc.html )

Implementing '%z' will be useless if only UTC is used.

(11) By Keith Medcalf (kmedcalf) on 2020-05-04 16:16:14 in reply to 9 [link] [source]

More correctly the formatting function uses the Lilian Date.

The difference between a Lilian Date and a Julian Date is that the "Julian" epoch occurs at noon at the prime meridian, but a "Lilian" has an epoch which occurs at noon at some other (local) meridian.

The internal datetime structure does not carry the meridian offset of the Lilian Date it stores relative to the prime meridian, so calculation of the "time zone offset" is not possible.

Also, the functions do not use UTC.

They use "Universal Time" time in which a minute always contains exactly 60 seconds, and hour contains exactly sixty minutes, a day contains exactly 24 hours, and a year contains exactly 365 days (except when it contains exactly 366 days). The length of a "second" is variable in order to maintain these invariant.

UTC allows a minute to contain 60 seconds "give or take a few" in order to approximate (the C in UTC stands for "on average approximately") UT1 time using fixed length seconds.

(12) By Keith Medcalf (kmedcalf) on 2020-05-04 17:09:32 in reply to 11 [link] [source]

Correction -- the Lilian (or Lillian) epoch, depending on whether you work for or with IBM or not is not based on the Julian Epoch but rather the Gregorian epoch.

However, the concept is the same. "Julianday" has its 0 defined as Noon at the prime meridian on November 24, 4714 BC. The internal datetime structure stores the datetime as the offset (in ms) from Noon on November 24, 4714 BC at "some (local) meridian" which is neither specified nor stored.

The "julianday" function returns this value converted into "days" (divided by 86400000).

It uses the "julianday" scale and base, but at an arbitrary and unrecorded rotational offset.