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

Exercise

Risky parking behavior

The parking_violation table contains many parking violation details. However, it's unclear what causes an individual to violate parking restrictions. One hypothesis is that violators attempt to park in restricted areas just before the parking restrictions end. You have been asked to investigate this phenomenon. You first need to contend with the fact that times in the parking_violation table are represented as strings.

In this exercise, you will convert violation_time, and to_hours_in_effect to TIMESTAMP values for violations that took place in locations with partial day restrictions, calculate the interval between the violation_time and to_hours_in_effect for these records, and identify the records where the violation_time is less than 1 hour before to_hours_in_effect.

Instructions 1/3

undefined XP
    1
    2
    3
  • Convert violation_time and to_hours_in_effect to TIMESTAMP values using TO_TIMESTAMP() and the appropriate format string. ::TIME converts the value to a TIME.
  • Exclude locations having both a from_hours_in_effect value of 1200AM and a to_hours_in_effect value of 1159PM.