"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?
