Get startedGet started for free

Extracting hours from a time value

Your team has been tasked with generating a summary report to better understand the hour of the day when most parking violations are occurring. The violation_time field has been imported into the database using strings consisting of the hour (in 12-hour format), the minutes, and AM/PM designation for each violation. An example time stored in this field is '1225AM'. Note the lack of a colon and space in this format.

Use the TO_TIMESTAMP() function and the proper format string to convert the violation_time into a TIMESTAMP, extract the hour from the TIME component of this TIMESTAMP, and provide a count of all parking violations by hour issued. The given conversion to a TIME value is performed because violation_time values do not include date information.

This exercise is part of the course

Cleaning Data in PostgreSQL Databases

View Course

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

SELECT
  -- Convert violation_time to a TIMESTAMP
  ___(___, ___)::TIME AS violation_time
FROM
  parking_violation
WHERE
  -- Exclude NULL violation_time
  ___ IS NOT ___;
Edit and Run Code