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