Duration column
Use a string formatted value to upsert a duration value into a column. A duration can be expressed in the formats described in this section.
|
Provide values for a duration without quotes. |
Duration format
A duration can be expressed in this format:
<N>y<N>mo<N>w<N>d<N>h<N>s<N>ms<N>us<N>ns
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:
P<N>Y<N>M<N>DT<N>HNM<N>S
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:
P<N>W
Where:
-
P: Period designator placed at the start of the duration
-
<N>: Number
-
W: Weeks
For example, 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 value.
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 value.
UPDATE cycling.race_times SET finish_time = 2h5m22s14ms WHERE race_name = '17th Santos Tour Down Under' AND cyclist_name = 'Rohan DENNIS' AND race_date = '2017-04-14';
-
Select duration value.
SELECT * FROM cycling.race_times WHERE race_name = '17th Santos Tour Down Under' AND cyclist_name = 'Rohan DENNIS' AND race_date = '2017-04-14';
race_date | race_name | cyclist_name | finish_time | race_time
------------+-----------------------------+--------------+-------------+--------------------
2017-04-14 | 17th Santos Tour Down Under | Rohan DENNIS | 2h5m22s14ms | 19:15:18.000000000
(1 rows)