0

Is there any way to add a derived date/time column (to an existing table) to hold calculated running time from a race.

The values are in hours, round to 2 decimals. There are 2 columns for start time and end time in the table. Below is the last I've tried got a missing expression error. 

ALTER TABLE race
ADD race_time AS (SELECT ROUND(DATEDIFF (endtime, starttime),2)FROM race);
RyanY
  • 1
  • 1

2 Answers2

2

There is no datediff() function in Oracle and you also don't need a SELECT statement to create a computed column:

ALTER TABLE race
  ADD race_time AS (endtime - starttime);

If endtime and starttime are defined as timestamp the race_time column will be of the type interval.

If those two columns are date columns, the result will be a number representing the number of days between the two days

0

It's been a while since I used this functionality but I think it would look more like:

ALTER TABLE race
ADD race_time AS (ROUND(DATEDIFF (endtime, starttime),2))

Note that I'm not aware of a DATEDIFF function in Oracle - if you haven't written this yourself and are looking to get the number of hours between two dates it would be more like:

ALTER TABLE race
ADD race_time AS (ROUND((endtime - starttime)*24.0,2))

If your columns are timestamps, it would probably be easier to convert them to dates when doing the math

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=3ac1f46f8d7296754d14a3de6191dbe8

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • To nominate an answer as the one that best fit your needs, click the grey ✔️ mark to turn it green. If an answer is useful click the . Many answers can be marked as useful, though only one can be accepted as a best fit. While you're a new user, up votes on useful answers may not show but will be recorded and applied later when your own rep is higher – Caius Jard Dec 02 '19 at 22:15
  • Thanks, I used your suggestion in a query to get the race_time prior but now I need to have this column in the table to calculate automatically without hardcoding. – RyanY Dec 02 '19 at 22:17
  • That's what I gave you? Look: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=3410b18c9d72931e8e179eee829ba49e - I don't insert 30 into the race time column, it works it out when I insert two dates 1.25 days apart – Caius Jard Dec 02 '19 at 22:27
  • I got it working by moving the brackets but still need to limit the decimals. Adding a ,2 after 24 doesn't work. – RyanY Dec 03 '19 at 00:05
  • Thanks Caius for pointing me in the right direction. The following will limit the decimal as follows: – RyanY Dec 03 '19 at 00:30