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.
Cet exercice fait partie du cours
Cleaning Data in PostgreSQL Databases
Exercice interactif pratique
Essayez cet exercice en complétant cet exemple de code.
SELECT
-- Convert violation_time to a TIMESTAMP
___(___, ___)::TIME AS violation_time
FROM
parking_violation
WHERE
-- Exclude NULL violation_time
___ IS NOT ___;