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.
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 an Edit Column step in the Pipeline to as an integer.
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.
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.
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.
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 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