datetime result help

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

datetime result help

jose isaias cabrera

Greetings!

I am trying to calculate a date using sqlite date and time functions.  Perhaps, one of you GURUs can help this poor soul.  I have been trying to figure it out, but I know I am lack the understanding.  I read the documentation for it,

https://www.sqlite.org/lang_datefunc.html

but I can not seem to find the way to do this.  The idea is this one, there will be an input of hours, which in this case will be 5.5 hours, that will be added to the actual time.  For example, if it's 10AM, adding 5.5 hours will give 3:30 PM.  This is easy,

datetime('now','localtime','+5.5 hours')

the problem happens if it is after 5PM. For example, say it is 2PM, adding 5.5 hours to it will give 7:30PM.  What I am trying to do is to add the amount of hours over 5PM, which is,

time('now','localtime','+5.5 hours') - '17:00:00'

to 8.5 hours to the start of the next day and select that date and time.  So, I thought that if I concatenate the result to the with to '+XXX hours' it would work.  But, you all probably know the result.  So, here is what I have tried last:

select CASE
  WHEN time('now','localtime','+5.5 hours') > '17:00:00' THEN
    datetime('now','localtime','+1 day','start of day','+8.5 hours','+' || time('now','localtime','+5.5 hours') - '17:00:00' || ' hours')
  ELSE
    datetime('now','localtime','+5.5 hours')
  END;

but I get nothing for result:

sqlite> select CASE
   ...>   WHEN time('now','localtime','+5.5 hours') > '17:00:00' THEN
   ...>     datetime('now','start of day','+1 day','+8.5 hours','+' || time('now
','+5.5 hours','localtime') - '17:00:00' || ' hours')
   ...>   else
   ...>     datetime('now','localtime','+5.5 hours')
   ...>   END;


sqlite>

Any help would be greatly appreciated.  Thanks.

_______________________________________________
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: datetime result help

Richard Hipp-3
On 7/29/15, jose isaias cabrera <[hidden email]> wrote:

>
> Greetings!
>
> I am trying to calculate a date using sqlite date and time functions.
> Perhaps, one of you GURUs can help this poor soul.  I have been trying to
> figure it out, but I know I am lack the understanding.  I read the
> documentation for it,
>
> https://www.sqlite.org/lang_datefunc.html
>
> but I can not seem to find the way to do this.  The idea is this one, there
> will be an input of hours, which in this case will be 5.5 hours, that will
> be added to the actual time.  For example, if it's 10AM, adding 5.5 hours
> will give 3:30 PM.  This is easy,
>
> datetime('now','localtime','+5.5 hours')
>
> the problem happens if it is after 5PM. For example, say it is 2PM, adding
> 5.5 hours to it will give 7:30PM.  What I am trying to do is to add the
> amount of hours over 5PM, which is,
>
> time('now','localtime','+5.5 hours') - '17:00:00'
>
> to 8.5 hours to the start of the next day and select that date and time.

SELECT
  CASE WHEN time('now','+5.5 hours','localtime') > '17:00:00'
       THEN datetime('now','+21.0 hours','localtime')
       ELSE datetime('now','+5.5 hours','localtime') END;

--
D. Richard Hipp
[hidden email]
_______________________________________________
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: datetime result help

R Smith
In reply to this post by jose isaias cabrera


On 2015-07-29 06:34 PM, jose isaias cabrera wrote:

> Greetings!
>
> I am trying to calculate a date using sqlite date and time functions.  Perhaps, one of you GURUs can help this poor soul.  I have been trying to figure it out, but I know I am lack the understanding.  I read the documentation for it,
>
> https://www.sqlite.org/lang_datefunc.html
>
> but I can not seem to find the way to do this.  The idea is this one, there will be an input of hours, which in this case will be 5.5 hours, that will be added to the actual time.  For example, if it's 10AM, adding 5.5 hours will give 3:30 PM.  This is easy,
>
> datetime('now','localtime','+5.5 hours')
>
> the problem happens if it is after 5PM. For example, say it is 2PM, adding 5.5 hours to it will give 7:30PM.  What I am trying to do is to add the amount of hours over 5PM, which is,
>
> time('now','localtime','+5.5 hours') - '17:00:00'
>
> to 8.5 hours to the start of the next day and select that date and time.

Finally, an interesting question :)

It's easy to do, but you need to think of time in days:


WITH DTVals( EndOfToday, NewTimeToday, NewTimeTomorrow ) AS (
   SELECT
     datetime( 'now', 'localtime', 'start of day', '+17 hours' ),      
// End of Today
     datetime( 'now', 'localtime', '+5.5 hours' ),                      
// New time if today
     datetime( 'now', 'localtime', 'start of day', '+1 day', '+8.5
hours', '+5.5 hours' )    // New time if tomorrow
)
SELECT
   CASE WHEN NewTimeToday <= EndOfToday THEN NewTimeToday ELSE
NewTimeTomorrow END
FROM DTVals;


The whole CTE isn't necessary, it's just to show more clear what is
happening. Once you understand why the  above works, the calculation can
be optimized a lot and made faster.
(All those spaces are just for clarity, they may be omitted)


Cheers,
Ryan


>   So, I thought that if I concatenate the result to the with to '+XXX hours' it would work.  But, you all probably know the result.  So, here is what I have tried last:
>
> select CASE
>    WHEN time('now','localtime','+5.5 hours') > '17:00:00' THEN
>      datetime('now','localtime','+1 day','start of day','+8.5 hours','+' || time('now','localtime','+5.5 hours') - '17:00:00' || ' hours')
>    ELSE
>      datetime('now','localtime','+5.5 hours')
>    END;
>
> but I get nothing for result:
>
> sqlite> select CASE
>     ...>   WHEN time('now','localtime','+5.5 hours') > '17:00:00' THEN
>     ...>     datetime('now','start of day','+1 day','+8.5 hours','+' || time('now
> ','+5.5 hours','localtime') - '17:00:00' || ' hours')
>     ...>   else
>     ...>     datetime('now','localtime','+5.5 hours')
>     ...>   END;
>
>
> sqlite>
>
> Any help would be greatly appreciated.  Thanks.
>
> _______________________________________________
> 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: datetime result help

jose isaias cabrera
In reply to this post by Richard Hipp-3

Doctor Hipp wrote...

> On 7/29/15, jose isaias cabrera <[hidden email]> wrote:
>>
>> Greetings!
>>
>> I am trying to calculate a date using sqlite date and time functions.
>> Perhaps, one of you GURUs can help this poor soul.  I have been trying to
>> figure it out, but I know I am lack the understanding.  I read the
>> documentation for it,
>>
>> https://www.sqlite.org/lang_datefunc.html
>>
>> but I can not seem to find the way to do this.  The idea is this one,
>> there
>> will be an input of hours, which in this case will be 5.5 hours, that
>> will
>> be added to the actual time.  For example, if it's 10AM, adding 5.5 hours
>> will give 3:30 PM.  This is easy,
>>
>> datetime('now','localtime','+5.5 hours')
>>
>> the problem happens if it is after 5PM. For example, say it is 2PM,
>> adding
>> 5.5 hours to it will give 7:30PM.  What I am trying to do is to add the
>> amount of hours over 5PM, which is,
>>
>> time('now','localtime','+5.5 hours') - '17:00:00'
>>
>> to 8.5 hours to the start of the next day and select that date and time.
>
> SELECT
>  CASE WHEN time('now','+5.5 hours','localtime') > '17:00:00'
>       THEN datetime('now','+21.0 hours','localtime')
>       ELSE datetime('now','+5.5 hours','localtime') END;
>
So, I will have to do two select, one for the subtraction of the hours over
5.5 and the other select for the actual date desired.  Ok, thanks.  I
thought it could be done all in one entry.

thanks.

_______________________________________________
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: datetime result help

R Smith
In reply to this post by R Smith
Oops, rereading your message it seems you need only the difference
carried over to the next day, not schedule the entire time-slot on the
next day, this will work better for that:

WITH DTVals( EndOfToday, NewTimeToday, NewTimeTomorrow ) AS (
   SELECT
     datetime( 'now', 'start of day', '+17 hours', 'localtime'),
     datetime( 'now', '+5.5 hours', 'localtime'),
     datetime( 'now', '+5.5 hours', '+15.5 hours', 'localtime')
)
SELECT
   CASE WHEN NewTimeToday <= EndOfToday THEN NewTimeToday ELSE
NewTimeTomorrow END
FROM DTVals;


(The difference between today 17:00 and tomorrow morning 08:30 is 15.5
hours, hence...)

On 2015-07-29 07:29 PM, R.Smith wrote:

>
>
> On 2015-07-29 06:34 PM, jose isaias cabrera wrote:
>> Greetings!
>>
>> I am trying to calculate a date using sqlite date and time
>> functions.  Perhaps, one of you GURUs can help this poor soul. I have
>> been trying to figure it out, but I know I am lack the
>> understanding.  I read the documentation for it,
>>
>> https://www.sqlite.org/lang_datefunc.html
>>
>> but I can not seem to find the way to do this.  The idea is this one,
>> there will be an input of hours, which in this case will be 5.5
>> hours, that will be added to the actual time.  For example, if it's
>> 10AM, adding 5.5 hours will give 3:30 PM.  This is easy,
>>
>> datetime('now','localtime','+5.5 hours')
>>
>> the problem happens if it is after 5PM. For example, say it is 2PM,
>> adding 5.5 hours to it will give 7:30PM.  What I am trying to do is
>> to add the amount of hours over 5PM, which is,
>>
>> time('now','localtime','+5.5 hours') - '17:00:00'
>>
>> to 8.5 hours to the start of the next day and select that date and time.
>
> Finally, an interesting question :)
>
> It's easy to do, but you need to think of time in days:
>
>
> WITH DTVals( EndOfToday, NewTimeToday, NewTimeTomorrow ) AS (
>   SELECT
>     datetime( 'now', 'localtime', 'start of day', '+17 hours' ),      
> // End of Today
>     datetime( 'now', 'localtime', '+5.5 hours' ),                      
> // New time if today
>     datetime( 'now', 'localtime', 'start of day', '+1 day', '+8.5
> hours', '+5.5 hours' )    // New time if tomorrow
> )
> SELECT
>   CASE WHEN NewTimeToday <= EndOfToday THEN NewTimeToday ELSE
> NewTimeTomorrow END
> FROM DTVals;
>
>
> The whole CTE isn't necessary, it's just to show more clear what is
> happening. Once you understand why the  above works, the calculation
> can be optimized a lot and made faster.
> (All those spaces are just for clarity, they may be omitted)
>
>
> Cheers,
> Ryan
>
>
>>   So, I thought that if I concatenate the result to the with to '+XXX
>> hours' it would work.  But, you all probably know the result.  So,
>> here is what I have tried last:
>>
>> select CASE
>>    WHEN time('now','localtime','+5.5 hours') > '17:00:00' THEN
>>      datetime('now','localtime','+1 day','start of day','+8.5
>> hours','+' || time('now','localtime','+5.5 hours') - '17:00:00' || '
>> hours')
>>    ELSE
>>      datetime('now','localtime','+5.5 hours')
>>    END;
>>
>> but I get nothing for result:
>>
>> sqlite> select CASE
>>     ...>   WHEN time('now','localtime','+5.5 hours') > '17:00:00' THEN
>>     ...>     datetime('now','start of day','+1 day','+8.5 hours','+'
>> || time('now
>> ','+5.5 hours','localtime') - '17:00:00' || ' hours')
>>     ...>   else
>>     ...>     datetime('now','localtime','+5.5 hours')
>>     ...>   END;
>>
>>
>> sqlite>
>>
>> Any help would be greatly appreciated.  Thanks.
>>
>> _______________________________________________
>> 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: datetime result help

R Smith
In reply to this post by jose isaias cabrera


On 2015-07-29 08:00 PM, jose isaias cabrera wrote:

>
> Doctor Hipp wrote...
>
>> On 7/29/15, jose isaias cabrera <[hidden email]> wrote:
>>
>> SELECT
>>  CASE WHEN time('now','+5.5 hours','localtime') > '17:00:00'
>>       THEN datetime('now','+21.0 hours','localtime')
>>       ELSE datetime('now','+5.5 hours','localtime') END;
>>
> So, I will have to do two select, one for the subtraction of the hours
> over 5.5 and the other select for the actual date desired. Ok,
> thanks.  I thought it could be done all in one entry.

No, it can be done in one select - Dr. Hipp's suggestion is just 1
select, how do you see 2 selects?

Are the hours or some other thing in your query variable?
Maybe give us your actual query and we could understand and help better.


_______________________________________________
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: datetime result help

jose isaias cabrera
In reply to this post by R Smith
"R.Smith" wrote,

> On 2015-07-29 06:34 PM, jose isaias cabrera wrote:
>> Greetings!
>>
>> I am trying to calculate a date using sqlite date and time functions.
>> Perhaps, one of you GURUs can help this poor soul.  I have been trying to
>> figure it out, but I know I am lack the understanding.  I read the
>> documentation for it,
>>
>> https://www.sqlite.org/lang_datefunc.html
>>
>> but I can not seem to find the way to do this.  The idea is this one,
>> there will be an input of hours, which in this case will be 5.5 hours,
>> that will be added to the actual time.  For example, if it's 10AM, adding
>> 5.5 hours will give 3:30 PM.  This is easy,
>>
>> datetime('now','localtime','+5.5 hours')
>>
>> the problem happens if it is after 5PM. For example, say it is 2PM,
>> adding 5.5 hours to it will give 7:30PM.  What I am trying to do is to
>> add the amount of hours over 5PM, which is,
>>
>> time('now','localtime','+5.5 hours') - '17:00:00'
>>
>> to 8.5 hours to the start of the next day and select that date and time.
>
> Finally, an interesting question :)
>
> It's easy to do, but you need to think of time in days:
>
>
> WITH DTVals( EndOfToday, NewTimeToday, NewTimeTomorrow ) AS (
>   SELECT
>     datetime( 'now', 'localtime', 'start of day', '+17 hours' ),       //
> End of Today
>     datetime( 'now', 'localtime', '+5.5 hours' ),                      //
> New time if today
>     datetime( 'now', 'localtime', 'start of day', '+1 day', '+8.5 hours',
> '+5.5 hours' )    // New time if tomorrow
> )
> SELECT
>   CASE WHEN NewTimeToday <= EndOfToday THEN NewTimeToday ELSE
> NewTimeTomorrow END
> FROM DTVals;
>
>
> The whole CTE isn't necessary, it's just to show more clear what is
> happening. Once you understand why the  above works, the calculation can
> be optimized a lot and made faster.
> (All those spaces are just for clarity, they may be omitted)

The above almost works.  NewTimeTomorrow needs to have the amount of hours
over 17:00:00. as of right now you have it as,

datetime( 'now', 'localtime', 'start of day', '+1 day', '+8.5 hours', '+5.5
hours' )    // New time if tomorrow

which is not correct. :-) .  The last entry, '+5.5 hours' should  be the
result of

time('now','localtime','+5.5 hours') - '17:00:00'

So, if time now is 15:00:00, then, NewTimeTomorrow would be,

datetime( 'now', 'localtime', 'start of day', '+1 day', '+8.5 hours', '+3.5
hours' )

So, can this be possible.  I should probably say that 5.5 hours is not
always 5.5 hours.  This is a variable piece of data.  So, here is your
change again to work something interesting again. :-)  Thanks.

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: datetime result help

jose isaias cabrera
In reply to this post by R Smith

"R.Smith" wrote...

>
> On 2015-07-29 08:00 PM, jose isaias cabrera wrote:
>>
>> Doctor Hipp wrote...
>>
>>> On 7/29/15, jose isaias cabrera <[hidden email]> wrote:
>>>
>>> SELECT
>>>  CASE WHEN time('now','+5.5 hours','localtime') > '17:00:00'
>>>       THEN datetime('now','+21.0 hours','localtime')
>>>       ELSE datetime('now','+5.5 hours','localtime') END;
>>>
>> So, I will have to do two select, one for the subtraction of the hours
>> over 5.5 and the other select for the actual date desired. Ok, thanks.  I
>> thought it could be done all in one entry.
>
> No, it can be done in one select - Dr. Hipp's suggestion is just 1 select,
> how do you see 2 selects?

he calculated the 21 hours.  But the 5.5 hours can vary, so that was just a
number I suggested.

> Are the hours or some other thing in your query variable?
Yes.  That 5.5 hour is an input from someone.  It could be 1, 1.5, ...100,
etc. But, it will be an hourly amount.  It can also be 0.5 hour.  So, that
needs to be calculated.

> Maybe give us your actual query and we could understand and help better.
You did it on the previous one. ;-)  Thanks.

_______________________________________________
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: datetime result help

jose isaias cabrera
In reply to this post by R Smith

"R.Smith" wrote,

> Oops, rereading your message it seems you need only the difference carried
> over to the next day, not schedule the entire time-slot on the next day,
> this will work better for that:
>
> WITH DTVals( EndOfToday, NewTimeToday, NewTimeTomorrow ) AS (
>   SELECT
>     datetime( 'now', 'start of day', '+17 hours', 'localtime'),
>     datetime( 'now', '+5.5 hours', 'localtime'),
>     datetime( 'now', '+5.5 hours', '+15.5 hours', 'localtime')
> )
> SELECT
>   CASE WHEN NewTimeToday <= EndOfToday THEN NewTimeToday ELSE
> NewTimeTomorrow END
> FROM DTVals;
>
>
> (The difference between today 17:00 and tomorrow morning 08:30 is 15.5
> hours, hence...)

Yes, this one works. Thanks.  You guys are amazing.


> On 2015-07-29 07:29 PM, R.Smith wrote:
>>
>>
>> On 2015-07-29 06:34 PM, jose isaias cabrera wrote:
>>> Greetings!
>>>
>>> I am trying to calculate a date using sqlite date and time functions.
>>> Perhaps, one of you GURUs can help this poor soul. I have been trying to
>>> figure it out, but I know I am lack the understanding.  I read the
>>> documentation for it,
>>>
>>> https://www.sqlite.org/lang_datefunc.html
>>>
>>> but I can not seem to find the way to do this.  The idea is this one,
>>> there will be an input of hours, which in this case will be 5.5 hours,
>>> that will be added to the actual time.  For example, if it's 10AM,
>>> adding 5.5 hours will give 3:30 PM.  This is easy,
>>>
>>> datetime('now','localtime','+5.5 hours')
>>>
>>> the problem happens if it is after 5PM. For example, say it is 2PM,
>>> adding 5.5 hours to it will give 7:30PM.  What I am trying to do is to
>>> add the amount of hours over 5PM, which is,
>>>
>>> time('now','localtime','+5.5 hours') - '17:00:00'
>>>
>>> to 8.5 hours to the start of the next day and select that date and time.
>>
>> Finally, an interesting question :)
>>
>> It's easy to do, but you need to think of time in days:
>>
>>
>> WITH DTVals( EndOfToday, NewTimeToday, NewTimeTomorrow ) AS (
>>   SELECT
>>     datetime( 'now', 'localtime', 'start of day', '+17 hours' ),       //
>> End of Today
>>     datetime( 'now', 'localtime', '+5.5 hours' ),                      //
>> New time if today
>>     datetime( 'now', 'localtime', 'start of day', '+1 day', '+8.5 hours',
>> '+5.5 hours' )    // New time if tomorrow
>> )
>> SELECT
>>   CASE WHEN NewTimeToday <= EndOfToday THEN NewTimeToday ELSE
>> NewTimeTomorrow END
>> FROM DTVals;
>>
>>
>> The whole CTE isn't necessary, it's just to show more clear what is
>> happening. Once you understand why the  above works, the calculation can
>> be optimized a lot and made faster.
>> (All those spaces are just for clarity, they may be omitted)
>>
>>
>> Cheers,
>> Ryan
>>
>>
>>>   So, I thought that if I concatenate the result to the with to '+XXX
>>> hours' it would work.  But, you all probably know the result.  So, here
>>> is what I have tried last:
>>>
>>> select CASE
>>>    WHEN time('now','localtime','+5.5 hours') > '17:00:00' THEN
>>>      datetime('now','localtime','+1 day','start of day','+8.5 hours','+'
>>> || time('now','localtime','+5.5 hours') - '17:00:00' || ' hours')
>>>    ELSE
>>>      datetime('now','localtime','+5.5 hours')
>>>    END;
>>>
>>> but I get nothing for result:
>>>
>>> sqlite> select CASE
>>>     ...>   WHEN time('now','localtime','+5.5 hours') > '17:00:00' THEN
>>>     ...>     datetime('now','start of day','+1 day','+8.5 hours','+' ||
>>> time('now
>>> ','+5.5 hours','localtime') - '17:00:00' || ' hours')
>>>     ...>   else
>>>     ...>     datetime('now','localtime','+5.5 hours')
>>>     ...>   END;
>>>
>>>
>>> sqlite>
>>>
>>> Any help would be greatly appreciated.  Thanks.
>>>
>>> _______________________________________________
>>> 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 

_______________________________________________
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: datetime result help

R Smith
In reply to this post by R Smith
Adapted to allow variable hours:

Assuming you have in your code a variable set for the hours, let's
assume some PHP and call it $hrs:

SELECT (CASE
     WHEN datetime( 'now', '+".$hrs."' hours', 'localtime') <= (date(
'now', 'localtime')||' 17:00:00')  THEN datetime( 'now', '+".$hrs."'
hours', 'localtime')
     ELSE datetime( 'now', '+".$hrs." hours', '+15.5 hours', 'localtime')
   END )

Now take note, you've said in a previous mail that it can be ANY amount
of hours... the methods we have mentioned so far will break very badly
if you give more hours than today and tomorrow can accommodate.. such as
'+35 hours', because we are only skipping one day's hours, not 2 or more.

If you really need to accommodate more than 2 work-days worth of hours,
we need to adjust that query a lot - let us know.



On 2015-07-29 08:01 PM, R.Smith wrote:

> Oops, rereading your message it seems you need only the difference
> carried over to the next day, not schedule the entire time-slot on the
> next day, this will work better for that:
>
> WITH DTVals( EndOfToday, NewTimeToday, NewTimeTomorrow ) AS (
>   SELECT
>     datetime( 'now', 'start of day', '+17 hours', 'localtime'),
>     datetime( 'now', '+5.5 hours', 'localtime'),
>     datetime( 'now', '+5.5 hours', '+15.5 hours', 'localtime')
> )
> SELECT
>   CASE WHEN NewTimeToday <= EndOfToday THEN NewTimeToday ELSE
> NewTimeTomorrow END
> FROM DTVals;
>
>
> (The difference between today 17:00 and tomorrow morning 08:30 is 15.5
> hours, hence...)
>
> On 2015-07-29 07:29 PM, R.Smith wrote:
>>
>>
>> On 2015-07-29 06:34 PM, jose isaias cabrera wrote:
>>> Greetings!
>>>
>>> I am trying to calculate a date using sqlite date and time
>>> functions.  Perhaps, one of you GURUs can help this poor soul. I
>>> have been trying to figure it out, but I know I am lack the
>>> understanding.  I read the documentation for it,
>>>
>>> https://www.sqlite.org/lang_datefunc.html
>>>
>>> but I can not seem to find the way to do this.  The idea is this
>>> one, there will be an input of hours, which in this case will be 5.5
>>> hours, that will be added to the actual time.  For example, if it's
>>> 10AM, adding 5.5 hours will give 3:30 PM. This is easy,
>>>
>>> datetime('now','localtime','+5.5 hours')
>>>
>>> the problem happens if it is after 5PM. For example, say it is 2PM,
>>> adding 5.5 hours to it will give 7:30PM.  What I am trying to do is
>>> to add the amount of hours over 5PM, which is,
>>>
>>> time('now','localtime','+5.5 hours') - '17:00:00'
>>>
>>> to 8.5 hours to the start of the next day and select that date and
>>> time.
>>
>> Finally, an interesting question :)
>>
>> It's easy to do, but you need to think of time in days:
>>
>>
>> WITH DTVals( EndOfToday, NewTimeToday, NewTimeTomorrow ) AS (
>>   SELECT
>>     datetime( 'now', 'localtime', 'start of day', '+17 hours'
>> ),       // End of Today
>>     datetime( 'now', 'localtime', '+5.5 hours'
>> ),                      // New time if today
>>     datetime( 'now', 'localtime', 'start of day', '+1 day', '+8.5
>> hours', '+5.5 hours' )    // New time if tomorrow
>> )
>> SELECT
>>   CASE WHEN NewTimeToday <= EndOfToday THEN NewTimeToday ELSE
>> NewTimeTomorrow END
>> FROM DTVals;
>>
>>
>> The whole CTE isn't necessary, it's just to show more clear what is
>> happening. Once you understand why the  above works, the calculation
>> can be optimized a lot and made faster.
>> (All those spaces are just for clarity, they may be omitted)
>>
>>
>> Cheers,
>> Ryan
>>
>>
>>>   So, I thought that if I concatenate the result to the with to
>>> '+XXX hours' it would work.  But, you all probably know the result.  
>>> So, here is what I have tried last:
>>>
>>> select CASE
>>>    WHEN time('now','localtime','+5.5 hours') > '17:00:00' THEN
>>>      datetime('now','localtime','+1 day','start of day','+8.5
>>> hours','+' || time('now','localtime','+5.5 hours') - '17:00:00' || '
>>> hours')
>>>    ELSE
>>>      datetime('now','localtime','+5.5 hours')
>>>    END;
>>>
>>> but I get nothing for result:
>>>
>>> sqlite> select CASE
>>>     ...>   WHEN time('now','localtime','+5.5 hours') > '17:00:00' THEN
>>>     ...>     datetime('now','start of day','+1 day','+8.5 hours','+'
>>> || time('now
>>> ','+5.5 hours','localtime') - '17:00:00' || ' hours')
>>>     ...>   else
>>>     ...>     datetime('now','localtime','+5.5 hours')
>>>     ...>   END;
>>>
>>>
>>> sqlite>
>>>
>>> Any help would be greatly appreciated.  Thanks.
>>>
>>> _______________________________________________
>>> 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

_______________________________________________
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: datetime result help

jose isaias cabrera

"R.Smith" wrote...

> Adapted to allow variable hours:
>
> Assuming you have in your code a variable set for the hours, let's assume
> some PHP and call it $hrs:
>
> SELECT (CASE
>     WHEN datetime( 'now', '+".$hrs."' hours', 'localtime') <= (date(
> 'now', 'localtime')||' 17:00:00')  THEN datetime( 'now', '+".$hrs."'
> hours', 'localtime')
>     ELSE datetime( 'now', '+".$hrs." hours', '+15.5 hours', 'localtime')
>   END )
>
> Now take note, you've said in a previous mail that it can be ANY amount of
> hours... the methods we have mentioned so far will break very badly if you
> give more hours than today and tomorrow can accommodate.. such as '+35
> hours', because we are only skipping one day's hours, not 2 or more.
>
> If you really need to accommodate more than 2 work-days worth of hours, we
> need to adjust that query a lot - let us know.

There will be entries longer than 1 day, so I will have to address than.
Thanks for the help, but this is great, though.  If I need more help, I will
come back, but I have enough, and I "I think" I can figure out those longer
hours then one or more days.  Thanks.

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: datetime result help

R Smith

On 2015-07-29 08:58 PM, jose isaias cabrera wrote:
>
>
> There will be entries longer than 1 day, so I will have to address
> than. Thanks for the help, but this is great, though.  If I need more
> help, I will come back, but I have enough, and I "I think" I can
> figure out those longer hours then one or more days.  Thanks.
>
> josé

Well, it seemed fun, and probably easier to calculate in code, but this
SQL (SQLite specific) will do what you need for any amount of hours
(even hundreds of days) and for any work-day-length and work-start-time
in the day:


WITH DTBase(HrsToAdd, DayStartsAt, HrsPerDay, HrsNow) AS (SELECT

   5.5,     -- Set this to the hours you wish to add. Any positive value
is fine.
   8.5,     -- Set this to the hours where the work-day starts (8:30am =
8.5, 9:15am = 9.25, etc.)
   8.5,     -- Set this to how many hours makes up a workday
(8:30..17:00 = 8.5 hours)

   (CAST(strftime('%H','now','localtime') AS
REAL)+(CAST(strftime('%M','now','localtime') AS REAL)/60)-8.5)-- 8.5
here Same as DaySTart

), DTFill(HrsToFillToday,HrsRemain) AS (SELECT
     MAX(HrsPerDay-HrsNow,0),
     MAX(HrsToAdd-MAX(HrsPerDay-HrsNow,0),0)
   FROM DTBase

), DTDays(DaysToAdd,DayHrsToAdd) AS (SELECT
      CAST((HrsRemain/HrsPerDay) AS INT),
     (HrsRemain-(CAST((HrsRemain/HrsPerDay) AS INT)*HrsPerDay))
   FROM DTFill,DTBase

), DTTime(DaysToAdd,DayHrsToAdd) AS (SELECT
      DaysToAdd,
     (DaySTartsAt+(CASE WHEN HrsRemain>0 THEN DayHrsToAdd+24 ELSE
HrsNow+HrsToFillToday END))
   FROM DTBase,DTFill,DTDays

)
SELECTdatetime( 'now', 'localtime', 'start of day', '+'||DaysToAdd||'
days', '+'||DayHrsToAdd||' hours') AS NewTime
   FROM DTTime;



Cheers,
Ryan

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

Thanks SQLite

R Smith

I just wish to note something and display my gratitude (which I hope is
shared by others) towards Richard, Dan and other SQLite devs for all the
great functionality, but specifically CTE's.

The reason I need to say this is that I was one of the people who, when
we first had requests for CTE's on this forum (if anyone fancies
scrolling back), said: "C-T-E - Shmee-Tee-Eee - who would ever need that
for real?" chimed along with others who had statements like "Anything
you can do with CTE you can also do without it" and the like.

Today the inverse is true. What an absolute pleasure to use. Even if the
CTE may not be strictly needed, almost every problem or complex query
can be simplified using a CTE, and easily later factored back (removing
the CTE) if optimization requires it. I find also explaining query
concepts to others via a CTE much easier. I now use CTE's extensively in
MSSQL (thanks to SQLite showing the way) and get annoyed at
MariaDB/MySQL for not having it.

This also makes up amicably for SQLite's lack of script variables. Just
make a CTE table full of presets to use later - problem solved.

A heartfelt Thank-you!
Ryan

_______________________________________________
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: Thanks SQLite

Richard Hipp-3
On 7/29/15, R.Smith <[hidden email]> wrote:
>
> I just wish to ... display my gratitude ... for ... CTE's.
>

Glad you find them useful.

I'm looking for real-world (open-source) use cases for CTEs.  Please
send me links find any.  I have spotted a few CTEs used in Firefox.
For example, when rearranging the bookmark hierarchy by drag-and-drop
in Firefox, it uses a CTE to verify that you are not trying to make a
bookmark folder into a subfolder of itself.  And, of course, Fossil
uses CTEs (since I wrote them).  But more examples would be
appreciated.

> I now get annoyed at
> MariaDB/MySQL for not having it.
>

FWIW: I am told that MariaDB will have CTEs soon - within 6 months or so.

--
D. Richard Hipp
[hidden email]
_______________________________________________
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: Thanks SQLite

Simon Slavin-3

On 29 Jul 2015, at 11:32pm, Richard Hipp <[hidden email]> wrote:

> I'm looking for real-world (open-source) use cases for CTEs.

I would like to see a demonstration of how non-recursive CTEs can be useful in simplifying a SQL command.  Ideally in a plausible example rather than something obviously made up with no real-world equivalent.  It seems that everyone who mentions CTEs jumps straight to recursion, as if that's the only thing they're useful for.

Simon.
_______________________________________________
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: Thanks SQLite

R Smith


On 2015-07-30 12:41 AM, Simon Slavin wrote:
> On 29 Jul 2015, at 11:32pm, Richard Hipp <[hidden email]> wrote:
>
>> I'm looking for real-world (open-source) use cases for CTEs.
> I would like to see a demonstration of how non-recursive CTEs can be useful in simplifying a SQL command.  Ideally in a plausible example rather than something obviously made up with no real-world equivalent.  It seems that everyone who mentions CTEs jumps straight to recursion, as if that's the only thing they're useful for.

Well, it's hard to show a quick and simple example when the question
specifically presupposes complexity, but if you forgive the verbosity,
here is an example of non-recursive CTE which I don't think is even
possible without CTE (or pre-set-up temporary tables) in a very
real-World situation:

Basic manufacturing company has a data system comprising of stock items
and stockkeeping units (SKU's) aka manufactured products. The method of
turning stock into a manufactured product is commonly referred to as a Job.

Jobs have Bills of material (BOM) that basically is a stock item (sku)
with a parent-child relationship to several other stock items which it
"uses" or consumes to get made.

A stock item can belong to very many Bills of material, or can be used
outside of any (such as surgical gloves that makes part of the
consumables for a job, but isn't part of the manufactured product,
etc.). BOMs can be sub-BOMs of other BOMs, in the way that your car's
Engine is its own manufactured BOM unit but also a sub-unit of the Car's
total BOM. (Recursive CTE's help us a lot in this regard for working out
other things)

Now at any point, in your store there is an amount of stock that can be
used, but the amount of a specific finished product that can be produced
from it will vary widely according to BOM consumption figures. So let's
say you have 10 of Stock X1,  20 of X2 and  5 of X3, but the product you
want to make consumes 8 of each. You will need to buy 3 more X3 to be
able to make even 1 of this product.

That sounds simple enough, but consider that nobody will sell you 3 more
X3, those X3 items comes in minimum orders of 25 (we say MOQ for
minimum-order-quantity). So if I buy a pack of X3, I can now make one
product - but, I will now be left over with 2 of X1, 12 of X2 and 22 of
X3 - that is more stock than I started with!

Of course the MOQ for X1 and X2 are different, I might need to buy 30 of
X1's and 10 of X2's.  (There is also a thing called Pan-Size / Pack-Size
that dictates the minimum set to buy - you might need to buy a minimum
of 30, but after that you still need to buy sets of 6, so you can buy 36
or 42, but not 35 or 37, etc. I will ignore this bit for this example,
but note how the complexity grows).

Then, every item has a cost. Some items are very expensive, and some are
very cheap. I won't mind buying 50 extra of cheap item A as long as I
use up expensive item B completely.

One common question is: How much do I need to buy of everything so that
I can manufacture my products and have the least amount of wasted value
left over?.

A final complication to mention is that, we only really care about
balancing after a point - I mean it's all fine and dandy if we find that
making 7235 jobs will use up the stock so that there is zero of
everything left. We can't make that many jobs, we don't have the
capacity and even if we did, nobody will buy them all. In fact, for this
exercise, we don't really wish to make more than 20 jobs at any time.

By now I hopefully don't need to explain any more how this has become a
really complex problem to solve. To answer the question, some CTEs can
be called into service.

First, let's build a table with job counts in the range that we are
comfortable with (so 0 to 20 in this case).

    WITH JCount(c) AS (
       SELECT 0 UNION ALL SELECT c+1 FROM JCount LIMIT 21
    ),


(Ignore the recursion here, it only builds a quick little table, it
isn't recursing the main dataset in any way - just another way of saying
SELECT 1 UNION ALL SELECT 2 UNION ALL.. etc etc.)

Next we will add a CTE that lists Job candidates for manufacture,
followed by a CTE that calculates all our candidate BOMs and their stock
needs.
I'll imagine this is a sweets factory and we're making little jelly
things this time.
(I'm not showing the schema because it isn't important and you can
intuit it)

    WITH JCount(c) AS (
    SELECT 0 UNION ALL SELECT c+1 FROM JCount LIMIT 20
    ),  Candidates(BOMCode, UnitsPerBOM) AS (
    SELECT StockCode, QtyPer FROM stock
        WHERE Description LIKE '%jelly%'  AND StockCode IN (SELECT
    ParentPart FROM BomStructure)

    ), JBOMs(JobQty, BOMCode, TotalUnitsToMake, TotalStockNeeded) AS (
       SELECT c, BOMCode, (c * UnitsPerBOM),  (QtyPer * c)
         FROM JCount, Candidates
         JOIN stock ON stock.StockCode IN (SELECT Component FROM
    BomStructure WHERE ParentPart = BOMCode)
        WHERE stock.QtyPer > 0

    ),


Now that we have a list of possible BOMs to make against several
possible quantities to make and knowing the stock usage for each of
those, we can continue to see how much we must procure to make them happen:


    WITH JCount(c) AS (
       SELECT 0 UNION ALL SELECT c+1 FROM JCount LIMIT 20
    ),  Candidates(BOMCode, UnitsPerBOM) AS (
       SELECT StockCode, QtyPer FROM stock
        WHERE Description LIKE '%jelly%'  AND StockCode IN (SELECT
    ParentPart FROM BomStructure)

    ), JBOMs(JobQty, BOMCode, TotalUnitsToMake, TotalStockNeeded) AS (
       SELECT c, BOMCode, (c * UnitsPerBOM),  (QtyPer * c)
         FROM JCount, Candidates
         JOIN stock ON stock.StockCode IN (SELECT Component FROM
    BomStructure WHERE ParentPart = BOMCode)
        WHERE stock.QtyPer > 0

    ), Procure(c, BOMCode, ProcureQty) AS (
       SELECT c, BOMCode, ((CASE
         WHEN (WH.QtyOnHand - TotalStockNeeded) >= 0 THEN (WH.QtyOnHand
    - TotalStockNeeded)
         ELSE MAX(TotalStockNeeded - WH.QtyOnHand, MOQ)
         END) * UnitCost)
         FROM JBOMs

    )


In the real World, Stock will sit in multiple warehouses and have
various lots which may be subject to expiry dates etc - We'll ignore all
that here, but another CTE can take care of those things too.

At this point we are only interested in the quantities of jobs to make
and the Values left over after making them - another query can take care
of calculating the procurement specifics once we have decided on the
quantities.

    WITH JCount(c) AS (
       SELECT 0 UNION ALL SELECT c+1 FROM JCount LIMIT 20
    ),  Candidates(BOMCode, UnitsPerBOM) AS (
       SELECT StockCode, QtyPer FROM stock
        WHERE Description LIKE '%jelly%'  AND StockCode IN (SELECT
    ParentPart FROM BomStructure)

    ), JBOMs(JobQty, BOMCode, TotalUnitsToMake, TotalStockNeeded) AS (
       SELECT c, BOMCode, (c * UnitsPerBOM),  (QtyPer * c)
         FROM JCount, Candidates
         JOIN stock ON stock.StockCode IN (SELECT Component FROM
    BomStructure WHERE ParentPart=BOMCode)
        WHERE stock.QtyPer > 0

    ), Procure(c, BOMCode, StockCode, UCost, StockNeeded, QtyOnHand,
    ProcureQty) AS (
       SELECT c, BOMCode, StockCode, WH.UnitCost, StockNeeded,
    WH.QtyOnHand, (CASE
         WHEN (WH.QtyOnHand - StockNeeded) >= 0 THEN (WH.QtyOnHand -
    StockNeeded)
         ELSE MAX(StockNeeded - WH.QtyOnHand, MOQ)
         END)
         FROM JBOMs
         LEFT JOIN Warehouse AS WH ON WH.Warehouse = 'RAWMAT' AND
    WH.StockCode=StockCode

    ), LeftOvers(c, BOMCode, LeftOverVal) AS (
       SELECT c, BOMCode, SUM((QtyOnHand + ProcureQty - StockNeeded) *
    UCost)
         FROM Procure
        GROUP BY c, BOMCode
    )



Now finally, we can run a standard query against these CTE's and rank
the results to find the lowest 10 Left-over values vs. Qty to make which
will enable a human to best judge which qty to go for:

    SELECT c||'  x  '||BOMCode||'  yields  a waste value of: '||LeftOverVal
       FROM LeftOvers
       ORDER BY LeftOverVal DESC
       LIMIT 10
    ;


It might be possible to do without CTE's, but the complexity would be
staggering, and I can't actually think of a way to achieve this without
CTE's or Temp tables, and in the case of temp tables, it definitely
won't be inside of 1 single statement like this. Most importantly: with
CTE's, the above became real easy to solve.

This is just one example of a real-World question that can be made
simple by a divide and conquer philosophy of complex problems by using
CTE's.

In fact, now I think of it, examples of complex problems being helped by
CTE are everywhere, it's finding examples of simple problems that are
helped by CTE that is difficult.

Cheers,
Ryan


_______________________________________________
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: datetime result help

jose isaias cabrera
In reply to this post by R Smith

"R.Smith" wrote...

>
> On 2015-07-29 08:58 PM, jose isaias cabrera wrote:
>>
>>
>> There will be entries longer than 1 day, so I will have to address than.
>> Thanks for the help, but this is great, though.  If I need more help, I
>> will come back, but I have enough, and I "I think" I can figure out those
>> longer hours then one or more days.  Thanks.
>>
>> josé
>
> Well, it seemed fun, and probably easier to calculate in code, but this
> SQL (SQLite specific) will do what you need for any amount of hours (even
> hundreds of days) and for any work-day-length and work-start-time in the
> day:
>
>
> WITH DTBase(HrsToAdd, DayStartsAt, HrsPerDay, HrsNow) AS (SELECT
>
>   5.5,     -- Set this to the hours you wish to add. Any positive value is
> fine.
>   8.5,     -- Set this to the hours where the work-day starts (8:30am =
> 8.5, 9:15am = 9.25, etc.)
>   8.5,     -- Set this to how many hours makes up a workday (8:30..17:00 =
> 8.5 hours)
>
>   (CAST(strftime('%H','now','localtime') AS
> REAL)+(CAST(strftime('%M','now','localtime') AS REAL)/60)-8.5)-- 8.5 here
> Same as DaySTart
>
> ), DTFill(HrsToFillToday,HrsRemain) AS (SELECT
>     MAX(HrsPerDay-HrsNow,0),
>     MAX(HrsToAdd-MAX(HrsPerDay-HrsNow,0),0)
>   FROM DTBase
>
> ), DTDays(DaysToAdd,DayHrsToAdd) AS (SELECT
>      CAST((HrsRemain/HrsPerDay) AS INT),
>     (HrsRemain-(CAST((HrsRemain/HrsPerDay) AS INT)*HrsPerDay))
>   FROM DTFill,DTBase
>
> ), DTTime(DaysToAdd,DayHrsToAdd) AS (SELECT
>      DaysToAdd,
>     (DaySTartsAt+(CASE WHEN HrsRemain>0 THEN DayHrsToAdd+24 ELSE
> HrsNow+HrsToFillToday END))
>   FROM DTBase,DTFill,DTDays
>
> )
> SELECTdatetime( 'now', 'localtime', 'start of day', '+'||DaysToAdd||'
> days', '+'||DayHrsToAdd||' hours') AS NewTime
>   FROM DTTime;

Well, since you did it for fun, I'll test it and let you know. ;-)  Thanks.

_______________________________________________
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: datetime result help

jose isaias cabrera
In reply to this post by R Smith

"R.Smith" wrote...

>
> On 2015-07-29 08:58 PM, jose isaias cabrera wrote:
>>
>>
>> There will be entries longer than 1 day, so I will have to address than.
>> Thanks for the help, but this is great, though.  If I need more help, I
>> will come back, but I have enough, and I "I think" I can figure out those
>> longer hours then one or more days.  Thanks.
>>
>> josé
>
> Well, it seemed fun, and probably easier to calculate in code, but this
> SQL (SQLite specific) will do what you need for any amount of hours (even
> hundreds of days) and for any work-day-length and work-start-time in the
> day:
>
>
> WITH DTBase(HrsToAdd, DayStartsAt, HrsPerDay, HrsNow) AS (SELECT
>
>   5.5,     -- Set this to the hours you wish to add. Any positive value is
> fine.
>   8.5,     -- Set this to the hours where the work-day starts (8:30am =
> 8.5, 9:15am = 9.25, etc.)
>   8.5,     -- Set this to how many hours makes up a workday (8:30..17:00 =
> 8.5 hours)
>
>   (CAST(strftime('%H','now','localtime') AS
> REAL)+(CAST(strftime('%M','now','localtime') AS REAL)/60)-8.5)-- 8.5 here
> Same as DaySTart
>
> ), DTFill(HrsToFillToday,HrsRemain) AS (SELECT
>     MAX(HrsPerDay-HrsNow,0),
>     MAX(HrsToAdd-MAX(HrsPerDay-HrsNow,0),0)
>   FROM DTBase
>
> ), DTDays(DaysToAdd,DayHrsToAdd) AS (SELECT
>      CAST((HrsRemain/HrsPerDay) AS INT),
>     (HrsRemain-(CAST((HrsRemain/HrsPerDay) AS INT)*HrsPerDay))
>   FROM DTFill,DTBase
>
> ), DTTime(DaysToAdd,DayHrsToAdd) AS (SELECT
>      DaysToAdd,
>     (DaySTartsAt+(CASE WHEN HrsRemain>0 THEN DayHrsToAdd+24 ELSE
> HrsNow+HrsToFillToday END))
>   FROM DTBase,DTFill,DTDays
>
> )
> SELECTdatetime( 'now', 'localtime', 'start of day', '+'||DaysToAdd||'
> days', '+'||DayHrsToAdd||' hours') AS NewTime
>   FROM DTTime;

Ok, I have one more option for you, and since you think it's fun, I need to
exclude Saturdays and Sundays.  I have a hack in the programming side of
things, but I would like to do it right from SQLite.  Thougths?

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: Thanks SQLite

Roman Fleysher
In reply to this post by R Smith


I dare to add my thanks here, with a much simpler example. Initially, for me, CTE was another thing to learn..... Then I wanted SQLite to compute statistics on a simple two-column table. Not a big deal, I typed the equation and was done. Next day, I needed the same equation to be applied to two different columns, but these columns were to be produced from some other tables. Maybe there was a way to do it without, but I did it with WITH ... . Now, the equation stays put all the time and I change the WITH clause to redefine the two columns when I need equation to be applied to new data.

I was elated when I implemented first equation. This is like stored procedure.

Roman


________________________________________
From: [hidden email] [[hidden email]] on behalf of R.Smith [[hidden email]]
Sent: Wednesday, July 29, 2015 10:08 PM
To: [hidden email]
Subject: Re: [sqlite] Thanks SQLite

On 2015-07-30 12:41 AM, Simon Slavin wrote:
> On 29 Jul 2015, at 11:32pm, Richard Hipp <[hidden email]> wrote:
>
>> I'm looking for real-world (open-source) use cases for CTEs.
> I would like to see a demonstration of how non-recursive CTEs can be useful in simplifying a SQL command.  Ideally in a plausible example rather than something obviously made up with no real-world equivalent.  It seems that everyone who mentions CTEs jumps straight to recursion, as if that's the only thing they're useful for.

Well, it's hard to show a quick and simple example when the question
specifically presupposes complexity, but if you forgive the verbosity,
here is an example of non-recursive CTE which I don't think is even
possible without CTE (or pre-set-up temporary tables) in a very
real-World situation:

Basic manufacturing company has a data system comprising of stock items
and stockkeeping units (SKU's) aka manufactured products. The method of
turning stock into a manufactured product is commonly referred to as a Job.

Jobs have Bills of material (BOM) that basically is a stock item (sku)
with a parent-child relationship to several other stock items which it
"uses" or consumes to get made.

A stock item can belong to very many Bills of material, or can be used
outside of any (such as surgical gloves that makes part of the
consumables for a job, but isn't part of the manufactured product,
etc.). BOMs can be sub-BOMs of other BOMs, in the way that your car's
Engine is its own manufactured BOM unit but also a sub-unit of the Car's
total BOM. (Recursive CTE's help us a lot in this regard for working out
other things)

Now at any point, in your store there is an amount of stock that can be
used, but the amount of a specific finished product that can be produced
from it will vary widely according to BOM consumption figures. So let's
say you have 10 of Stock X1,  20 of X2 and  5 of X3, but the product you
want to make consumes 8 of each. You will need to buy 3 more X3 to be
able to make even 1 of this product.

That sounds simple enough, but consider that nobody will sell you 3 more
X3, those X3 items comes in minimum orders of 25 (we say MOQ for
minimum-order-quantity). So if I buy a pack of X3, I can now make one
product - but, I will now be left over with 2 of X1, 12 of X2 and 22 of
X3 - that is more stock than I started with!

Of course the MOQ for X1 and X2 are different, I might need to buy 30 of
X1's and 10 of X2's.  (There is also a thing called Pan-Size / Pack-Size
that dictates the minimum set to buy - you might need to buy a minimum
of 30, but after that you still need to buy sets of 6, so you can buy 36
or 42, but not 35 or 37, etc. I will ignore this bit for this example,
but note how the complexity grows).

Then, every item has a cost. Some items are very expensive, and some are
very cheap. I won't mind buying 50 extra of cheap item A as long as I
use up expensive item B completely.

One common question is: How much do I need to buy of everything so that
I can manufacture my products and have the least amount of wasted value
left over?.

A final complication to mention is that, we only really care about
balancing after a point - I mean it's all fine and dandy if we find that
making 7235 jobs will use up the stock so that there is zero of
everything left. We can't make that many jobs, we don't have the
capacity and even if we did, nobody will buy them all. In fact, for this
exercise, we don't really wish to make more than 20 jobs at any time.

By now I hopefully don't need to explain any more how this has become a
really complex problem to solve. To answer the question, some CTEs can
be called into service.

First, let's build a table with job counts in the range that we are
comfortable with (so 0 to 20 in this case).

    WITH JCount(c) AS (
       SELECT 0 UNION ALL SELECT c+1 FROM JCount LIMIT 21
    ),


(Ignore the recursion here, it only builds a quick little table, it
isn't recursing the main dataset in any way - just another way of saying
SELECT 1 UNION ALL SELECT 2 UNION ALL.. etc etc.)

Next we will add a CTE that lists Job candidates for manufacture,
followed by a CTE that calculates all our candidate BOMs and their stock
needs.
I'll imagine this is a sweets factory and we're making little jelly
things this time.
(I'm not showing the schema because it isn't important and you can
intuit it)

    WITH JCount(c) AS (
    SELECT 0 UNION ALL SELECT c+1 FROM JCount LIMIT 20
    ),  Candidates(BOMCode, UnitsPerBOM) AS (
    SELECT StockCode, QtyPer FROM stock
        WHERE Description LIKE '%jelly%'  AND StockCode IN (SELECT
    ParentPart FROM BomStructure)

    ), JBOMs(JobQty, BOMCode, TotalUnitsToMake, TotalStockNeeded) AS (
       SELECT c, BOMCode, (c * UnitsPerBOM),  (QtyPer * c)
         FROM JCount, Candidates
         JOIN stock ON stock.StockCode IN (SELECT Component FROM
    BomStructure WHERE ParentPart = BOMCode)
        WHERE stock.QtyPer > 0

    ),


Now that we have a list of possible BOMs to make against several
possible quantities to make and knowing the stock usage for each of
those, we can continue to see how much we must procure to make them happen:


    WITH JCount(c) AS (
       SELECT 0 UNION ALL SELECT c+1 FROM JCount LIMIT 20
    ),  Candidates(BOMCode, UnitsPerBOM) AS (
       SELECT StockCode, QtyPer FROM stock
        WHERE Description LIKE '%jelly%'  AND StockCode IN (SELECT
    ParentPart FROM BomStructure)

    ), JBOMs(JobQty, BOMCode, TotalUnitsToMake, TotalStockNeeded) AS (
       SELECT c, BOMCode, (c * UnitsPerBOM),  (QtyPer * c)
         FROM JCount, Candidates
         JOIN stock ON stock.StockCode IN (SELECT Component FROM
    BomStructure WHERE ParentPart = BOMCode)
        WHERE stock.QtyPer > 0

    ), Procure(c, BOMCode, ProcureQty) AS (
       SELECT c, BOMCode, ((CASE
         WHEN (WH.QtyOnHand - TotalStockNeeded) >= 0 THEN (WH.QtyOnHand
    - TotalStockNeeded)
         ELSE MAX(TotalStockNeeded - WH.QtyOnHand, MOQ)
         END) * UnitCost)
         FROM JBOMs

    )


In the real World, Stock will sit in multiple warehouses and have
various lots which may be subject to expiry dates etc - We'll ignore all
that here, but another CTE can take care of those things too.

At this point we are only interested in the quantities of jobs to make
and the Values left over after making them - another query can take care
of calculating the procurement specifics once we have decided on the
quantities.

    WITH JCount(c) AS (
       SELECT 0 UNION ALL SELECT c+1 FROM JCount LIMIT 20
    ),  Candidates(BOMCode, UnitsPerBOM) AS (
       SELECT StockCode, QtyPer FROM stock
        WHERE Description LIKE '%jelly%'  AND StockCode IN (SELECT
    ParentPart FROM BomStructure)

    ), JBOMs(JobQty, BOMCode, TotalUnitsToMake, TotalStockNeeded) AS (
       SELECT c, BOMCode, (c * UnitsPerBOM),  (QtyPer * c)
         FROM JCount, Candidates
         JOIN stock ON stock.StockCode IN (SELECT Component FROM
    BomStructure WHERE ParentPart=BOMCode)
        WHERE stock.QtyPer > 0

    ), Procure(c, BOMCode, StockCode, UCost, StockNeeded, QtyOnHand,
    ProcureQty) AS (
       SELECT c, BOMCode, StockCode, WH.UnitCost, StockNeeded,
    WH.QtyOnHand, (CASE
         WHEN (WH.QtyOnHand - StockNeeded) >= 0 THEN (WH.QtyOnHand -
    StockNeeded)
         ELSE MAX(StockNeeded - WH.QtyOnHand, MOQ)
         END)
         FROM JBOMs
         LEFT JOIN Warehouse AS WH ON WH.Warehouse = 'RAWMAT' AND
    WH.StockCode=StockCode

    ), LeftOvers(c, BOMCode, LeftOverVal) AS (
       SELECT c, BOMCode, SUM((QtyOnHand + ProcureQty - StockNeeded) *
    UCost)
         FROM Procure
        GROUP BY c, BOMCode
    )



Now finally, we can run a standard query against these CTE's and rank
the results to find the lowest 10 Left-over values vs. Qty to make which
will enable a human to best judge which qty to go for:

    SELECT c||'  x  '||BOMCode||'  yields  a waste value of: '||LeftOverVal
       FROM LeftOvers
       ORDER BY LeftOverVal DESC
       LIMIT 10
    ;


It might be possible to do without CTE's, but the complexity would be
staggering, and I can't actually think of a way to achieve this without
CTE's or Temp tables, and in the case of temp tables, it definitely
won't be inside of 1 single statement like this. Most importantly: with
CTE's, the above became real easy to solve.

This is just one example of a real-World question that can be made
simple by a divide and conquer philosophy of complex problems by using
CTE's.

In fact, now I think of it, examples of complex problems being helped by
CTE are everywhere, it's finding examples of simple problems that are
helped by CTE that is difficult.

Cheers,
Ryan


_______________________________________________
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: Thanks SQLite

Török Edwin
In reply to this post by Richard Hipp-3
On 07/30/2015 01:32 AM, Richard Hipp wrote:
> On 7/29/15, R.Smith <[hidden email]> wrote:
>>
>> I just wish to ... display my gratitude ... for ... CTE's.
>>
>
> Glad you find them useful.
>
> I'm looking for real-world (open-source) use cases for CTEs.  Please
> send me links find any.

We use CTE in Skylable SX's job manager to track dependencies in a chain of jobs:
http://gitweb.skylable.com/gitweb/?p=sx.git;a=blob;f=server/src/fcgi/jobmgr.c;h=118d1d742e03c9404313d9f967eef58500e3b6c5;hb=HEAD#l5944
http://gitweb.skylable.com/gitweb/?p=sx.git;a=blob;f=server/src/fcgi/jobmgr.c;h=118d1d742e03c9404313d9f967eef58500e3b6c5;hb=HEAD#l5949

These chains are quite short (so that temp B-tree is not a performance problem) and could probably have been implemented with a loop in C and transactions, but it is very convenient to do it with just a single query.

EXPLAIN QUERY PLAN WITH RECURSIVE descendents_of(jb) AS (SELECT job FROM jobs WHERE parent = :job UNION SELECT job FROM jobs, descendents_of WHERE jobs.parent = descendents_of.jb) UPDATE jobs SET result = :res, reason = :reason, complete = 1, lock = NULL WHERE job IN (SELECT * FROM descendents_of) AND result = 0
        0|0|0|SEARCH TABLE jobs USING INTEGER PRIMARY KEY (rowid=?)
        0|0|0|EXECUTE LIST SUBQUERY 0
        2|0|0|SEARCH TABLE jobs USING COVERING INDEX jobs_parent (parent=?)
        3|0|1|SCAN TABLE descendents_of
        3|1|0|SEARCH TABLE jobs USING COVERING INDEX jobs_parent (parent=?)
        1|0|0|COMPOUND SUBQUERIES 0 AND 0 USING TEMP B-TREE (UNION)
        0|0|0|SCAN SUBQUERY 1

 EXPLAIN QUERY PLAN WITH RECURSIVE descendents_of(jb) AS (VALUES(:job) UNION SELECT job FROM jobs, descendents_of WHERE jobs.parent = descendents_of.jb) UPDATE jobs SET expiry_time = datetime(expiry_time, :ttldiff)  WHERE job IN (SELECT * FROM descendents_of)
        0|0|0|SEARCH TABLE jobs USING INTEGER PRIMARY KEY (rowid=?)
        0|0|0|EXECUTE LIST SUBQUERY 0
        3|0|1|SCAN TABLE descendents_of
        3|1|0|SEARCH TABLE jobs USING COVERING INDEX jobs_parent (parent=?)
        1|0|0|COMPOUND SUBQUERIES 0 AND 0 USING TEMP B-TREE (UNION)
        0|0|0|SCAN SUBQUERY 1

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