jan-seins jan-seins - 1 month ago 14
SQL Question

Change type of calculated field in select query (sqlite)

im sure i am not the first one to ask this but i can't find the answer to this:
I haver a select query on a datatable in a sqlite database.

select *, ((int_EndTime)-(int_StartTime))/60 as dou_usage_min FROM tbl_unautho_usage;


when i run this i get all the fields from the datatable including a new column calculated from to integer columns with unix time stamp values. However, i want my calculated column to be of the type double. With the query above i get a type integer.

select *, ((int_EndTime as float)-(int_StartTime as float))/60 as dou_usage_min FROM tbl_unautho_usage;


Afterwards I tried to change the column type of my integer-columns to float, but this gies me the following error:

near "as": syntax error:


i got the idea for that from the following post:
How to cast computed column with correct decimal/$ result

Answer

Try multiplying a value used within the arithmetic operation by 1.0.

select 
  *, 
  ((int_EndTime*1.0)-(int_StartTime*1.0))/60 as dou_usage_min 
FROM tbl_unautho_usage;

Probably only one value multiplied will be sufficient.