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
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 ___;