Session Ready
Exercise

Extracting hours from a time value

In an effort to better understand the hour of the day when most parking violations are occurring, your team has been tasked with generating a summary report to provide this information. 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'.

In this exercise, you will 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 the violation_time includes no date information.

Instructions 1/2
undefined XP
  • 1
  • 2
  • Convert violation_time to a TIMESTAMP using the TO_TIMESTAMP() function and a format string including 12-hour format (HH12), minutes (MI), and meridian indicator (AM or PM). ::TIME converts the resulting timestamp value to a TIME.
  • Exclude records with a NULL-valued violation_time.
    Loading