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