Duration column
Use a string formatted value to upsert a duration value into a column.
Duration format
A duration can be expressed in this format:
NyNmoNwNdNhNsNmsNusNns
Where: - N: Number
- y: Years
- mo: Months
- w: Weeks
- d: Days
- h: Hours
- m: Minutes
- s: Seconds
- ms: Milliseconds
- µs or us: Microseconds
- ns: Nanoseconds
For example, 2mo10d12h
for 2 months 10 days and 12 hours. Missing
duration designators are set to 0.
ISO 8601 format
A duration can be expressed in this ISO 8601 format:
PNYNMNDTNHNMNS
Where: - P: Period designator placed at the start of the duration
- N: Number
- Y: Years
- M: Months
- D: Days
- T: Time
- H: Hours
- M: Minutes
- S: Seconds
For example, P4Y6M3DT12H30M5S
for four years, six months, three
days, twelve hours, thirty minutes, and five seconds.
A duration can also be expressed in this week ISO 8601 format:
PNW
Where: - P: Period designator placed at the start of the duration
- N: Number
- W: Weeks
P6W
for six weeks.Alternative ISO 8601 format
A duration can be expressed in this alternative ISO 8601 format:
P[YYYY]-[MM]-[DD]T[hh]:[mm]:[ss]
Where: - P: Period designator placed at the start of the duration
- YYYY: Years
- MM: Months
- DD: Days
- T: Time
- hh: Hours
- mm: Minutes
- ss: Seconds
For example, P0004-06-03T12:30:05
for four years, six months, three
days, twelve hours, thirty minutes, and five seconds.
Procedure
-
INSERT duration values.
INSERT INTO cycling.race_times ( finish_time, race_name, cyclist_name, race_date ) VALUES ( 1h4m48s20ms, '17th Santos Tour Down Under', 'Rohan DENNIS', '2017-04-14' );
-
UPDATE duration values.
UPDATE cycling.race_times SET finish_time = 4h7m41s19ms WHERE race_name = '17th Santos Tour Down Under' AND cyclist_name = 'Rohan DENNIS' AND race_date = '2017-04-14';