Recurring Dates

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Recurring Dates

Stephen Chrzanowski
Does anyone have any decent functions or procedures that when given a date
and a recurring type that SQLite gives the current or next date?

Take for instance
- using today as the base date, a date of Aug 1, 2016 with a yearly
recurring date, I'd like to get Aug 1, 2018.
- using today as the base date, a date of Aug 1, 2016 with a weekly
recurring date, I'd like to get Sept 2, 2017.

I plan on feeding this function todays date, the target date (Theoretically
a constant), and the recursive type being daily, weekly, monthly and
yearly.  (Not interested in the weekend before the 15th of any month, kinda
deal).
_______________________________________________
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: Recurring Dates

David Raymond
For a procedure anyway, something like...

with recursive iterations (iteration, dateGuess) as (
values (1, '2016-08-01')
union all
select iteration + 1, date(dateGuess, '1 years') from iterations
where dateGuess < date('now'))
select dateGuess from iterations order by iteration desc limit 1;

dateGuess
2018-08-01



with recursive iterations (iteration, dateGuess) as (
values (1, '2016-08-01')
union all
select iteration + 1, date(dateGuess, '7 days') from iterations
where dateGuess < date('now'))
select dateGuess from iterations order by iteration desc limit 1;

dateGuess
2017-10-02

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Stephen Chrzanowski
Sent: Wednesday, September 27, 2017 10:15 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Recurring Dates

Does anyone have any decent functions or procedures that when given a date
and a recurring type that SQLite gives the current or next date?

Take for instance
- using today as the base date, a date of Aug 1, 2016 with a yearly
recurring date, I'd like to get Aug 1, 2018.
- using today as the base date, a date of Aug 1, 2016 with a weekly
recurring date, I'd like to get Sept 2, 2017.

I plan on feeding this function todays date, the target date (Theoretically
a constant), and the recursive type being daily, weekly, monthly and
yearly.  (Not interested in the weekend before the 15th of any month, kinda
deal).
_______________________________________________
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: Recurring Dates

Donald Griggs
In reply to this post by Stephen Chrzanowski
Hello Stephen,

Are the nice date/time features at https://sqlite.org/lang_datefunc.html
enough?

For example:

   select date ('2016-08-01', '+1 year');  -- gives 2017-08-01

Regarding:   "- using today as the base date, a date of Aug 1, 2016 with a
weekly
recurring date, I'd like to get Sept 2, 2017."

I'm not sure I understand.   If I run:
    select julianday(  '2017-09-02') - julianday('2016-08-01');
I get a difference of 397 days, which equals  56 weeks and 5 days -- not
evenly divisible by 7.

select 397.0 / 7;   -- gives 56.714...
select 397.0 % 7; -- gives 5

>
>
_______________________________________________
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: Recurring Dates

David Raymond
In reply to this post by Stephen Chrzanowski
Or pared down slightly...

with recursive dateGuesses (dateGuess) as (
values ('original-date-here')
union all
select date(dateGuess, '+1 years' / '+1 months' / '+7 days' / '+1 days')
from dateGuesses
where dateGuess < date('now'))
select max(dateGuess) as nextDate from dateGuesses;



with recursive dateGuesses (dateGuess) as (
values ('2016-08-01')
union all
select date(dateGuess, '+1 months')
from dateGuesses
where dateGuess < date('now'))
select max(dateGuess) as nextDate from dateGuesses;

nextDate
2017-10-01

-----Original Message-----
From: David Raymond
Sent: Wednesday, September 27, 2017 10:32 AM
To: 'SQLite mailing list'
Subject: RE: [sqlite] Recurring Dates

For a procedure anyway, something like...

with recursive iterations (iteration, dateGuess) as (
values (1, '2016-08-01')
union all
select iteration + 1, date(dateGuess, '1 years') from iterations
where dateGuess < date('now'))
select dateGuess from iterations order by iteration desc limit 1;

dateGuess
2018-08-01



with recursive iterations (iteration, dateGuess) as (
values (1, '2016-08-01')
union all
select iteration + 1, date(dateGuess, '7 days') from iterations
where dateGuess < date('now'))
select dateGuess from iterations order by iteration desc limit 1;

dateGuess
2017-10-02

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Stephen Chrzanowski
Sent: Wednesday, September 27, 2017 10:15 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Recurring Dates

Does anyone have any decent functions or procedures that when given a date
and a recurring type that SQLite gives the current or next date?

Take for instance
- using today as the base date, a date of Aug 1, 2016 with a yearly
recurring date, I'd like to get Aug 1, 2018.
- using today as the base date, a date of Aug 1, 2016 with a weekly
recurring date, I'd like to get Sept 2, 2017.

I plan on feeding this function todays date, the target date (Theoretically
a constant), and the recursive type being daily, weekly, monthly and
yearly.  (Not interested in the weekend before the 15th of any month, kinda
deal).
_______________________________________________
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: Recurring Dates

Stephen Chrzanowski
In reply to this post by Donald Griggs
From my original;




*Take for instance - using today as the base date, a date of Aug 1, 2016
with a yearly recurring date, I'd like to get Aug 1, 2018.- using today as
the base date, a date of Aug 1, 2016 with a weekly recurring date, I'd like
to get Sept 2, 2017.*
Today being Sept 27, 2017.
Aug 1, 2016 is a Monday
Aug 1, 2018 is a Wed, so week day change is valid. (Aug 1, 2017 is in the
past)
Sept 2, 2017 should have been Oct 2, 2017.  My bad.  The result I'd want is
Oct 2, 2017 which is a Monday.  I should have caught that, since Sept 2 is
in the past and I want the result to be >= Today.

The purpose of this is that I've built a calendar system at home, and I'm
tired of going into the history and reset birthdays to be visible, as well
as regular weekly events for my sons guitar lessons having to be recreated
regularly.  Either via PHP code or (very nicely done) SQL code by David,
which I've yet to test and build into my web page, needs to be built in.
With a single additional field to the table that indicates Daily, Weekly,
Monthly or Yearly (Maybe 2xWeek, 3xWeek, 4xWeek now that I have some hints
to what to do), and minimal change to the 'add date' page, this should help.

Thanks all.

On Wed, Sep 27, 2017 at 10:34 AM, Donald Griggs <[hidden email]> wrote:

> Hello Stephen,
> I'm not sure I understand.   If I run:
>     select julianday(  '2017-09-02') - julianday('2016-08-01');
> I get a difference of 397 days, which equals  56 weeks and 5 days -- not
> evenly divisible by 7.
>
> select 397.0 / 7;   -- gives 56.714...
> select 397.0 % 7; -- gives 5
>
> >
> >
> _______________________________________________
> 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: Recurring Dates

Stephen Chrzanowski
Check that...  'Week[s]' isn't supported.

I tested at the command line, works very well, thanks for the effort Dave.
Appreciated.  Now to understand how EXACTLY this works.

On Wed, Sep 27, 2017 at 12:30 PM, Stephen Chrzanowski <[hidden email]>
wrote:

> ..... that indicates Daily, Weekly, Monthly or Yearly (Maybe 2xWeek,
> 3xWeek, 4xWeek now that I have some hints to what to do).....
>
>
_______________________________________________
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: Recurring Dates

rpkelly
This post has NOT been accepted by the mailing list yet.
In reply to this post by Stephen Chrzanowski

I’m not a C programmer and have some code at:

 

https://github.com/breacsealgaire/Calendar_Calculations-Freebasic

 

From: Stephen Chrzanowski [via SQLite] [mailto:[hidden email]]
Sent: Wednesday, September 27, 2017 9:15 AM
To: rpkelly
Subject: Recurring Dates

 

Does anyone have any decent functions or procedures that when given a date
and a recurring type that SQLite gives the current or next date?

Take for instance
- using today as the base date, a date of Aug 1, 2016 with a yearly
recurring date, I'd like to get Aug 1, 2018.
- using today as the base date, a date of Aug 1, 2016 with a weekly
recurring date, I'd like to get Sept 2, 2017.

I plan on feeding this function todays date, the target date (Theoretically
a constant), and the recursive type being daily, weekly, monthly and
yearly.  (Not interested in the weekend before the 15th of any month, kinda
deal).
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


If you reply to this email, your message will be added to the discussion below:

http://sqlite.1065341.n5.nabble.com/Recurring-Dates-tp97838.html

To unsubscribe from SQLite, click here.
NAML

RPK