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