Modifying Time in Chartio - Tips and Tricks


There are several ways to modify your time data in Chartio using features from our data source or data pipeline. Check out a few tips below:

Changing Real-time data to a cached version in charts

If you connect your data warehouse to Chartio, Chartio will query your data warehouse directly to return the results. Therefore, your charts’ results will be reflecting real-time data as soon as the charts are set to update.

If you would like to reduce the load on your warehouse and you do not need real-time data, take a look at our docs on our caching and Data Stores.

Changing Unix Integer columns to Time Stamps

It is difficult for Chartio to automatically recognize Unix timestamps when pulling the schema from databases, as in the schema they are marked simply as integer columns. You can, however, specify which columns are Unix time stamps by changing the Type attribute of the relevant columns in the schema editor.

For more information, see our documentation on how you can change your column’s data type using a custom column.

Once the columns are specified, the Data Explorer will automatically apply FROM_UNIXTIME functions to those columns when the queries are generated.

12 Hour Display with “AM” and “PM” Labels

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

Select Hour of Day in the Time Bucket Dropdown menu.

Add a Hidden variable

Add an Edit Column step in the Pipeline to as an integer.

Add an Edit Column step in the Pipeline

Add a new column and use a Case Statement to change the hours in a 12 Hour Clock Time of Day with AM and PM labels with the SQLite statement below:

CASE WHEN (“Hour” = 0) THEN “12 AM” WHEN “Hour” = 12 THEN (“Hour” || " PM") 
WHEN “Hour” < 12 THEN (“Hour” || " AM") ELSE ((“hour”-12) || " PM") END

Add a Hide Column step to hide the unnecessary column and reorder so the Hour of Day is the left-hand column.

Final result

Business Vs. Non-Business Hours

It is possible in Chartio to adjust the dates of reporting, such as differentiating between weekends from the typical Monday-Friday business hours. In this example, we want to add some overtime pay onto employee wages for weekends they worked.

  1. Drag Shift into the Dimensions field twice and select different Time Buckets: one for the Day (or Date) of Shift and the other for Day of Week.

    Set up your chart by dragging shift into the Dimensions field twice

  2. In the Pipeline, add a Combine Columns step to combine the hourly wages and hours worked per day columns. Select the option to hide the original columns so that the calculation will happen behind the scenes.

    Add Combine Columns step in the Pipeline

  3. Add an Edit Column Pipeline step and use a case statement to edit the Gross Pay per Day column to add time-and-a-half pay to weekend days:

    CASE WHEN "Day of Week: Shift" LIKE 'S%' THEN 1.5*("Gross Pay per Day")
    ELSE "Gross Pay per Day" END

    Edit Column using a case statement

Related General Help Articles

See more