Getting the week of the month from strftime or date functions

classic Classic list List threaded Threaded
25 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Getting the week of the month from strftime or date functions

Jose Isaias Cabrera-4

Greetings.

To break Manuel's constant bug finding emails, :-), I want to get the week of the month from either date or strftime functions.  I know I can get the week of the year by doing,

 SELECT strftime('%W','2019-03-07');

but I need to get the week of that month based on the date.  I can write a quick function to do it, but I thought there was an option for it, but I couldn't find it in the help site[1].  Is there such a choice?

Thanks.

[1] https://www.sqlite.org/lang_datefunc.html

SQLite Query Language: Date And Time Functions<https://www.sqlite.org/lang_datefunc.html>
The only reasons for providing functions other than strftime() is for convenience and for efficiency. Time Strings. A time string can be in any of the following formats:
www.sqlite.org


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting the week of the month from strftime or date functions

Thomas Kurz
I think "week of the month" is not a standard value. As with week of the year, is week #1 the week in which the month starts, the first complete week within the month, or the first week with at least 4 days?


----- Original Message -----
From: Jose Isaias Cabrera <[hidden email]>
To: [hidden email] <[hidden email]>
Sent: Thursday, May 2, 2019, 21:44:44
Subject: [sqlite] Getting the week of the month from strftime or date functions


Greetings.

To break Manuel's constant bug finding emails, :-), I want to get the week of the month from either date or strftime functions.  I know I can get the week of the year by doing,

 SELECT strftime('%W','2019-03-07');

but I need to get the week of that month based on the date.  I can write a quick function to do it, but I thought there was an option for it, but I couldn't find it in the help site[1].  Is there such a choice?

Thanks.

[1] https://www.sqlite.org/lang_datefunc.html

SQLite Query Language: Date And Time Functions<https://www.sqlite.org/lang_datefunc.html>
The only reasons for providing functions other than strftime() is for convenience and for efficiency. Time Strings. A time string can be in any of the following formats:
www.sqlite.org


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting the week of the month from strftime or date functions

Donald Griggs
In reply to this post by Jose Isaias Cabrera-4
Hello Jose,

Regarding: "...but I need to get the week of that month based on the date."

One interpretation of your question might me:

Given a date "d", which, say, falls  on a Wednesday, then return
    1, 2, 3, 4, or 5 denoting whether d is on the 1st, 2nd, 3rd, 4th, or
5th Wednesday of that month.

If that's the question, then the sqlite (or C) expression:
     1 +   (d - 1) / 7

should do it (where the slash represent truncating integer division)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting the week of the month from strftime or date functions

Jose Isaias Cabrera-4
In reply to this post by Thomas Kurz

I found this very interesting,

15:52:46.71>sqlite3
SQLite version 3.28.0 2019-04-16 19:49:53
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

I expected 2019-01-01 to be part of week 1, since it was Tuesday.  So, back to the drawing board. ;-)  Thanks.




Thomas Kurz, on Thursday, May 2, 2019 04:01 PM, wrote...
To: SQLite mailing list
Subject: Re: [sqlite] Getting the week of the month from strftime or date functions

I think "week of the month" is not a standard value. As with week of the year, is week #1 the week in which the month starts, the first complete week within the month, or the first week with at least 4 days?


----- Original Message -----
From: Jose Isaias Cabrera <[hidden email]>
To: [hidden email] <[hidden email]>
Sent: Thursday, May 2, 2019, 21:44:44
Subject: [sqlite] Getting the week of the month from strftime or date functions


Greetings.

To break Manuel's constant bug finding emails, :-), I want to get the week of the month from either date or strftime functions.  I know I can get the week of the year by doing,

 SELECT strftime('%W','2019-03-07');

but I need to get the week of that month based on the date.  I can write a quick function to do it, but I thought there was an option for it, but I couldn't find it in the help site[1].  Is there such a choice?

Thanks.

[1] https://www.sqlite.org/lang_datefunc.html

SQLite Query Language: Date And Time Functions<https://www.sqlite.org/lang_datefunc.html>
The only reasons for providing functions other than strftime() is for convenience and for efficiency. Time Strings. A time string can be in any of the following formats:
www.sqlite.org<http://www.sqlite.org>


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting the week of the month from strftime or date functions

Jose Isaias Cabrera-4
In reply to this post by Donald Griggs


Yes, this will work.  It's a long story.  I am creating a Gantt visual schedule of a project based on the tasks dates, and I want to show the visual effects per weeks.  But, you have hit the hammer on the nail, as we say in Spanish. This I can use.

Donald Griggs, Thursday, May 2, 2019 04:16 PM, wrote...
To: SQLite mailing list
Subject: Re: [sqlite] Getting the week of the month from strftime or date functions

Hello Jose,

Regarding: "...but I need to get the week of that month based on the date."

One interpretation of your question might me:

Given a date "d", which, say, falls  on a Wednesday, then return
    1, 2, 3, 4, or 5 denoting whether d is on the 1st, 2nd, 3rd, 4th, or
5th Wednesday of that month.

If that's the question, then the sqlite (or C) expression:
     1 +   (d - 1) / 7

should do it (where the slash represent truncating integer division)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting the week of the month from strftime or date functions

Olivier Mascia
In reply to this post by Thomas Kurz
> Le 2 mai 2019 à 22:01, Thomas Kurz <[hidden email]> a écrit :
>
> I think "week of the month" is not a standard value. As with week of the year, is week #1 the week in which the month starts, the first complete week within the month, or the first week with at least 4 days?

These are very regional matters around our living globe.
In most European locations where ISO-8601 applies for that matter, a week starts on Monday and ends on Sunday (not Sunday to Saturday) and the week #1 of a year is the one week whose Thursday falls in that year (and the Monday can be in the previous year). This can lead to years sometimes with 53 weeks instead of 52.

Some past examples:

Monday 31 December 2007 until Sunday 6 January 2008  == Week 01/2008
Monday 28 December 2009 until Sunday 3 January 2010  == Week 53/2009
Monday 04 January 2010  until Sunday 10 January 2010 == Week 01/2010

I'm not implying whatever SQLite does through its date manipulation features should be done any differently. I'm just adding facts to the discussion (and I'm late reading the list these days). I'm used to never rely on any component way of computing dates but to rely on application logic, specific to the user's location.

Applying ISO-8601 way of thinking, the first week of a month could be the first one whose Thursday falls in that month.  But I don't remember to ever had to compute a week # of the month. People generally only rely on week # of the year when stating, for instance, "expect delivery within week #13".

—  
Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten Grüßen,
Olivier Mascia
https://www.integral.be


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting the week of the month from strftime or date functions

Tim Streater-3
On 04 May 2019, at 09:35, Olivier Mascia <[hidden email]> wrote:

>> Le 2 mai 2019 à 22:01, Thomas Kurz <[hidden email]> a écrit :
>>
>>  I think "week of the month" is not a standard value. As with week of the
>> year, is week #1 the week in which the month starts, the first complete week
>> within the month, or the first week with at least 4 days?
>
> These are very regional matters around our living globe.
> In most European locations where ISO-8601 applies for that matter, a week
> starts on Monday and ends on Sunday (not Sunday to Saturday) and the week #1
> of a year is the one week whose Thursday falls in that year (and the Monday
> can be in the previous year). This can lead to years sometimes with 53 weeks
> instead of 52.

It can also be a personal matter. For me, the week starts on Monday. For my wife, it starts on Sunday.



--
Cheers  --  Tim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting the week of the month from strftime or date functions

Luuk
In reply to this post by Jose Isaias Cabrera-4

On 2-5-2019 22:17, Jose Isaias Cabrera wrote:

> I found this very interesting,
>
> 15:52:46.71>sqlite3
> SQLite version 3.28.0 2019-04-16 19:49:53
> 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
>
> I expected 2019-01-01 to be part of week 1, since it was Tuesday.  So, back to the drawing board. ;-)  Thanks.
>
It's more complex than that

sqlite> select strftime('%W','2018-12-29');
52
sqlite> select strftime('%W','2018-12-30');
52
sqlite> select strftime('%W','2018-12-31');
53
sqlite> select strftime('%W','2019-01-01');
00
sqlite>


As others have noted, it's a question of definition, and which
definition do you follow?

- Does a week start on Sunday, or on Monday?
- Is week #1 the week in which the month starts, the first complete week
within the month, or the first week with at least 4 days?

Even EXCEL (Microsoft) has problems with this, that's why they
implemented server WEEKNUM functions

=WEEKNUM(<date>;2) for the 31th december returns 53 (the second
parameter is used to specify when a week starts)

=ISO.WEEKNUM(<date>) for the same date returns 1


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting the week of the month from strftime or date functions

Petite Abeille-2


> On May 4, 2019, at 12:47, Luuk <[hidden email]> wrote:
>
> As others have noted, it's a question of definition, and which definition do you follow?

What about just sticking with the ISO week definition?

https://en.wikipedia.org/wiki/ISO_week_date



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting the week of the month from strftime or date functions

Luuk

On 4-5-2019 15:21, Petite Abeille wrote:
>
>> On May 4, 2019, at 12:47, Luuk <[hidden email]> wrote:
>>
>> As others have noted, it's a question of definition, and which definition do you follow?
> What about just sticking with the ISO week definition?
>
> https://en.wikipedia.org/wiki/ISO_week_date

This is the 'standard' used here where i live, so i can accept that ;)


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting the week of the month from strftime or date functions

Petite Abeille-2


> On May 4, 2019, at 15:59, Luuk <[hidden email]> wrote:
>
> This is the 'standard' used here where i live, so i can accept that ;)

"The nice thing about standards is that you have so many to choose from."
-- Andrew Stuart "Andy" Tanenbaum

:P

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting the week of the month from strftime or date functions

Thomas Kurz
In reply to this post by Petite Abeille-2
> What about just sticking with the ISO week definition?
>
> https://en.wikipedia.org/wiki/ISO_week_date

From the document you cited:

"The ISO standard does not define any association of weeks to months."

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting the week of the month from strftime or date functions

Petite Abeille-2


> On May 4, 2019, at 21:24, Thomas Kurz <[hidden email]> wrote:
>
>> What about just sticking with the ISO week definition?
>>
>> https://en.wikipedia.org/wiki/ISO_week_date
>
> From the document you cited:
>
> "The ISO standard does not define any association of weeks to months."

True enough, even though one could convert a 'week of year' into a 'week of month':

with
DayRange( day, until )
as
(
  select '2019-01-01' as day,
         '2019-12-31' as until

  union all
  select  date( DayRange.day, '+1 day' ) as day,
          DayRange.until
  from    DayRange

  where   date( DayRange.day, '+1 day' ) <= DayRange.until
),
DateRange
as
(
  select  day,
          cast( strftime( '%Y', day ) as number ) as year,
          cast( strftime( '%m', day ) as number ) as month,
          cast( strftime( '%W', day ) as number ) as week -- where is '%V' when one needs it :P
  from    DayRange
)
select  DateRange.*,
        dense_rank() over( partition by year, month order by week ) as week_of_month
from    DateRange;


2019-01-01|2019|1|0|1
2019-01-02|2019|1|0|1
2019-01-03|2019|1|0|1
2019-01-04|2019|1|0|1
2019-01-05|2019|1|0|1
2019-01-06|2019|1|0|1
2019-01-07|2019|1|1|2
2019-01-08|2019|1|1|2
2019-01-09|2019|1|1|2
2019-01-10|2019|1|1|2
2019-01-11|2019|1|1|2
2019-01-12|2019|1|1|2
2019-01-13|2019|1|1|2
2019-01-14|2019|1|2|3
2019-01-15|2019|1|2|3
2019-01-16|2019|1|2|3
2019-01-17|2019|1|2|3
2019-01-18|2019|1|2|3
2019-01-19|2019|1|2|3
2019-01-20|2019|1|2|3
2019-01-21|2019|1|3|4
2019-01-22|2019|1|3|4
2019-01-23|2019|1|3|4
2019-01-24|2019|1|3|4
2019-01-25|2019|1|3|4
2019-01-26|2019|1|3|4
2019-01-27|2019|1|3|4
2019-01-28|2019|1|4|5
2019-01-29|2019|1|4|5
2019-01-30|2019|1|4|5
2019-01-31|2019|1|4|5
2019-02-01|2019|2|4|1
2019-02-02|2019|2|4|1
2019-02-03|2019|2|4|1
2019-02-04|2019|2|5|2
2019-02-05|2019|2|5|2
2019-02-06|2019|2|5|2
2019-02-07|2019|2|5|2
2019-02-08|2019|2|5|2
2019-02-09|2019|2|5|2
2019-02-10|2019|2|5|2
2019-02-11|2019|2|6|3
2019-02-12|2019|2|6|3
2019-02-13|2019|2|6|3
2019-02-14|2019|2|6|3
2019-02-15|2019|2|6|3
2019-02-16|2019|2|6|3
2019-02-17|2019|2|6|3
2019-02-18|2019|2|7|4
2019-02-19|2019|2|7|4
2019-02-20|2019|2|7|4
2019-02-21|2019|2|7|4
2019-02-22|2019|2|7|4
2019-02-23|2019|2|7|4
2019-02-24|2019|2|7|4
2019-02-25|2019|2|8|5
2019-02-26|2019|2|8|5
2019-02-27|2019|2|8|5
2019-02-28|2019|2|8|5
2019-03-01|2019|3|8|1
2019-03-02|2019|3|8|1
2019-03-03|2019|3|8|1
2019-03-04|2019|3|9|2
2019-03-05|2019|3|9|2
2019-03-06|2019|3|9|2
2019-03-07|2019|3|9|2
2019-03-08|2019|3|9|2
2019-03-09|2019|3|9|2
2019-03-10|2019|3|9|2
2019-03-11|2019|3|10|3
2019-03-12|2019|3|10|3
2019-03-13|2019|3|10|3
2019-03-14|2019|3|10|3
2019-03-15|2019|3|10|3
2019-03-16|2019|3|10|3
2019-03-17|2019|3|10|3
2019-03-18|2019|3|11|4
2019-03-19|2019|3|11|4
2019-03-20|2019|3|11|4
2019-03-21|2019|3|11|4
2019-03-22|2019|3|11|4
2019-03-23|2019|3|11|4
2019-03-24|2019|3|11|4
2019-03-25|2019|3|12|5
2019-03-26|2019|3|12|5
2019-03-27|2019|3|12|5
2019-03-28|2019|3|12|5
2019-03-29|2019|3|12|5
2019-03-30|2019|3|12|5
2019-03-31|2019|3|12|5
2019-04-01|2019|4|13|1
2019-04-02|2019|4|13|1
2019-04-03|2019|4|13|1
2019-04-04|2019|4|13|1
2019-04-05|2019|4|13|1
2019-04-06|2019|4|13|1
2019-04-07|2019|4|13|1
2019-04-08|2019|4|14|2
2019-04-09|2019|4|14|2
2019-04-10|2019|4|14|2
2019-04-11|2019|4|14|2
2019-04-12|2019|4|14|2
2019-04-13|2019|4|14|2
2019-04-14|2019|4|14|2
2019-04-15|2019|4|15|3
2019-04-16|2019|4|15|3
2019-04-17|2019|4|15|3
2019-04-18|2019|4|15|3
2019-04-19|2019|4|15|3
2019-04-20|2019|4|15|3
2019-04-21|2019|4|15|3
2019-04-22|2019|4|16|4
2019-04-23|2019|4|16|4
2019-04-24|2019|4|16|4
2019-04-25|2019|4|16|4
2019-04-26|2019|4|16|4
2019-04-27|2019|4|16|4
2019-04-28|2019|4|16|4
2019-04-29|2019|4|17|5
2019-04-30|2019|4|17|5
2019-05-01|2019|5|17|1
2019-05-02|2019|5|17|1
2019-05-03|2019|5|17|1
2019-05-04|2019|5|17|1
2019-05-05|2019|5|17|1
2019-05-06|2019|5|18|2
2019-05-07|2019|5|18|2
2019-05-08|2019|5|18|2
2019-05-09|2019|5|18|2
2019-05-10|2019|5|18|2
2019-05-11|2019|5|18|2
2019-05-12|2019|5|18|2
2019-05-13|2019|5|19|3
2019-05-14|2019|5|19|3
2019-05-15|2019|5|19|3
2019-05-16|2019|5|19|3
2019-05-17|2019|5|19|3
2019-05-18|2019|5|19|3
2019-05-19|2019|5|19|3
2019-05-20|2019|5|20|4
2019-05-21|2019|5|20|4
2019-05-22|2019|5|20|4
2019-05-23|2019|5|20|4
2019-05-24|2019|5|20|4
2019-05-25|2019|5|20|4
2019-05-26|2019|5|20|4
2019-05-27|2019|5|21|5
2019-05-28|2019|5|21|5
2019-05-29|2019|5|21|5
2019-05-30|2019|5|21|5
2019-05-31|2019|5|21|5
2019-06-01|2019|6|21|1
2019-06-02|2019|6|21|1
2019-06-03|2019|6|22|2
2019-06-04|2019|6|22|2
2019-06-05|2019|6|22|2
2019-06-06|2019|6|22|2
2019-06-07|2019|6|22|2
2019-06-08|2019|6|22|2
2019-06-09|2019|6|22|2
2019-06-10|2019|6|23|3
2019-06-11|2019|6|23|3
2019-06-12|2019|6|23|3
2019-06-13|2019|6|23|3
2019-06-14|2019|6|23|3
2019-06-15|2019|6|23|3
2019-06-16|2019|6|23|3
2019-06-17|2019|6|24|4
2019-06-18|2019|6|24|4
2019-06-19|2019|6|24|4
2019-06-20|2019|6|24|4
2019-06-21|2019|6|24|4
2019-06-22|2019|6|24|4
2019-06-23|2019|6|24|4
2019-06-24|2019|6|25|5
2019-06-25|2019|6|25|5
2019-06-26|2019|6|25|5
2019-06-27|2019|6|25|5
2019-06-28|2019|6|25|5
2019-06-29|2019|6|25|5
2019-06-30|2019|6|25|5
2019-07-01|2019|7|26|1
2019-07-02|2019|7|26|1
2019-07-03|2019|7|26|1
2019-07-04|2019|7|26|1
2019-07-05|2019|7|26|1
2019-07-06|2019|7|26|1
2019-07-07|2019|7|26|1
2019-07-08|2019|7|27|2
2019-07-09|2019|7|27|2
2019-07-10|2019|7|27|2
2019-07-11|2019|7|27|2
2019-07-12|2019|7|27|2
2019-07-13|2019|7|27|2
2019-07-14|2019|7|27|2
2019-07-15|2019|7|28|3
2019-07-16|2019|7|28|3
2019-07-17|2019|7|28|3
2019-07-18|2019|7|28|3
2019-07-19|2019|7|28|3
2019-07-20|2019|7|28|3
2019-07-21|2019|7|28|3
2019-07-22|2019|7|29|4
2019-07-23|2019|7|29|4
2019-07-24|2019|7|29|4
2019-07-25|2019|7|29|4
2019-07-26|2019|7|29|4
2019-07-27|2019|7|29|4
2019-07-28|2019|7|29|4
2019-07-29|2019|7|30|5
2019-07-30|2019|7|30|5
2019-07-31|2019|7|30|5
2019-08-01|2019|8|30|1
2019-08-02|2019|8|30|1
2019-08-03|2019|8|30|1
2019-08-04|2019|8|30|1
2019-08-05|2019|8|31|2
2019-08-06|2019|8|31|2
2019-08-07|2019|8|31|2
2019-08-08|2019|8|31|2
2019-08-09|2019|8|31|2
2019-08-10|2019|8|31|2
2019-08-11|2019|8|31|2
2019-08-12|2019|8|32|3
2019-08-13|2019|8|32|3
2019-08-14|2019|8|32|3
2019-08-15|2019|8|32|3
2019-08-16|2019|8|32|3
2019-08-17|2019|8|32|3
2019-08-18|2019|8|32|3
2019-08-19|2019|8|33|4
2019-08-20|2019|8|33|4
2019-08-21|2019|8|33|4
2019-08-22|2019|8|33|4
2019-08-23|2019|8|33|4
2019-08-24|2019|8|33|4
2019-08-25|2019|8|33|4
2019-08-26|2019|8|34|5
2019-08-27|2019|8|34|5
2019-08-28|2019|8|34|5
2019-08-29|2019|8|34|5
2019-08-30|2019|8|34|5
2019-08-31|2019|8|34|5
2019-09-01|2019|9|34|1
2019-09-02|2019|9|35|2
2019-09-03|2019|9|35|2
2019-09-04|2019|9|35|2
2019-09-05|2019|9|35|2
2019-09-06|2019|9|35|2
2019-09-07|2019|9|35|2
2019-09-08|2019|9|35|2
2019-09-09|2019|9|36|3
2019-09-10|2019|9|36|3
2019-09-11|2019|9|36|3
2019-09-12|2019|9|36|3
2019-09-13|2019|9|36|3
2019-09-14|2019|9|36|3
2019-09-15|2019|9|36|3
2019-09-16|2019|9|37|4
2019-09-17|2019|9|37|4
2019-09-18|2019|9|37|4
2019-09-19|2019|9|37|4
2019-09-20|2019|9|37|4
2019-09-21|2019|9|37|4
2019-09-22|2019|9|37|4
2019-09-23|2019|9|38|5
2019-09-24|2019|9|38|5
2019-09-25|2019|9|38|5
2019-09-26|2019|9|38|5
2019-09-27|2019|9|38|5
2019-09-28|2019|9|38|5
2019-09-29|2019|9|38|5
2019-09-30|2019|9|39|6
2019-10-01|2019|10|39|1
2019-10-02|2019|10|39|1
2019-10-03|2019|10|39|1
2019-10-04|2019|10|39|1
2019-10-05|2019|10|39|1
2019-10-06|2019|10|39|1
2019-10-07|2019|10|40|2
2019-10-08|2019|10|40|2
2019-10-09|2019|10|40|2
2019-10-10|2019|10|40|2
2019-10-11|2019|10|40|2
2019-10-12|2019|10|40|2
2019-10-13|2019|10|40|2
2019-10-14|2019|10|41|3
2019-10-15|2019|10|41|3
2019-10-16|2019|10|41|3
2019-10-17|2019|10|41|3
2019-10-18|2019|10|41|3
2019-10-19|2019|10|41|3
2019-10-20|2019|10|41|3
2019-10-21|2019|10|42|4
2019-10-22|2019|10|42|4
2019-10-23|2019|10|42|4
2019-10-24|2019|10|42|4
2019-10-25|2019|10|42|4
2019-10-26|2019|10|42|4
2019-10-27|2019|10|42|4
2019-10-28|2019|10|43|5
2019-10-29|2019|10|43|5
2019-10-30|2019|10|43|5
2019-10-31|2019|10|43|5
2019-11-01|2019|11|43|1
2019-11-02|2019|11|43|1
2019-11-03|2019|11|43|1
2019-11-04|2019|11|44|2
2019-11-05|2019|11|44|2
2019-11-06|2019|11|44|2
2019-11-07|2019|11|44|2
2019-11-08|2019|11|44|2
2019-11-09|2019|11|44|2
2019-11-10|2019|11|44|2
2019-11-11|2019|11|45|3
2019-11-12|2019|11|45|3
2019-11-13|2019|11|45|3
2019-11-14|2019|11|45|3
2019-11-15|2019|11|45|3
2019-11-16|2019|11|45|3
2019-11-17|2019|11|45|3
2019-11-18|2019|11|46|4
2019-11-19|2019|11|46|4
2019-11-20|2019|11|46|4
2019-11-21|2019|11|46|4
2019-11-22|2019|11|46|4
2019-11-23|2019|11|46|4
2019-11-24|2019|11|46|4
2019-11-25|2019|11|47|5
2019-11-26|2019|11|47|5
2019-11-27|2019|11|47|5
2019-11-28|2019|11|47|5
2019-11-29|2019|11|47|5
2019-11-30|2019|11|47|5
2019-12-01|2019|12|47|1
2019-12-02|2019|12|48|2
2019-12-03|2019|12|48|2
2019-12-04|2019|12|48|2
2019-12-05|2019|12|48|2
2019-12-06|2019|12|48|2
2019-12-07|2019|12|48|2
2019-12-08|2019|12|48|2
2019-12-09|2019|12|49|3
2019-12-10|2019|12|49|3
2019-12-11|2019|12|49|3
2019-12-12|2019|12|49|3
2019-12-13|2019|12|49|3
2019-12-14|2019|12|49|3
2019-12-15|2019|12|49|3
2019-12-16|2019|12|50|4
2019-12-17|2019|12|50|4
2019-12-18|2019|12|50|4
2019-12-19|2019|12|50|4
2019-12-20|2019|12|50|4
2019-12-21|2019|12|50|4
2019-12-22|2019|12|50|4
2019-12-23|2019|12|51|5
2019-12-24|2019|12|51|5
2019-12-25|2019|12|51|5
2019-12-26|2019|12|51|5
2019-12-27|2019|12|51|5
2019-12-28|2019|12|51|5
2019-12-29|2019|12|51|5
2019-12-30|2019|12|52|6
2019-12-31|2019|12|52|6



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting the week of the month from strftime or date functions

Jose Isaias Cabrera-4

Petite Abeille, on Sunday, May 5, 2019 09:10 AM, wrote...
>> On May 4, 2019, at 21:24, Thomas Kurz <[hidden email]> wrote:

>True enough, even though one could convert a 'week of year' into a 'week of month':

[clip]

> 2019-11-30|2019|11|47|5
> 2019-12-01|2019|12|47|1  <--
> 2019-12-02|2019|12|48|2
> 2019-12-03|2019|12|48|2
> 2019-12-04|2019|12|48|2

Not that I want to continue with this subject, but something is wrong where one week only has 1 day.  I know all the arguments about dates and countries and, etc., but we have discover DNA; shouldn't we have the knowledge to come up with a dating system that should work for the world. :-)  Yes, I know.  It's probably why we still have wars.  But, I am one of those that believes that someday, as John Lennon sang, "...the world will live as one." ;-)

Happy dating...

josé


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting the week of the month from strftime or date functions

Petite Abeille-2


> On May 6, 2019, at 19:58, Jose Isaias Cabrera <[hidden email]> wrote:
>
> something is wrong where one week only has 1 day

... per month :P

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting the week of the month from strftime or date functions

Jose Isaias Cabrera-4


Yes. :-), per month.

From: sqlite-users <[hidden email]> on behalf of Petite Abeille <[hidden email]>
Sent: Monday, May 6, 2019 03:57 PM
To: SQLite mailing list
Subject: Re: [sqlite] Getting the week of the month from strftime or date functions



> On May 6, 2019, at 19:58, Jose Isaias Cabrera <[hidden email]> wrote:
>
> something is wrong where one week only has 1 day

... per month :P

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting the week of the month from strftime or date functions

Richard Damon
In reply to this post by Jose Isaias Cabrera-4
On 5/6/19 1:58 PM, Jose Isaias Cabrera wrote:

> Petite Abeille, on Sunday, May 5, 2019 09:10 AM, wrote...
>>> On May 4, 2019, at 21:24, Thomas Kurz <[hidden email]> wrote:
>> True enough, even though one could convert a 'week of year' into a 'week of month':
> [clip]
>
>> 2019-11-30|2019|11|47|5
>> 2019-12-01|2019|12|47|1  <--
>> 2019-12-02|2019|12|48|2
>> 2019-12-03|2019|12|48|2
>> 2019-12-04|2019|12|48|2
> Not that I want to continue with this subject, but something is wrong where one week only has 1 day.  I know all the arguments about dates and countries and, etc., but we have discover DNA; shouldn't we have the knowledge to come up with a dating system that should work for the world. :-)  Yes, I know.  It's probably why we still have wars.  But, I am one of those that believes that someday, as John Lennon sang, "...the world will live as one." ;-)
>
> Happy dating...
>
> josé
It depends a lot on how you want to define a 'week' and what you are
going to use it for. If printing a traditional calendar, a one day week
makes a lot of sense. The first week is the first row of the calendar,
the second week is the second row, and so on.

--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting the week of the month from strftime or date functions

Warren Young
In reply to this post by Jose Isaias Cabrera-4
On May 6, 2019, at 11:58 AM, Jose Isaias Cabrera <[hidden email]> wrote:
>
> we have discover DNA; shouldn't we have the knowledge to come up with a dating system that should work for the world. :-)

The Earth year doesn’t divide evenly by Earth days.  No matter what you do, the solution *will* be messy.

Even the concept “Earth year” is variable:

   https://en.wikipedia.org/wiki/Year#Variation_in_the_length_of_the_year_and_the_day

Ideas for fixing this aren’t new.  Start here and follow the links and references from there:

    https://en.wikipedia.org/wiki/Symmetry454

When you get bored with that, start here and repeat:

   https://en.wikipedia.org/wiki/Universal_language

> someday, as John Lennon sang, "...the world will live as one." ;-)

Okay, but one *what*?  Serious question.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting the week of the month from strftime or date functions

Jens Alfke-2


> On May 6, 2019, at 6:15 PM, Warren Young <[hidden email]> wrote:
>
> Ideas for fixing this aren’t new.

The French had a supremely utopian "Republican Calendar" that lasted from 1793 to 1805 ("and for 18 days by the Paris Commune <https://en.wikipedia.org/wiki/Paris_Commune> in 1871" … such pathos in that little aside.)

> There were twelve months, each divided into three ten-day weeks called décades. The tenth day, décadi, replaced Sunday as the day of rest and festivity. The five or six extra days needed to approximate the solar or tropical year were placed after the months at the end of each year and called complementary days. … Each day in the Republican Calendar was divided into ten hours, each hour into 100 decimal minutes, and each decimal minute into 100 decimal seconds."

[https://en.wikipedia.org/wiki/French_Republican_calendar]

Face it, if they couldn't ram through a pointy-headed decimalized regularized calendar during the effin' *French Enlightenment*, it's certainly not going to work in the current dark ages.

Also relevant to this entire thread, since apparently a lot of people aren't aware of this stuff:

Falsehoods Programmers Believe About Time <https://infiniteundo.com/post/25326999628/falsehoods-programmers-believe-about-time> (really a must-read for anyone dealing with dates and times)
You Advocate An Approach To Calendar Reform; Your Idea Will Not Work; Here Is Why <https://qntm.org/calendar> (brutal takedown)

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Getting the week of the month from strftime or date functions

Hick Gunter
Even the concept of "year" is subject to differing religious and cultural viewpoints, with some traditions still insisting on a lunar calendar with the corresponding shift of seasons by 11 days each year. And in one case, the length of a month depending on the weather conditions and the eyesight of the guy who happens to call the months. Pity the maya calendar didn't catch on. One "day number" wraparound every 4000 years sounds great (until you are the one who has to fix the coding that assumed it would "never happen")

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Jens Alfke
Gesendet: Dienstag, 07. Mai 2019 05:36
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] Getting the week of the month from strftime or date functions



> On May 6, 2019, at 6:15 PM, Warren Young <[hidden email]> wrote:
>
> Ideas for fixing this aren’t new.

The French had a supremely utopian "Republican Calendar" that lasted from 1793 to 1805 ("and for 18 days by the Paris Commune <https://en.wikipedia.org/wiki/Paris_Commune> in 1871" … such pathos in that little aside.)

> There were twelve months, each divided into three ten-day weeks called décades. The tenth day, décadi, replaced Sunday as the day of rest and festivity. The five or six extra days needed to approximate the solar or tropical year were placed after the months at the end of each year and called complementary days. … Each day in the Republican Calendar was divided into ten hours, each hour into 100 decimal minutes, and each decimal minute into 100 decimal seconds."

[https://en.wikipedia.org/wiki/French_Republican_calendar]

Face it, if they couldn't ram through a pointy-headed decimalized regularized calendar during the effin' *French Enlightenment*, it's certainly not going to work in the current dark ages.

Also relevant to this entire thread, since apparently a lot of people aren't aware of this stuff:

Falsehoods Programmers Believe About Time <https://infiniteundo.com/post/25326999628/falsehoods-programmers-believe-about-time> (really a must-read for anyone dealing with dates and times) You Advocate An Approach To Calendar Reform; Your Idea Will Not Work; Here Is Why <https://qntm.org/calendar> (brutal takedown)

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12