Duration column

Use a string formatted value to upsert a duration value into a 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.
Note: Provide values for a duration without quotes.

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
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 a duration.
    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 a duration.
    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';