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.
This exercise is part of the course
Cleaning Data in PostgreSQL Databases
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
summons_number,
-- Convert violation_time to a TIMESTAMP
___(___, 'HH12MIPM')::TIME as violation_time,
-- Convert to_hours_in_effect to a TIMESTAMP
___(___, 'HH12MIPM')::TIME as to_hours_in_effect
FROM
parking_violation
WHERE
-- Exclude all day parking restrictions
NOT (___ = '1200AM' AND ___ = '1159PM');