Customizing date formats
As date and time can be broken down into individual components like month, day, year, hour, minute, etc., you can format your date or time values to only show the information you want within your charts.
You can use these modifiers in the Data Pipeline using the Custom Formula option in the Add Column or Edit Column Pipeline steps. If you want to use modifiers in your initial query against your data source, check your corresponding data source SQL syntax to make sure you’re using the correct modifier associated with that data source type.
You can use the modifiers listed above in format functions using the following SQLite syntax:
For example, if you have a column named “Day of Created Date” which includes information that’s too granular for my needs, you can refine it to only include the year, month, and day in your preferred order using the formula below:
strftime('%Y-%m-%d',"Day of Created Date")
For the most common variations like date or datetime, you can use functions that will apply the format to your whole column such as:
Other things to do with dates and times
You can modify dates and times and do computations with them. Modifiers can be appended to any of the following functions: date(), time(), datetime(), and julianday(). The units below can be used as part of the formulas.
These modifiers can be used to modify your date or time. For example, in the screenshot below, we’re adding 1 month and 2 days to the “Day of Created Date” column using the following formula structure:
date("Column Name",'+interval unit','+interval unit')
Note: The modifiers can also be negative values if you’d like to subtract values from your date or datetime columns. The second interval unit is also optional.
For additional math functions, Chartio has the following built-in functions available in the Pipeline using SQLite:
dateadd(date_string,amount,'unit')- Adds a time interval to a date or datetime.
datesub(date_string,amount,'unit')- Subtracts a time interval from a date or datetime.
datediff(date_string1,date_string2,'unit')- Calculates the difference between two dates or datetimes.
unitis optional; the formula will default to days if no unit is specified.
datepart(date_string,'unit')- Extracts a part of the date string.
The units available to be used within the above functions are:
- week (datediff only)
Create a granular date range filter
There may be a situation where a more granular date range selection including a specific time of day is required. This example uses our PostgreSQL Document Company Demo Data Data Source. In PostgreSQL, depending on your data, you may have a datetime field you’d like to use to filter your data.
When you drag a date field to the filters, a dropdown of options are presented but with the absence of a time selection. You can instead filter using a timestamp in the Data Pipeline by following the steps below:
- Open the Users table and drag User Id into the Measures field.
Next, to be able to filter between a specific time, you’ll need to add a date column to your query. Here, we’ll add the Created Date column from the same table into the Dimensions field. Then, click on the column and select Second of Users.Created Date from the Time Bucket dropdown to keep the column as a datetime. Click Ok then Run Query.
Scroll down to Pipeline, click + Add Transformation and select Filter Rows from the list. You can then set your precise filter conditions here using the Custom Input option.
In this example, we’re filtering to include data for a time greater than 2017-02-17T00:05:00 and less than 2017-02-17T00:44:40. This final filtering step will depend on your chart requirements. You may need to hide the initial Second of Created Date column or add a Group Rows step if there are duplicate values.
- If you only needed the date column to filter your results, add a Hide Columns Pipeline step after completing this.