Documentation Charts FAQs

Use 12-hour format with AM and PM labels

To bucket data by hour of the day and to display it in a 12-hour format, set the columns to 12-hour clock, with AM and PM labels.

In the Visual SQL interface

  1. Add the datetime column to your query and select Hour of Day in the column’s Aggregation menu in the Result Table.

  2. Add a Formula Column Action in the Pipeline, select Custom as the formula type and use the following formula to cast the datetime column as an integer (replace Created Date with the name of your column).

    cast("Created Date" as integer)
    
  3. For clarity, we renamed our newly added Custom Formula column to “Hour” using a Rename Column Action; however, renaming the column is optional.

    Rename the Custom Formula column to "Hour"

  4. Add another Formula Column, again selecting the Custom formula type, and use the following CASE statement to change the Hour column to a 12-hour format with AM and PM labels:

    case when ("Hour" = 0) then "12 AM" when "Hour" = 12 then ("Hour" || " PM")
    when "Hour" < 12 then ("Hour" || " AM") else (("hour"-12) || " PM") end
    
  5. We rename the new Custom Formula column to “Hour of Day” for clarity.

    "Hour of Day" column shows 12-hour format with AM and PM labels

  6. Hide the original datetime column and the “Hour” column from Step 2 then Reorder the “Hour of Day” column to the front (i.e., leftmost column in the Result Table).

    Use the Hide Column and Reorder Column Actions to format your Result Table

  7. Choose the chart type to best represent your data and style it as you see fit!

    Final result


In the Data Explorer interface

  1. Drag a datetime column into the Dimensions field. Select Hour of Day in the Time Bucket dropdown menu and change the Label to Hour for clarity.

    Add a Hidden variable

  2. Add an Edit Column step in the Pipeline, select Custom as the formula type, and cast the datetime column as an integer.

    Click Apply & Close to save the step settings.

    Add an Edit Column step in the Pipeline

  3. Use an Add Column step in the Pipeline to add a new column. Select Custom formula as the formula type and use the following CASE statement to change the Hour column to a 12-hour format with AM and PM labels:

    case when ("Hour" = 0) then "12 AM" when "Hour" = 12 then ("Hour" || " PM")
    when "Hour" < 12 then ("Hour" || " AM") else (("hour"-12) || " PM") end
    

    Click Apply & Close to save the step settings.

    Add an Add Column step in the Pipeline

  4. Add a Hide Columns step to hide the unnecessary column from Step 1 and use a Reorder Columns step to move the Hour of Day column to the far-left of the table.

    Use the Hide Column and Reorder Column Actions

  5. Choose the chart type to best represent your data and style it as you see fit!

    Final result