On 2015-07-29 08:58 PM, jose isaias cabrera wrote:
>
>
> There will be entries longer than 1 day, so I will have to address
> than. Thanks for the help, but this is great, though. If I need more
> help, I will come back, but I have enough, and I "I think" I can
> figure out those longer hours then one or more days. Thanks.
>
> jos?
Well, it seemed fun, and probably easier to calculate in code, but this
SQL (SQLite specific) will do what you need for any amount of hours
(even hundreds of days) and for any work-day-length and work-start-time
in the day:
WITH DTBase(HrsToAdd, DayStartsAt, HrsPerDay, HrsNow) AS (SELECT
5.5, -- Set this to the hours you wish to add. Any positive value
is fine.
8.5, -- Set this to the hours where the work-day starts (8:30am =
8.5, 9:15am = 9.25, etc.)
8.5, -- Set this to how many hours makes up a workday
(8:30..17:00 = 8.5 hours)
(CAST(strftime('%H','now','localtime') AS
REAL)+(CAST(strftime('%M','now','localtime') AS REAL)/60)-8.5)-- 8.5
here Same as DaySTart
), DTFill(HrsToFillToday,HrsRemain) AS (SELECT
MAX(HrsPerDay-HrsNow,0),
MAX(HrsToAdd-MAX(HrsPerDay-HrsNow,0),0)
FROM DTBase
), DTDays(DaysToAdd,DayHrsToAdd) AS (SELECT
CAST((HrsRemain/HrsPerDay) AS INT),
(HrsRemain-(CAST((HrsRemain/HrsPerDay) AS INT)*HrsPerDay))
FROM DTFill,DTBase
), DTTime(DaysToAdd,DayHrsToAdd) AS (SELECT
DaysToAdd,
(DaySTartsAt+(CASE WHEN HrsRemain>0 THEN DayHrsToAdd+24 ELSE
HrsNow+HrsToFillToday END))
FROM DTBase,DTFill,DTDays
)
SELECTdatetime( 'now', 'localtime', 'start of day', '+'||DaysToAdd||'
days', '+'||DayHrsToAdd||' hours') AS NewTime
FROM DTTime;
Cheers,
Ryan
