1. Learn
    2. /
  2. Courses
    3. /
  3. Cleaning Data in PostgreSQL Databases

Exercise

Extracting time units with SUBSTRING()

In a previous exercise, you separated the interval between the violation_time and to_hours_in_effect columns into their constituent hour and minute time units. Some pre-cleaning of these values was done behind the scenes to make the values more amenable for conversion because of inconsistencies in the recording of these values. The functions explored in this lesson provide an approach to extract values from strings.

In this exercise, you will use SUBSTRING() to extract the hour and minute units from time strings. This is an alternative approach to extracting time units removing the need to convert the string to a TIMESTAMP value to extract the time unit as was done previously.

Instructions 1/2

undefined XP
    1
    2
  • Define the hour column as the substring starting at the 1st position in violation_time and extending 2 characters in length.