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 [link] [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;
}
(17) By MBL (UserMBL) on 2022-08-07 09:33:48 in reply to 16 [link] [source]
strftime('%i',...) seems to be more efficient because computeJD is called only once but
strftime('%G-W%V-%u',...) is more flexible if you need only the ISO week and not the ISO weekday of the whole string; each element calls function computeJD three times.
substr(strftime('%i','now'),1,8) versus strftime('%G-%W','now') the 2nd is a tiny little better in performance; I compared 1,000,000 execution cycles.
With the table from ISO_week_date as input:
sqlite> with RefTable(English,ISOday,ISOweekday) as (
...> values('Sat 1 Jan 1977','1977-01-01','1976-W53-6')
...> ,('Sun 2 Jan 1977','1977-01-02','1976-W53-7')
...> ,('Sat 31 Dec 1977','1977-12-31','1977-W52-6')
...> ,('Sun 1 Jan 1978','1978-01-01','1977-W52-7')
...> ,('Mon 2 Jan 1978','1978-01-02','1978-W01-1')
...> ,('Sun 31 Dec 1978','1978-12-31','1978-W52-7')
...> ,('Mon 1 Jan 1979','1979-01-01','1979-W01-1')
...> ,('Sun 30 Dec 1979','1979-12-30','1979-W52-7')
...> ,('Mon 31 Dec 1979','1979-12-31','1980-W01-1')
...> ,('Tue 1 Jan 1980','1980-01-01','1980-W01-2')
...> ,('Sun 28 Dec 1980','1980-12-28','1980-W52-7')
...> ,('Mon 29 Dec 1980','1980-12-29','1981-W01-1')
...> ,('Tue 30 Dec 1980','1980-12-30','1981-W01-2')
...> ,('Wed 31 Dec 1980','1980-12-31','1981-W01-3')
...> ,('Thu 1 Jan 1981','1981-01-01','1981-W01-4')
...> ,('Thu 31 Dec 1981','1981-12-31','1981-W53-4')
...> ,('Fri 1 Jan 1982','1982-01-01','1981-W53-5')
...> ,('Sat 2 Jan 1982','1982-01-02','1981-W53-6')
...> ,('Sun 3 Jan 1982','1982-01-03','1981-W53-7')
...> ,('now',strftime('%Y-%m-%d','now'),'undefined')
...> )
...> select R.English, R.ISOday
...> , strftime('%Y-%m-%d',ISOday) as ISOday2
...> , R.ISOweekday
...> , strftime('%i',ISOday) as ISOweekday2
...> , strftime('%G-W%V-%u',ISOday) as ISOweekday3
...> , R.ISOweekday == strftime('%i',ISOday) as isEqual2
...> , R.ISOweekday == strftime('%G-W%V-%u',ISOday) as isEqual3
...> from RefTable R;
┌─────────────────┬────────────┬────────────┬────────────┬─────────────┬─────────────┬──────────┬──────────┐
│ English │ ISOday │ ISOday2 │ ISOweekday │ ISOweekday2 │ ISOweekday3 │ isEqual2 │ isEqual3 │
├─────────────────┼────────────┼────────────┼────────────┼─────────────┼─────────────┼──────────┼──────────┤
│ Sat 1 Jan 1977 │ 1977-01-01 │ 1977-01-01 │ 1976-W53-6 │ 1976-W53-6 │ 1976-W53-6 │ 1 │ 1 │
│ Sun 2 Jan 1977 │ 1977-01-02 │ 1977-01-02 │ 1976-W53-7 │ 1976-W53-7 │ 1976-W53-7 │ 1 │ 1 │
│ Sat 31 Dec 1977 │ 1977-12-31 │ 1977-12-31 │ 1977-W52-6 │ 1977-W52-6 │ 1977-W52-6 │ 1 │ 1 │
│ Sun 1 Jan 1978 │ 1978-01-01 │ 1978-01-01 │ 1977-W52-7 │ 1977-W52-7 │ 1977-W52-7 │ 1 │ 1 │
│ Mon 2 Jan 1978 │ 1978-01-02 │ 1978-01-02 │ 1978-W01-1 │ 1978-W01-1 │ 1978-W01-1 │ 1 │ 1 │
│ Sun 31 Dec 1978 │ 1978-12-31 │ 1978-12-31 │ 1978-W52-7 │ 1978-W52-7 │ 1978-W52-7 │ 1 │ 1 │
│ Mon 1 Jan 1979 │ 1979-01-01 │ 1979-01-01 │ 1979-W01-1 │ 1979-W01-1 │ 1979-W01-1 │ 1 │ 1 │
│ Sun 30 Dec 1979 │ 1979-12-30 │ 1979-12-30 │ 1979-W52-7 │ 1979-W52-7 │ 1979-W52-7 │ 1 │ 1 │
│ Mon 31 Dec 1979 │ 1979-12-31 │ 1979-12-31 │ 1980-W01-1 │ 1980-W01-1 │ 1980-W01-1 │ 1 │ 1 │
│ Tue 1 Jan 1980 │ 1980-01-01 │ 1980-01-01 │ 1980-W01-2 │ 1980-W01-2 │ 1980-W01-2 │ 1 │ 1 │
│ Sun 28 Dec 1980 │ 1980-12-28 │ 1980-12-28 │ 1980-W52-7 │ 1980-W52-7 │ 1980-W52-7 │ 1 │ 1 │
│ Mon 29 Dec 1980 │ 1980-12-29 │ 1980-12-29 │ 1981-W01-1 │ 1981-W01-1 │ 1981-W01-1 │ 1 │ 1 │
│ Tue 30 Dec 1980 │ 1980-12-30 │ 1980-12-30 │ 1981-W01-2 │ 1981-W01-2 │ 1981-W01-2 │ 1 │ 1 │
│ Wed 31 Dec 1980 │ 1980-12-31 │ 1980-12-31 │ 1981-W01-3 │ 1981-W01-3 │ 1981-W01-3 │ 1 │ 1 │
│ Thu 1 Jan 1981 │ 1981-01-01 │ 1981-01-01 │ 1981-W01-4 │ 1981-W01-4 │ 1981-W01-4 │ 1 │ 1 │
│ Thu 31 Dec 1981 │ 1981-12-31 │ 1981-12-31 │ 1981-W53-4 │ 1981-W53-4 │ 1981-W53-4 │ 1 │ 1 │
│ Fri 1 Jan 1982 │ 1982-01-01 │ 1982-01-01 │ 1981-W53-5 │ 1981-W53-5 │ 1981-W53-5 │ 1 │ 1 │
│ Sat 2 Jan 1982 │ 1982-01-02 │ 1982-01-02 │ 1981-W53-6 │ 1981-W53-6 │ 1981-W53-6 │ 1 │ 1 │
│ Sun 3 Jan 1982 │ 1982-01-03 │ 1982-01-03 │ 1981-W53-7 │ 1981-W53-7 │ 1981-W53-7 │ 1 │ 1 │
│ now │ 2022-08-07 │ 2022-08-07 │ undefined │ 2022-W31-7 │ 2022-W31-7 │ 0 │ 0 │
└─────────────────┴────────────┴────────────┴────────────┴─────────────┴─────────────┴──────────┴──────────┘
sqlite>
I love both enhancements '%i' and '%G-W%V-%u', one for simplicity and the other for flexibility and would like to see one or the other in future version of of SQLite3 core.
(18.1) By Keith Medcalf (kmedcalf) on 2022-08-07 19:46:22 edited from 18.0 in reply to 17 [link] [source]
Even better patch that combines both into one and defines agglomerated format specifiers so that there is no need to recompute the same thing redundantly:
case 'u': /* ISO day (1=Mon...7=Sun) */ {
sqlite3_str_appendchar(&sRes, 1,
(char)(((x.iJD + 648000000)/86400000) % 7) + '1');
break;
}
case 'i': /* yyyy-Www-d */
case 'I': /* yyyy-Www */
case 'V': /* ww */
case 'G': /* yyyy */
case 'q': /* yyyy-Qq */
case 'Q': /* q */ {
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 + 648000000)/86400000) % 7))*86400000;
computeYMD(&y);
if( zFmt[i]=='G' ){
sqlite3_str_appendf(&sRes,"%04d",y.Y);
}else{
sqlite3_int64 tJD = y.iJD;
int nWeek;
if( zFmt[i]=='i' || zFmt[i]=='I' )
sqlite3_str_appendf(&sRes, "%04d-W", y.Y);
if( zFmt[i]=='q' )
sqlite3_str_appendf(&sRes, "%04d-Q", y.Y);
/* borrowed code from case 'j' */
y.validJD = 0;
y.M = 1;
y.D = 1;
computeJD(&y);
nWeek = (int)((tJD-y.iJD+43200000)/86400000/7+1);
if( zFmt[i]=='Q' || zFmt[i]=='q') {
nWeek = (nWeek + 13) / 13;
nWeek -= (nWeek == 5);
sqlite3_str_appendchar(&sRes, 1, nWeek + '0');
}
else sqlite3_str_appendf(&sRes,"%02d", nWeek);
if( zFmt[i]=='i' )
sqlite3_str_appendf(&sRes, "-%d", ((x.iJD + 648000000)/86400000) % 7 + 1);
}
break;
}
(19) By MBL (UserMBL) on 2022-08-08 07:44:35 in reply to 18.1 [link] [source]
This new section is working great and as expected also more performant.
Next question is: What's the best way to get the ISO day from an ISO weekday, an ISO week or an ISO quarter?
strftime('%Y-%m-%d', strftime('%Q','now') ) is giving a wrong reply and the other formats (i,I,q) nothing.
strftime('%Y-%m-%d', strftime('%Y-%m-%d','now') ) is giving the correct formatted result of 'now'.
How to obtain the starting date from an ISO period format?
(21) By Keith Medcalf (kmedcalf) on 2022-08-08 09:13:52 in reply to 19 [link] [source]
Arrrrg. Use this for this part (do not change the other case).
case 'i': /* Fall thru yyyy-Www-d */
case 'I': /* Fall thru yyyy-Www */
case 'V': /* Fall thru w */
case 'X': /* Fall thru yyyy-Mmm */
case 'x': /* Fall thru mm */
case 'G': /* Fall thru yyyy */
case 'q': /* Fall thru yyyy-Qq */
case 'Q': /* q */ {
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 + 648000000)/86400000) % 7))*86400000;
computeYMD(&y);
if( zFmt[i]=='G' ){
sqlite3_str_appendf(&sRes,"%04d",y.Y);
}else{
sqlite3_int64 tJD = y.iJD;
int nWeek;
if( zFmt[i]=='i' || zFmt[i]=='I' )
sqlite3_str_appendf(&sRes, "%04d-W", y.Y);
else if( zFmt[i]=='q' )
sqlite3_str_appendf(&sRes, "%04d-Q", y.Y);
else if( zFmt[i]=='X' )
sqlite3_str_appendf(&sRes, "%04d-M%02d", y.Y, y.M);
else if( zFmt[i]=='x' )
sqlite3_str_appendf(&sRes, "%02d", y.M);
/* borrowed code from case 'j' */
y.validJD = 0;
y.M = 1;
y.D = 1;
computeJD(&y);
nWeek = (int)((tJD-y.iJD+43200000)/86400000/7+1);
if( zFmt[i]=='Q' || zFmt[i]=='q') {
nWeek = (nWeek + 13) / 13;
nWeek -= (nWeek == 5);
sqlite3_str_appendchar(&sRes, 1, nWeek + '0');
}
else if( zFmt[i]=='V' || zFmt[i]=='I')
sqlite3_str_appendf(&sRes,"%02d", nWeek);
else if( zFmt[i]=='i' )
sqlite3_str_appendf(&sRes, "%02d-%d", nWeek, ((x.iJD + 648000000)/86400000) % 7 + 1);
}
break;
}
How to obtain the starting date from an ISO period format?
What do you mean? Do you mean as in generate the boundaries?
Like this?
with inputs(year, zone, count, dow, iso) as
(
select cast(:year as integer),
coalesce(:zone, 'localtime'),
cast(coalesce(:count, 1) as integer),
cast(iif(:yearly, -3, iif(:quarterly, -2, iif(:monthly, -1, :dow))) as integer),
(:iso is True)
),
constants(yearlower, yearupper, base, zone, unzone, zoneaware, count, dow, jms, iso) as
(
select year,
year + count - 1,
julianday(format('%04d-01-01 00:00:00 Z', year), '-14 days'),
zone,
iif(instr(zone,'/'), '-' || zone, 'utc'),
strftime('%Z') is not null,
count * 365.2425 + 28,
dow,
1.0 / 86400000.0,
iso
from inputs
),
jd1(jd) as
(
select julianday(base, 'julianday', zone, 'start of day', 'naive', unzone)
from constants
where zoneaware
union all
select julianday(jd, 'julianday', zone, '+1 day', 'naive', unzone)
from constants, jd1
where jd < base + count
),
jd2(jd) as
(
select julianday(base, 'julianday', zone, 'start of day', unzone)
from constants
where not zoneaware
union all
select julianday(jd, 'julianday', zone, '+1 day', unzone)
from constants, jd2
where jd < base + count
),
jdall(jd) as
(
select jd
from jd1
union all
select jd
from jd2
),
jds(jd) as
(
-- default all days
select jd
from constants, jdall
where dow is null
union all
-- dow
select jd
from constants, jdall
where iso == 0
and dow > -1
and cast(strftime('%w', jd, 'julianday', zone) as integer) == dow
union all
-- monthly=True
select jd
from constants, jdall
where iso == 0
and dow == -1
and cast(strftime('%d', jd, 'julianday', zone) as integer) == 1
union all
-- quarterly=True
select jd
from constants, jdall
where iso == 0
and dow == -2
and cast(strftime('%m%d', jd, 'julianday', zone) as integer) in (101, 401, 701, 1001)
union all
-- yearly=True
select jd
from constants, jdall
where iso == 0
and dow == -3
and cast(strftime('%m%d', jd, 'julianday', zone) as integer) == 101
union all
-- iso=True dow
select jd
from constants, jdall
where iso == 1
and dow > 0
and cast(strftime('%u', jd, 'julianday', zone) as integer) == dow
union all
-- iso=True monthly=True
select min(jd)
from constants, jdall
where iso == 1
and dow == -1
group by strftime('%X', jd, 'julianday', zone)
union all
-- iso=True quarterly=True
select min(jd)
from constants, jdall
where iso == 1
and dow == -2
group by strftime('%q', jd, 'julianday', zone)
union all
-- iso=True yearly=True
select min(jd)
from constants, jdall
where iso == 1
and dow == -3
group by cast(strftime('%G', jd, 'julianday', zone) as integer)
),
boundaries(startjd, stopjd) as
(
select jd,
lead(jd) over () - jms
from constants, jds
),
limits(start, stop, startjd, stopjd, inTransition) as
(
select datetime(startjd, 'julianday', zone),
datetime(stopjd, 'julianday', zone),
startjd,
stopjd,
cast(((stopjd - startjd) * 86400.0 + 0.5) as integer) % 86400 != 0 as isTransition
from constants, boundaries
where (iso == 0 and (
likely(cast(strftime('%Y', startjd, 'julianday', zone) as integer) between yearlower and yearupper)
or unlikely(cast(strftime('%Y', stopjd, 'julianday', zone) as integer) between yearlower and yearupper)
))
or (iso != 0 and cast(strftime('%G', startjd, 'julianday', zone) as integer) between yearlower and yearupper)
)
select start,
stop,
startjd,
stopjd,
inTransition
from limits
It is designed for a statement vtab. You can run the statement with bindings, however.
:year start at year
:zone in this timezone
:count number of years
:yearly 1 = yearly intervals
:quarterly 1 = quarterly intervals
:monthly 1 = monthly intervals
:dow (0=Sun...6=Sat) start day for weeks
:iso ISO date semantics used for everything
So if you wrapped the above SQL in a statement vtab called DateLimits then you could do something like:
select strftime('%q', start) as Quarter, * from datelimits where year=2020 and count=5 and quarterly=1 and iso=1;
┌───────────┬──────────────────────────────────┬──────────────────────────────────┬──────────────────┬──────────────────┬──────────────┐
│ Quarter │ start │ stop │ startjd │ stopjd │ inTransition │
├───────────┼──────────────────────────────────┼──────────────────────────────────┼──────────────────┼──────────────────┼──────────────┤
│ '2020-Q1' │ '2019-12-30 00:00:00.000 -07:00' │ '2020-03-22 23:59:59.999 -06:00' │ 2458847.79166667 │ 2458931.74999999 │ 1 │
│ '2020-Q2' │ '2020-03-23 00:00:00.000 -06:00' │ '2020-06-21 23:59:59.999 -06:00' │ 2458931.75 │ 2459022.74999999 │ 0 │
│ '2020-Q3' │ '2020-06-22 00:00:00.000 -06:00' │ '2020-09-20 23:59:59.999 -06:00' │ 2459022.75 │ 2459113.74999999 │ 0 │
│ '2020-Q4' │ '2020-09-21 00:00:00.000 -06:00' │ '2021-01-03 23:59:59.999 -07:00' │ 2459113.75 │ 2459218.79166665 │ 1 │
│ '2021-Q1' │ '2021-01-04 00:00:00.000 -07:00' │ '2021-03-28 23:59:59.999 -06:00' │ 2459218.79166667 │ 2459302.74999999 │ 1 │
│ '2021-Q2' │ '2021-03-29 00:00:00.000 -06:00' │ '2021-06-27 23:59:59.999 -06:00' │ 2459302.75 │ 2459393.74999999 │ 0 │
│ '2021-Q3' │ '2021-06-28 00:00:00.000 -06:00' │ '2021-09-26 23:59:59.999 -06:00' │ 2459393.75 │ 2459484.74999999 │ 0 │
│ '2021-Q4' │ '2021-09-27 00:00:00.000 -06:00' │ '2022-01-02 23:59:59.999 -07:00' │ 2459484.75 │ 2459582.79166665 │ 1 │
│ '2022-Q1' │ '2022-01-03 00:00:00.000 -07:00' │ '2022-03-27 23:59:59.999 -06:00' │ 2459582.79166667 │ 2459666.74999999 │ 1 │
│ '2022-Q2' │ '2022-03-28 00:00:00.000 -06:00' │ '2022-06-26 23:59:59.999 -06:00' │ 2459666.75 │ 2459757.74999999 │ 0 │
│ '2022-Q3' │ '2022-06-27 00:00:00.000 -06:00' │ '2022-09-25 23:59:59.999 -06:00' │ 2459757.75 │ 2459848.74999999 │ 0 │
│ '2022-Q4' │ '2022-09-26 00:00:00.000 -06:00' │ '2023-01-01 23:59:59.999 -07:00' │ 2459848.75 │ 2459946.79166665 │ 1 │
│ '2023-Q1' │ '2023-01-02 00:00:00.000 -07:00' │ '2023-03-26 23:59:59.999 -06:00' │ 2459946.79166667 │ 2460030.74999999 │ 1 │
│ '2023-Q2' │ '2023-03-27 00:00:00.000 -06:00' │ '2023-06-25 23:59:59.999 -06:00' │ 2460030.75 │ 2460121.74999999 │ 0 │
│ '2023-Q3' │ '2023-06-26 00:00:00.000 -06:00' │ '2023-09-24 23:59:59.999 -06:00' │ 2460121.75 │ 2460212.74999999 │ 0 │
│ '2023-Q4' │ '2023-09-25 00:00:00.000 -06:00' │ '2023-12-31 23:59:59.999 -07:00' │ 2460212.75 │ 2460310.79166665 │ 1 │
│ '2024-Q1' │ '2024-01-01 00:00:00.000 -07:00' │ '2024-03-24 23:59:59.999 -06:00' │ 2460310.79166667 │ 2460394.74999999 │ 1 │
│ '2024-Q2' │ '2024-03-25 00:00:00.000 -06:00' │ '2024-06-23 23:59:59.999 -06:00' │ 2460394.75 │ 2460485.74999999 │ 0 │
│ '2024-Q3' │ '2024-06-24 00:00:00.000 -06:00' │ '2024-09-22 23:59:59.999 -06:00' │ 2460485.75 │ 2460576.74999999 │ 0 │
│ '2024-Q4' │ '2024-09-23 00:00:00.000 -06:00' │ '2024-12-29 23:59:59.999 -07:00' │ 2460576.75 │ 2460674.79166665 │ 1 │
└───────────┴──────────────────────────────────┴──────────────────────────────────┴──────────────────┴──────────────────┴──────────────┘
(23.1) By Harald Hanche-Olsen (hanche) on 2024-01-21 13:59:40 edited from 23.0 in reply to 21 [link] [source]
Edit: This is now obsolete, as the official source now supports the %G, %V format specifiers (and %u, as noted in the original post). I am adding this note for the benefit of anyone who might land here looking for ISO week support.
Just a few days ago, drh added the %u and %I format specifiers, so I needed to change the patch. The %I modifier clashes with yours, so I have abandoned it in my version of the patch. Otherwise, this now works, and passed my tests (which only test %V, %G and %u modifiers):
Index: src/date.c
==================================================================
--- src/date.c
+++ src/date.c
@@ -1370,10 +1370,56 @@
case 'w': {
char c = (char)(((x.iJD+129600000)/86400000) % 7) + '0';
if( c=='0' && cf=='u' ) c = '7';
sqlite3_str_appendchar(&sRes, 1, c);
break;
+ }
+ case 'i': /* Fall thru yyyy-Www-d */
+ case 'V': /* Fall thru w */
+ case 'X': /* Fall thru yyyy-Mmm */
+ case 'x': /* Fall thru mm */
+ case 'G': /* Fall thru yyyy */
+ case 'q': /* Fall thru yyyy-Qq */
+ case 'Q': /* q */ {
+ 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 + 648000000)/86400000) % 7))*86400000;
+ computeYMD(&y);
+ if( zFmt[i]=='G' ){
+ sqlite3_str_appendf(&sRes,"%04d",y.Y);
+ }else{
+ sqlite3_int64 tJD = y.iJD;
+ int nWeek;
+ if( zFmt[i]=='i' )
+ sqlite3_str_appendf(&sRes, "%04d-W", y.Y);
+ else if( zFmt[i]=='q' )
+ sqlite3_str_appendf(&sRes, "%04d-Q", y.Y);
+ else if( zFmt[i]=='X' )
+ sqlite3_str_appendf(&sRes, "%04d-M%02d", y.Y, y.M);
+ else if( zFmt[i]=='x' )
+ sqlite3_str_appendf(&sRes, "%02d", y.M);
+ /* borrowed code from case 'j' */
+ y.validJD = 0;
+ y.M = 1;
+ y.D = 1;
+ computeJD(&y);
+ nWeek = (int)((tJD-y.iJD+43200000)/86400000/7+1);
+ if( zFmt[i]=='Q' || zFmt[i]=='q') {
+ nWeek = (nWeek + 13) / 13;
+ nWeek -= (nWeek == 5);
+ sqlite3_str_appendchar(&sRes, 1, nWeek + '0');
+ }
+ else if( zFmt[i]=='V')
+ sqlite3_str_appendf(&sRes,"%02d", nWeek);
+ else if( zFmt[i]=='i' )
+ sqlite3_str_appendf(&sRes, "%02d-%d", nWeek,
+ ((x.iJD + 648000000)/86400000) % 7 + 1);
+ }
+ break;
}
case 'Y': {
sqlite3_str_appendf(&sRes,"%04d",x.Y);
break;
}
(20) By Harald Hanche-Olsen (hanche) on 2022-08-08 09:08:24 in reply to 18.1 [link] [source]
That's nice! I'll replace my old patch with this one.
Re the Q and q specifiers: I didn't know about those before, but from the code, I gather that a quarter is a chunk of thirteen weeks, Q1 starting with W01 (and Q4 occasionally having 14 weeks). Makes sense, I guess.
So, what are the chances of getting this, or something much like it, into the official SQLite source?
PS. Not that it matters much, or at all really – but why do you add seven and a half day (648000000 ms) to y.iJD, rather than just half a day (43200000 ms)? As far as I can tell, y.iJD is never negative, so you don't need the extra seven days in order to avoid reducing a negative number mod 7.
(22) By Keith Medcalf (kmedcalf) on 2022-08-08 09:23:43 in reply to 20 [link] [source]
Not that it matters much, or at all really – but why do you add seven and a half day (648000000 ms) to y.iJD, rather than just half a day (43200000 ms)?
I use a modified date.c that uses "instant in time" datetime rather than naive datetimes (that is, it tracks the tz offset, iJD is always relative to the Prime Meridian (UT)) plus has full IANA timezone tables.
So adding 7.5 days rather than 0.5 days assures that the result is positive when adjusted for the tz offset.
(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 [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.