view raw
jan-seins jan-seins - 7 months ago 39
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


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

  ((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.