Running this SQL:

    WITH dates(d) AS (
      VALUES ('2121-01-01'), ('2121-01-02'), ('2121-01-03'), ('2121-01-04'), ('2121-01-13'),
             ('2122-01-01'), ('2121-02-02'), ('2122-01-03'), ('2122-01-13'),
             ('2123-01-01'), ('2123-01-02'), ('2123-01-03')
    )
    SELECT d, strftime("week=%W day=%u", d) FROM dates;

Produces:

    ┌────────────┬───────────────────────────────┐
    │     d      │ strftime("week=%W day=%u", d) │
    ├────────────┼───────────────────────────────┤
    │ 2121-01-01 │ week=00 day=3                 │
    │ 2121-01-02 │ week=00 day=4                 │
    │ 2121-01-03 │ week=00 day=5                 │
    │ 2121-01-04 │ week=00 day=6                 │
    │ 2121-01-13 │ week=02 day=1                 │
    │ 2122-01-01 │ week=00 day=4                 │
    │ 2121-02-02 │ week=04 day=7                 │
    │ 2122-01-03 │ week=00 day=6                 │
    │ 2122-01-13 │ week=02 day=2                 │
    │ 2123-01-01 │ week=00 day=5                 │
    │ 2123-01-02 │ week=00 day=6                 │
    │ 2123-01-03 │ week=00 day=7                 │
    └────────────┴───────────────────────────────┘

Which looks correct to me. Am I missing something?

Dan.

deleted because I misread the question :(

So I need to write some code to achieve this.

Do you have any ideas on how to incorporate this in SQLite? I don't mean how to code an extension (I have written quite a few of them). For example, I could write a function like iso_date or datatime(@iso'now').

The last one is a nice challenge and only possible if I am able to overload the datetime function of SQLite. I know C doesn't support that.

Do you have any ideas on how to incorporate this in SQLite?

You may not need to. i have it on good authority that %U and %V are currently being strongly considered for inclusion.

The last one is a nice challenge and only possible if I am able to overload the datetime function of SQLite. I know C doesn't support that.

C doesn't but sqlite permits overloading of functions. You would not be able to support the @iso prefix you demonstrate because that level would be handled by sqlite's tokenizer.

I can wait a version or two. I will wait patiently for the version which will add full support for the ISO calendar.

I can wait a version or two.

Just note that "being strongly considered" does not mean "will definitely be added"! It is under investigation, in any case.

This part is just speculation, but there are presumably reasons (now lost to history) why %U and %V were not added along with the rest of the formats years ago.

So I need to write some code to achieve this.

No need; it has already been done.

You might look at this thread (now obsoleted by the inclusion of these features in the source, see elsewhere in this thread) for an extensive discussion of the issue, with patches to add the desired functionality. Note that the source code has changed a bit over time, so you need to locate the appropriate patch in the trhead. Most likely the latest one, if you are compiling from a recent source.

Of course, official support for the %U, $v, and %G substitutions would be most welcom.

Edited to note the happy update of the official source

I regard it as very important to note that besides %V also %G is crucial, without it %V is useless.

See src:e1155d6a. The checkin comment mentions only %g, but the code also handles %G. (Edit: not true - my eyes just don't work right.)

To verify the SQLite computes the Julian date correctly, I visited the NASA website https://ssd.jpl.nasa.gov/tools/jdc/#/cd

SQLite computes the Julian date (← please note that it is a date!) correctly, with the roll-over to the next Julian date at noon of a Gregorian day (that is the calendar we use daily).

Keith Medcalf ((12) By Keith Medcalf (kmedcalf) on 2020-05-04 17:09:32 in reply to 11 [link] [source]) said: 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 date November 24, 4714 BC will ring the creationists bell; it was the day the world was created. The reason for using this date way back has to do with the invention of the negative numbers. The reasoning for using this date (for which we have to thank Julius Scaliger) was that there is nothing before this date, and this avoided having to deal with negative numbers.

The SQLite version I am using is SQLite version 3.44.2 (compiled on 2023-11-24 11:41:44) and that is a pretty recent version that has no support for the ISO date. To prove my statement: select strftime('week=%W day=%u', '2021-01-01'); Gives as result week=00 day=5 which is incorrect if it is an ISO date; however the weekday is the correct ISO weekday. The correct answer is: The year is still 2020, week 53, day 5.

If you type: select strftime('week=%w day=%u', '2021-01-01');

The result is week=5 day=5 and this makes no sense. It was luuk (By luuk on 2020-05-03 08:28:45 in reply to 1 [link] [source]) who explained what %V and %w should do.

However, the ISO week numbers are from 1 to 53 (for the leap year) using the %V. And one could keep the %W for a semi-ISO week system with weeks from 0 to 52.

You must include tests for leap years, such as the 3th of January 2021 and the 1st of January 2000.

The test cases are from Wikipedia, and they cover 1980 which is a leap year, but yes I can always add more.

One less patch for me to support. Great!

I wish you could consider this patch too, or the (reduced functionality) version in this post: https://sqlite.org/forum/forumpost/e7a939e074

It prevents repeated "utc" and "localtime" modifiers from shifting the time repeatedly, recognizes "now" and "unixepoch" as UTC (so they work correctly with the modifiers) and, the more complex version, stores a useful timezone in p->tz when converting to "localtime" (which you could use to extend strftime to print the timezone).

I know it's external code, but as much as legally possible, I place it under the public domain. Feel free to adapt it.

docs should be changed too (when this is correct), they currently say: %W week of year: 00-53

https://www.sqlite.org/lang_datefunc.html#:~:text=with%20Sunday%3D%3D0-,%25W,week%20of%20year%3A%2000%2D53,-%25Y

The date4.test script checks the %V and %G results against libc for every one of the 24,858 days between 1970-01-01 and 2038-01-19, inclusive. They give the same result in all cases.

So, either SQLite is correct, or libc is wrong.

'%u' and '%V' do not seem to work

use this source on the latest version to test: https://dbfiddle.uk/sMjxkbg_

(DBFIDDLE is on 3.39, but I tested on 3.43.2 (Windows))

D:\TEMP>sqlite3
-- Loading resources from C:\Users\Luuk/.sqliterc
SQLite version 3.43.2 2023-10-10 12:14:04
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .headers on
sqlite> with dates as (
(x1...>   select strftime('%Y-%m-%d','1979-01-01') as d
(x1...>   union all
(x1...>   select date(d,'+1 day') from dates where d<=strftime('%Y-%m-%d','2023-01-10'))
   ...> select
   ...>   d,
   ...>   strftime('%W',d) as W,
   ...>   strftime('%V',d) as V,
   ...>   strftime('%u',d) as "u_1=Monday",
   ...>   strftime('%w',d) as "w_0=Sunday"
   ...> from dates
   ...> WHERE strftime('%W',d) in ('00','53')
   ...> ;
d           W   V  u_1=Monday  w_0=Sunday
----------  --  -  ----------  ----------
1979-12-31  53                 1
1980-01-01  00                 2
1980-01-02  00                 3
1980-01-03  00                 4
1980-01-04  00                 5
1980-01-05  00                 6
1980-01-06  00                 0
1981-01-01  00                 4
1981-01-02  00                 5
1981-01-03  00                 6
1981-01-04  00                 0
1982-01-01  00                 5
1982-01-02  00                 6
1982-01-03  00                 0
1983-01-01  00                 6
....
2014-01-01  00                 3
2014-01-02  00                 4
2014-01-03  00                 5
2014-01-04  00                 6
2014-01-05  00                 0
2015-01-01  00                 4
2015-01-02  00                 5
2015-01-03  00                 6
2015-01-04  00                 0
2016-01-01  00                 5
2016-01-02  00                 6
2016-01-03  00                 0
2017-01-01  00                 0
2018-12-31  53                 1
2019-01-01  00                 2
2019-01-02  00                 3
2019-01-03  00                 4
2019-01-04  00                 5
2019-01-05  00                 6
2019-01-06  00                 0
2020-01-01  00                 3
2020-01-02  00                 4
2020-01-03  00                 5
2020-01-04  00                 6
2020-01-05  00                 0
2021-01-01  00                 5
2021-01-02  00                 6
2021-01-03  00                 0
2022-01-01  00                 6
2022-01-02  00                 0
2023-01-01  00                 0
sqlite>

'%u' and '%V' do not seem to work ... (DBFIDDLE is on 3.39, but I tested on 3.43.2 (Windows))

%u and %V were only added two days ago. See src:e1155d6a.

Maybe try out your tests on https://sqlite.org/fiddle, which has all of the latest enhancements. Report any problems you find.

(Warning: This is completely off topic on this forum, but likely of interest to some. Stop reading now if you wish.)

The date November 24, 4714 BC will ring the creationists bell; it was the day the world was created. The reason for using this date way back has to do with the invention of the negative numbers. The reasoning for using this date (for which we have to thank Julius Scaliger) was that there is nothing before this date, and this avoided having to deal with negative numbers.

This account seems somewhat garbled to me. According to Wikipedia, and I think I have seen it elsewhere, the choice of that date is based on three cycles: The solar cycle: 28 years, as the Julian calendar repeats itself every 28 years; the metonic (or lunar) cycle: 19 years, as the dates of the phases of the moon repeat (approximately!) every 19 years; and, most cute of them all, the indiction cycle: 15 years, as taxes in ancient Rome was reassessed every 15 years (!). Noting that the numbers 28, 19, and 15 are pairwise mutually prime, their least common multiple is 28×19×15=7980, so all the cycles repeat every 7980 years (a Julan period).

It turns out the three cycles were all at their beginning together in the year 4713 BC, and so Julian day 0 was January 1, 4713 BC on the proleptic Julian calendar. Which becomes November 14, 47414 BC in the proleptic Gregorian calendar. (“Proleptic” here means you just extrapolate the respective calendars, leap years and all, into the past.)

Nowhere, as far as I can tell, was any notion of the creation of the world involved in this. I am well aware that some creationists have settled on a date roughly 6000 years in the past, however. Possibly, some of them might have locked onto Julian day 0, but I know of no evidence that creationist ideas inspired the choice of Julian day numbering.

But for sure, the desire to avoid negative numbers may well have inspired the choice of such an early date for the epoch. Even if the world existed before then, there were likely no recorded astronomical observations earlier thatn 4714 BC.

Oh, and if you can believe Wikipedia, it was Joseph Scalinger who came up with Julian days, not his father Julius. And he is supposed to have named it so not after his father, but from the Julian calendar. I have not dug deeper, but I too used to think it was named for Julius Scalinger.

That fix was made even before you posted. See https://sqlite.org/draft/lang_datefunc.html

I'll retire my patch as soon as 3.46 is released. :)

A bit of history, although it may seem off-topic, may shed some light on how time is measured and it is an interesting rabbit-hole.

The timescale offered by Jospehb Scaliger has nothing to do with the Gregorian calendar that was introduced on October the 15th of October 1582. The calendar replaced the old Julian (Roman) calendar. The document the “Inter Gravissimas pastoralis” (https://thelatinlibrary.com/gravissimas.html) stipulates:

Postremo, quoniam partim ob decem dies de mense octobris anni MDLXXXII (qui correctionis annus recte dici debet) exemptos, partim ob ternos etiam dies quolibet quadringentorum annorum spatio minime intercalandos, interrumpatur necesse est cyclus litterarum dominicalium XXVIII annorum ad hanc usque diem usitatus in Ecclesia Romana, volumus in eius locum substitui eumdem cyclum XXVIII annorum, ab eodem Lilio, tum ad dictam intercalandi bissexti in centesimis annis rationem, tum ad quamcumque anni solaris magnitudinem, accommodatum; ex quo littera dominicalis beneficio cycli solaris, aeque facile ac prius, ut in proprio canone explicatur, reperiri possit in perpetuum.

If you read the papal decree “Inter Gravissimas pastoralis” from Pope Gregorius you will discover that the reason for replacing the Julian calendar was:

1. Determine the correct date for the spring-equinox (this had also a religious reason!)
2. The correct date for the 14th moon directly after the spring-equinox (the religious reason is; determine the correct date for Easter)
3. Easter will be celebrated on the first Sunday after the 14th moon.

So the 4th of October 1582 was followed by the 15th of October 1582. You will also discover the rule that we skip the leap year every century rollover, except if the century modulus 400 is zero. The leap year wasn’t skipped in 1600 and 2000. But was skipped in 1700, 1800, and 1900.

Mr. Scaliger, a Calvinist (protestant) had a strong dislike for papal meddling, and he was not the only one! England and all of her American colonies adopted the Gregorian calendar in 1750. That is the reason we celebrate Christmas with Saint Nicolas, for which we have to thank the Dutch (← this is a different rabbit-hole).

Yes, mr. Scaliger also used the solar cycli (← Latin origin) for his timescale, that was also had a strong religious motive to determine the date when God created the world and that is based on the bible. That is why this timescale rings the creationists bell. The 6,000 years that is something that rings another bell. It was mentioned during the Monkey trials and it was a correction on what Scaliger originally had proposed.

There is also a timescale that uses the birthday of Jesus as the point of origin. This timescale introduced the anno domini nostri Jesu Christi (in the year of our Lord Jesus Christ) which is abbreviated to A.D. and ante anno domini nostri Jesu Christi (in the year before of our Lord Jesus Christ) which is abbreviated to B.C.

And yes, Joseph Scaliger named the timescale Julius to honor his father.

So 4717 B.C. is a different timescale than Julianday 0. It was Julius Cæsar that reformed the old Roman calendar in a strict solar calendar in about 46 B.C. The month July refers to Julius Cæsar and August refers to Augustus and that is why those month have the same length. The new year originally started on the first of March. That is why February is a bit odd, which is also a fossil of the calendar reforms proposed by Numa Pompillus.

And to get back on topic: That is why the ISO calendar was introduced because it is almost free of religious influences.

It is time to get out of this rabbit-hole …

There are exceptions in the calendar.

For example, if you want to check the algorithm to compute the date for Easter, you have to test for the exceptions. So, always test for the date of Easter 1943 and 1954.

Easter in 1943 was on the 25th of April and in 1954 was on the 18th of April. Two exceptions.

The year 2000 is also an exception. We didn't skip the leap year.

There is a difference between % and / and the use of div and ldiv. The operators % and / depend on the implementation, and div or ldiv do not depend on the implementation.

Assuming the C89 standard, let's analyze this code:

ISO week date specifiers.

https://sqlite.org/forum/forumpost/73d99e4497e8e6a7

--- sqlite3.c.orig +++ sqlite3.c @@ -1373,6 +1373,29 @@ static void strftimeFunc( sqlite3_str_appendchar(&sRes, 1, c); 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( cf=='G' ){
• sqlite3_str_appendf(&sRes,"%04d",y.Y);
• }else{
• int nDay; /* Number of days since 1st day of year */
• i64 tJD = y.iJD;
• 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;
• }

change nDay to a long which prevents a typecast later on. The statement nDay = (int)((tJD-y.iJD+43200000)/86400000) can be rewritten as: nDay = ldiv((tJD - y.iJD + 43200000), 86400000)

Since nDay is of type long (or int if you typecast the result) we have no use for the remainder, and therefore we use ldiv.

Let's analyze the part that determines if the Gregorian date 2021 January 5th is the new ISO year 2021 or still in week 53 of the ISO year 2020. Using fiddle:

.headers on .width 10 .mode column

-- compute ISO date for 2021 January 5th JD=2459219.5 and is stored in y(?) select (2459219.5 + 43200000)/86400000 as 'step 1', ((2459219.5 + 43200000)/86400000) % 7 as 'step 2', 3-((2459219.5 + 43200000)/86400000) % 7 as 'step 3', 86400000 * (3-((2459219.5 + 43200000)/86400000) % 7) as 'step 4' ;

Gives this result: 0.52846318 0.0 3.0 259200000.0

I fail to understand this, and it looks to me as a problem to determine the ISO year rollover.

I haven't studied your analysis in detail, but in response to this …

There is a difference between % and / and the use of div and ldiv. The operators % and / depend on the implementation, and div or ldiv do not depend on the implementation.

… I might remark that, as far as I know, the results of m%n and m/n are not implementation dependent if m is nonnegative and n is positive. And it has been my impression that the code in SQLite is taking some pains to ensure that. Am I wrong?

Now that we have ISO weeks in the output, it would be great also to have SQLite recognise a date string like '2021W52-6' (with or without an added timestamp) as an alias for '2022-01-01'.

Granted, ISO 8601 specifies too many ways to format a date, and I can understand the desire to support only one of them. But I think the notation using week and day numbers is sufficiently common to also merit inclusion.

(I think you meant to say that %G and %V where added recently. %u has been there for a while.)

What is meant with depending on the implementation?

Suppose you want only the integer part of the division; for example: int(47 / 17). What is the expected result? Will the result be 3 (the implementation rounds it off) or is it 2 (implementation throws the remainder away).

I referred to the C89 standard because of the possibility that legacy code is still being used by some library. It will not be the first time I got such a nasty surprise.

If the code is compliant with the C99 standard, then there is no difference between the result obtained by / or div of %.

But using div is telling the compiler explicitly to use the machine code div which is a very fast integer division that throws the remainder away.

I think that you are correct, but I will experiment a bit to test our assumption with SQLite fiddle.

I am also working on a faster and more efficient algorithm to compute the ISO date. The faster the better is my motto.

I don't have access to either the C89 standard or the C99 standard, as I believe either costs a veritable fortune. What I do have is the second edition of the Kernighan and Ritchie book, with its Appendix A, which is claimed to be based on the submitted proposal for the C89 standard. It says:

The binary / operator yields the quotient, and the % operator the remainder, of the division of the first operand by the second; if the second operand is 0, the result is undefined. Otherwise, it is always true that (a/b)*b + a%b is equal to a. If both operands are non-negative, then the remainder is non-negative and smaller than the divisor; if not, it is guaranteed only that the absolute value of the remainder is smaller than the absolute value of the divisor.

(A7.6; my emphasis.) That (and nothing more) is what I meant by not depending on the implementation.

Since you're unconvinced, the test now covers all days between the years 1500 and 2500, and compares against the Go standard library.

Instead of arguing that there are exceptions and implying my patch (or drh's) might be wrong, it'd be more helpful you could find a counter example where our patches actually fail? Then we'd have something to fix, which would be great.

PS: I ran the same tests (all days between the years 1500 and 2500, against the Go standard library) on drh's implementation, and it similarly passes the test (so his implementation agrees with both the C and the Go standard libraries). I hope that settles the matter.

I think libc is wrong or at least suspect.

I will be very interested to hear their response to this assertion. Please do feed back here.

The relevant section in freely accessible resources: an as close as possible to a C89 draft and an officially sanctioned (HTML version of a) C99 draft.

Generally, this SO question is a good resource.

I can't really understand what you want to show with this.

I'll just note that the column you've labeled "SQLite ISO weeks in year" is not the number of "ISO weeks" (sic) in (e.g.) the year 1904.

Rather, 53 is the "ISO week number" for the date 1904-01-01. You get 53 becase the "ISO week date" for 1904-01-01 is 1903-W53-5: it's the Friday (5) from the 53rd week, belonging to the 1903 "week-based year".

I can't really understand what you want to show with this.

I don°t understand it either, but it°s really very easy.

52 weeks is 364 days. A week is assigned to a given year if and only if at least four days of that week fall within the year. Do the math: For a year to have 53 weeks assigned to it, weeks 1 and 53 need to have at least four days each within the year, totalling eight days. Add to this the remaining 51 weeks, and you have 365 days.

This means that a non-leap year has 53 weeks assigned to it if, and only if, January 1 of that year is a Thursday. In leap years, you have an extra day of slack, so the requirement is for January 1 to be either a Wednesday or a Thursday.

The SQL expression strftime('%u',year||'-01-01') will get the (numerical) weekday of January 1 of a given year.

Simple, no?