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

[Petite Abeille]

> On May 7, 2019, at 05:35, Jens Alfke  wrote:
> 
> [https://en.wikipedia.org/wiki/French_Republican_calendar]

Along the same lines: International Fixed Calendar

https://en.m.wikipedia.org/wiki/International_Fixed_Calendar

> You Advocate An Approach To Calendar Reform; Your Idea Will Not Work; Here Is 
> Why  (brutal takedown)

While at it:  So You Want To Abolish Time Zones

https://qntm.org/abolish



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jose Isaias Cabrera]

I apologize to the group for the non-sqlite emails caused by my post. It was 
just having a little fun. :-)

josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[R Smith]

On 2019/05/07 3:07 PM, Jose Isaias Cabrera wrote:

Warren Young, on Monday, May 6, 2019 09:15 PM, wrote...
On May 6, 2019, at 11:58 AM, Jose Isaias Cabrera wrote:

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

Okay, but one *what*?  Serious question.

Yeah, if I have to explain it to you, then you miss the idea. :-) But here is 
something: you, one Warren Young, are composed of billions of living small 
living cells, all working to your well being. You are one. :-)

By the way, there will be a time when time will no longer be, but instead, 
we'll have eternity. :-) My personal believe, of course.


The spectacular width by which Warren's point was missed aside, I did 
chuckle at the irony of quoting a John Lennon song that advocates for no 
Religion with the very next post claiming adherence. :)


If time doesn't exist then, how will you know it is eternity?
(It's rhetorical, no answer required).


PS: It's Trillions of small cells, not Billions - around 38 Trillion in 
a reference 70Kg male, with almost equal numbers (around 30 Trillion 
more) non-human cells.

https://www.smithsonianmag.com/smart-news/there-are-372-trillion-cells-in-your-body-4941473/
https://journals.plos.org/plosbiology/article?id=10.1371/journal.pbio.1002533

PPS: Time is nothing but a measurement of the rate of progression, the 
progression of an irreversible sequence of events or states along a 
continuum. Progression rate is also relative to a frame of reference, 
and thus, so is time. This we can agree on: One day, time/progression 
will cease to be; in your frame of reference; for eternity. :)

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


Cheers!
Ryan


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jose Isaias Cabrera]

Warren Young, on Monday, May 6, 2019 09:15 PM, wrote...
On May 6, 2019, at 11:58 AM, Jose Isaias Cabrera wrote:
>> someday, as John Lennon sang, "...the world will live as one." ;-)
>
> Okay, but one *what*?  Serious question.

Yeah, if I have to explain it to you, then you miss the idea. :-) But here is 
something: you, one Warren Young, are composed of billions of living small 
living cells, all working to your well being. You are one. :-)

By the way, there will be a time when time will no longer be, but instead, 
we'll have eternity. :-) My personal believe, of course.

josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jens Alfke]

> On May 6, 2019, at 6:15 PM, Warren Young  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 
 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 

 (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  (brutal takedown)

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Warren Young]

On May 6, 2019, at 11:58 AM, Jose Isaias Cabrera  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Damon]

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  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jose Isaias Cabrera]

Yes. :-), per month.

From: sqlite-users  on behalf of 
Petite Abeille 
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  wrote:
>
> something is wrong where one week only has 1 day

... per month :P

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Petite Abeille]

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

... per month :P

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jose Isaias Cabrera]

Petite Abeille, on Sunday, May 5, 2019 09:10 AM, wrote...
>> On May 4, 2019, at 21:24, Thomas Kurz  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Petite Abeille]

> On May 4, 2019, at 21:24, Thomas Kurz  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
  fromDayRange

  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
  fromDayRange
)
select  DateRange.*,
dense_rank() over( partition by year, month order by week ) as 
week_of_month
fromDateRange;


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

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

[Thomas Kurz]

> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Petite Abeille]

> On May 4, 2019, at 15:59, Luuk  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Petite Abeille]

> On May 4, 2019, at 12:47, Luuk  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Luuk]

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(;2) for the 31th december returns 53 (the second 
parameter is used to specify when a week starts)


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


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Tim Streater]

On 04 May 2019, at 09:35, Olivier Mascia  wrote:

>> Le 2 mai 2019 à 22:01, Thomas Kurz  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Olivier Mascia]

> Le 2 mai 2019 à 22:01, Thomas Kurz  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jose Isaias Cabrera]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jose Isaias Cabrera]

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 
To: sqlite-users@mailinglists.sqlite.org 
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
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Donald Griggs]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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 
To: sqlite-users@mailinglists.sqlite.org 
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
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Getting the week of the month from strftime or date functions

[Jose Isaias Cabrera]

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
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users