Documentation Visual SQL (beta) FAQs

SQLite date and time functions in Visual SQL

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 Pipeline using the Custom Formula option in the Calculated Column Action. 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.

Format Definition Example
%d day of month 01-31 (up to 31 days)
%f fractional seconds 11.111 (SS.SSS)
%H hour 00-24 (24 hours)
%j day of year 01-366 (365 days, 366 if it’s a leap year)
%J Julian day number continuous count of days since the beginning of the Julian period
%m month 01-12 (Jan-Dec)
%M minute 00-59 (60 minutes)
%s seconds convert date/time value to seconds since 1970-01-01
%S seconds 00-59 (60 seconds)
%w day of week 0-6 (where Sunday = 0)
%W week of year 00-53 (ISO WEEKS)
%Y year 0000-9999

You can use the modifiers listed above in format functions using the following SQLite syntax:

strftime('format',"date/time_column_name")

For example, if you have a column named “Created Date” that includes information too granular for your needs (e.g., contains time info like hours, minutes, seconds), you can refine it to only include the year, month, and day in your preferred order using the formula below:

strftime('%Y-%m-%d',"Created Date")

Calculated Column Action to format to date to year-month-day

For the most common variations like date or datetime, you can use functions that will apply the format to your whole column such as:

Function Equivalent strftime()
date(…) strftime(‘%Y-%m-%d’,…)
time(…) strftime(‘%H:%M:%S’,…)
datetime(…) strftime(‘%Y-%m-%d %H:%M:%S’,…)
julianday(…) strftime(‘%J’,…)

Other things to do with dates and times

You can modify dates and times and do computations with them. You can append modifiers to any of the following functions: date(), time(), datetime(), and julianday(). The units below can be used as part of the formulas:

  • NNN days
  • NNN hours
  • NNN minutes
  • NNN.NNNN seconds
  • NNN months
  • NNN years

These modifiers can be used to modify your date or time. For example, you can use the following formula structure for date():

date("Column Name",'+interval unit','+interval unit')

In the screenshot below, we add one month and two days to the “Day of Created Date” column:

date("Created Date",'+1 months','+2 days')

Use date modifiers to add 1 month and 2 days to the Day of Created Date column

Note: The modifiers can also be negative values if you want 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. unit is 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:

  • day
  • week (datediff() only)
  • month
  • year
  • hour
  • minute
  • second

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 SaaS 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:

  1. From the Users table, add the User Id and Created Date columns to the Columns section.
  2. If you want to filter between a specific time, you need to change the time bucket of the datetime column. In this example, select Second from the Created Date’s aggregation menu then click Run Query.

    Create a chart and change the time bucket of Created Date

  3. Now add a Filter Rows Action, where you can set your precise filter conditions.

    Add a Filter Rows Action to filter data by a specific time

    In this example, we filter to include data for a time greater than 2019-05-13T01:00:00 and less than 2019-05-13T02:59:59. This final filtering step depends on your chart requirements.

  4. If you only needed the date column to filter your results but don’t need to include the date column in your final result set, add a Hide Column Action to hide the Created Date.