The statement
select strftime('%Y-%m-%d %H:%M:%f', (select datetime('2019-07-24 11:11:59', '-288.0110592 seconds')), '+411.030309 seconds')
gives me the timestamp
2019-07-24 11:14:01.030
Verifying the result with Python, I noticed that the result is about one second off.
My Python code:
import datetime
start = datetime.datetime.strptime('2019-07-24 11:11:59', '%Y-%m-%d %H:%M:%S')
offset1 = datetime.timedelta(seconds=288.0110592)
offset2 = datetime.timedelta(seconds=411.030309)
result = start - offset1 + offset2
print(result.strftime('%Y-%m-%d %H:%M:%S.%f'))
Output:
2019-07-24 11:14:02.019250
I did not expect the results to be exactly equal due to rounding errors, but why does the SQL statement seem to be off by almost a second?
Your problem is with the inner select using datetime. As described in the manual, datetime(...) is equivalent to strftime('%Y-%m-%d %H:%M:%S', ...), and you'll note that the format of the seconds is %S, not %f, so the result of
datetime('2019-07-24 11:11:59', '-288.0110592 seconds')
is
2019-07-24 11:07:10.000
not the correct value (with fractional seconds) of
2019-07-24 11:07:10.989
Changing the datetime(...) call to strftime('%Y-%m-%d %H:%M:%f', ...) i.e.
strftime('%Y-%m-%d %H:%M:%f', '2019-07-24 11:11:59', '-288.0110592 seconds')
yields the correct result.
Note that you can simplify the expression and use both modifiers in the outer call to strftime:
select strftime('%Y-%m-%d %H:%M:%f', '2019-07-24 11:11:59', '-288.0110592 seconds', '+411.030309 seconds')
Output:
2019-07-24 11:14:02.019