CODEWITHSUNDEEP
sqlsqlitedatetimestrftime
actual_panda
actual_panda

Reputation: 1260

Why does this SQL statement produce a wrong timestamp?

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?

Upvotes: 0

Views: 101

Answers (1)

Nick
Nick

Reputation: 147146

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

Demo on dbfiddle

Upvotes: 1

Related Questions