CODEWITHSUNDEEP
sql
Sobay
Sobay

Reputation: 85

calculate speed SQL

I have a table with column "time, and column distance." I would like to have a column with the speed by hour calculator.

here is my table

+---------+---------+----------------------+----------+--------------------+
| comp    | idname  | name                 | distance | time               | 
+---------+---------+----------------------+----------+--------------------+
| 1041321 | 1230274 | Berlin Calling (IRE) | 1400     | 1'30"74            | 
| 1086603 | 1230274 | Berlin Calling (IRE) | 1600     | 1'42"05            |
| 1106577 | 1230274 | Berlin Calling (IRE) | 1650     | 1'46"56            | 
| 1009518 | 1230274 | Berlin Calling (IRE) | 1700     | 1'44"23            |
| 1166930 | 1230274 | Berlin Calling (IRE) | 1800     | 2'06"54            | 
+---------+---------+----------------------+----------+--------------------+

I would like an output like this:

+---------+---------+----------------------+----------+--------------------+------------+
| comp    | idname  | name                 | distance |     time           | speed/KM/h |
+---------+---------+----------------------+----------+--------------------+------------+
| 1041321 | 1230274 | Berlin Calling (IRE) | 1400     | 1'30"74            | 55,5       |
| 1086603 | 1230274 | Berlin Calling (IRE) | 1600     | 1'42"05            | 56,4       |
| 1106577 | 1230274 | Berlin Calling (IRE) | 1650     | 1'46"56            | 55,7       |
| 1009518 | 1230274 | Berlin Calling (IRE) | 1700     | 1'44"23            | 58,7       |
| 1166930 | 1230274 | Berlin Calling (IRE) | 1800     | 2'06"54            | 51,13      |
+---------+---------+----------------------+----------+--------------------+------------+

Thank you for your help

Upvotes: 0

Views: 624

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Date/time and string parsing functions are specific to a database. I am going to assume you are using MySQL based on other questions you have asked.

If so, you can use:

select t.*,
       distance / (substring_index(time, '''', 1) * 24.0 +
                   substring_index(substring_index(time, '''', 2), '"') * 1.0 +
                   substring_index(time, '"', -1) / (60.0)
                  ) as speed
from t;

Other databases have functions for parsing strings, but this varies significantly among databases.

I would strongly recommend that you store your times as decimal hours rather than in the arcane format you are using.

Upvotes: 1

Related Questions