Skip to content

Instantly share code, notes, and snippets.

@gitfvb
Last active November 18, 2021 17:23
Show Gist options
  • Save gitfvb/1e21f60f4ed86a28a0b51bd534276d8a to your computer and use it in GitHub Desktop.
Save gitfvb/1e21f60f4ed86a28a0b51bd534276d8a to your computer and use it in GitHub Desktop.
calculate processing times in sqlite

if you have multiple columns and want to calculate the differences in seconds and milliseconds, a query like this can help to produce output like in the csv file in this gist.

This is the query

select
	"timestamp"
	,strftime('%f',julianday("inserted")-julianday("timestamp")) "Timestamp to inserted"
	,strftime('%f',julianday("response_calculated")-julianday("inserted")) "Response calculated"
	,strftime('%f',julianday("response_timestamp")-julianday("response_calculated")) "API Call finished"
from queue
order by timestamp
timestamp timestamp to inserted Response calculated API Call finished
2021-11-17 15:38:24.242Z 00.902 00.150 01.235
2021-11-17 16:34:06.837Z 01.048 00.050 00.988
2021-11-17 18:49:31.797Z 00.950 00.043 00.996
2021-11-18 07:33:00.803Z 01.250 00.186 01.394
2021-11-18 10:34:21.605Z 00.674 00.032 00.776
2021-11-18 10:34:23.809Z 00.093 00.198 02.905
2021-11-18 10:50:56.688Z 00.449 00.042 02.318
2021-11-18 10:51:17.901Z 00.065 00.043 00.661
2021-11-18 10:54:42.359Z 01.340 00.056 00.628
2021-11-18 10:55:34.35Z 00.092 00.044 02.469
2021-11-18 10:56:07.925Z 00.803 00.045 00.804
2021-11-18 11:13:23.133Z 00.826 00.043 02.071
2021-11-18 11:13:24.657Z 01.429 00.018 00.569
2021-11-18 11:14:43.697Z 00.822 00.032 02.513
2021-11-18 11:34:50.224Z 01.090 00.032 02.962
2021-11-18 11:34:53.548Z 00.777 00.027 02.443
2021-11-18 11:35:23.593Z 00.093 00.040 00.717
2021-11-18 11:35:25.411Z 00.109 00.049 00.682
2021-11-18 11:35:28.131Z 00.124 00.037 00.579
2021-11-18 11:35:30.853Z 00.090 00.025 00.626
2021-11-18 11:35:38.099Z 00.124 00.035 00.611
2021-11-18 11:35:40.037Z 00.238 00.034 00.633
2021-11-18 11:35:43.564Z 00.090 00.038 00.599
2021-11-18 11:36:04.165Z 00.105 00.038 00.674
2021-11-18 11:36:10.386Z 00.106 00.019 00.675
2021-11-18 11:36:14.535Z 00.092 00.033 00.575
2021-11-18 11:36:19.104Z 00.090 00.034 00.661
2021-11-18 11:36:23.468Z 00.097 00.027 00.598
2021-11-18 11:36:32.117Z 00.579 00.043 00.611
2021-11-18 11:36:36.105Z 00.089 00.051 00.487
2021-11-18 11:36:39.873Z 00.090 00.035 00.611
2021-11-18 11:36:52.79Z 00.117 00.038 00.581
2021-11-18 11:36:58.731Z 00.089 00.056 00.679
2021-11-18 11:37:10.788Z 00.091 00.039 00.617
2021-11-18 11:37:13.192Z 00.087 00.028 00.589
2021-11-18 11:37:15.102Z 00.102 00.038 00.484
2021-11-18 11:37:31.133Z 00.102 00.052 00.617
2021-11-18 11:38:25.903Z 00.112 00.142 00.676
2021-11-18 11:38:28.384Z 00.094 00.178 00.550
2021-11-18 11:38:31.141Z 00.096 00.124 00.725
2021-11-18 11:38:32.096Z 00.094 00.110 00.606
2021-11-18 11:38:42.302Z 00.146 00.099 01.060
2021-11-18 11:38:46.493Z 00.111 00.037 00.692
2021-11-18 11:38:55.411Z 00.101 00.035 00.599
2021-11-18 11:38:58.135Z 00.091 00.033 00.469
2021-11-18 11:39:04.372Z 00.094 00.029 00.598
2021-11-18 11:39:09.27Z 00.097 00.038 00.635
2021-11-18 11:39:11.133Z 00.098 00.030 00.556
2021-11-18 11:39:18.062Z 00.099 00.030 00.663
2021-11-18 11:39:19.621Z 00.095 00.035 00.559
2021-11-18 11:39:37.277Z 00.101 00.028 00.680
2021-11-18 11:39:50.231Z 00.106 00.033 00.662
2021-11-18 11:39:52.006Z 00.086 00.040 00.532
2021-11-18 11:39:53.779Z 00.104 00.031 00.670
2021-11-18 11:39:55.626Z 00.090 00.031 00.563
2021-11-18 11:40:05.55Z 00.092 00.040 00.670
2021-11-18 11:40:14.725Z 00.121 00.033 00.595
2021-11-18 11:40:16.856Z 00.102 00.021 00.596
2021-11-18 11:40:21.582Z 00.083 00.026 00.595
2021-11-18 11:40:24.187Z 01.333 00.036 00.575
2021-11-18 11:40:26.446Z 00.087 00.056 00.585
2021-11-18 11:40:39.853Z 00.099 00.041 00.598
2021-11-18 11:40:40.968Z 00.093 00.026 00.658
2021-11-18 11:41:58.103Z 00.124 00.062 00.629
2021-11-18 11:46:18.842Z 00.080 00.028 00.845
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment