SQLite Adding a Calculated Column

Question

I have a table named "Combined_Bike_Ride" with the below sample data in SQLite

started_at	ended_at
27/05/2020 10:03	27/05/2020 10:16
25/05/2020 10:47	25/05/2020 11:05

I want to add a new calculated column "ride_length" where ride_length = (ended_at) - (started_at) in that table.

I used the below sql code but getting a syntax error near AS.

ALTER TABLE Combined_Bike_Ride ADD COLUMN ride_length AS (ended_at) - (started_at)

Am I missing a date calculation? Any help is much appreciated.

Answer 1

First you must change the format of your dates to YYYY-MM-DD hh:mm because this is the only text date format that you can use with SQLite's datetime functions if you want to perform operations such as calculation of time difference.

For this purpose you can use the function strftime().
With strftime('%s', datetimecolumn) you get the number of seconds between 1970-01-01 00:00:00 and the value of datetimecolumn

The correct syntax to add the new column is this:

ALTER TABLE Combined_Bike_Ride 
ADD COLUMN ride_length 
GENERATED ALWAYS AS ((strftime('%s', ended_at) - strftime('%s', started_at)) / 60)