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
