An apparently trivial challenge that still took me bit longer than expected. What is the situation: I have a table with two columns (of interest). One is a column called gap, with values like these:

Each value represents a time difference. These values are strings – not much use to calculate with. Therefore a second column in the table – called timegap – is defined, of type INTERVAL DAY TO SECOND. The challenge I am facing is to set the value of this column timegap based on the time difference described by the string in column gap.

With a little bit of SQL agility, using for example some regular expressions and an interesting update on [the result of] an inline view, it turns out to be doable in a fairly simple SQL statement.

(more…)