Oracle_SQL partial _ time conversion (case 1)

Oracle_SQL partial _ time conversion (case 1)

Question:

Colleagues feedback that the time-consuming recorded in the XXX system is a character type. It is not convenient to calculate the time-consuming statistics through execl. Ask me if I have a good method.

Train of thought:

The data contains days, hours and minutes, and the character type cannot be calculated directly. The data of days, hours and minutes can be separated separately through the oracle database, that is, days and hours are converted into minutes. In the end, the time-consuming field is only minutes, which is convenient for later calculation.

The steps are as follows:

1 Import exec data into Oracle database

2 query data

(3) the days, hours and hours can be separated by intercepting the string through substr and obtaining the position by instr.

The longest time-consuming select process, nvl (substr (longest process time, 1, instr (longest process time, 'days',-1)-1), 0) day from process monitoring

-hours

Select

The longest time-consuming process

Substr (longest process time, instr (longest process time, 'days') + 1, instr (longest process time, 'hours')-instr (longest process time, 'days')-1 hour

From

Process monitoring

Select

The longest time-consuming process

Substr (longest process time, instr (longest process time, 'hours') + 2, instr (longest process time, 'minutes')-instr (longest process time, 'hours)-2 minutes

From

Process monitoring

4 convert days and hours into minutes, and calculate the total number of minutes

Select

The longest time-consuming process

(days)-1) * 60 + substr (longest process time, instr (longest process time, 'hours') + 2, instr (longest process time, 'minutes')-instr (longest process time, 'hours)-2)

From

Process monitoring

Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!