How to :: manage your dates format¶
You will often have to play with dates in your Toucan application and there are a few tips you need to know to become a Toucan date master ! 📅
There are 2 steps you will likely use to display dates in your charts:
- in the dataset editor : make sure your date column is properly interpreted
- in the configuration of your graph : decide how you want the date to be displayed
Ensure columns have the appropriate type¶
“Date” is a specific data type, like numeric, text or boolean. When crafting a query, you can easily check if a column has the “date” type by looking at the icon next to its header:
If it’s not, either:
- change the configuration of the data source (file or connector) to make sure dates are understood as such,
- or cast the column directly by clicking on the type icon.
Avoid dealing with timezones (when possible)¶
Toucan apps are global: they can be visited from users all around the world. Meaning all users won’t necessarily be on the same timezone. But we still want them to easily agree on the data they see and be able to talk about it! So to avoid mental calculation with timezone offsets and any related misunderstanding, Toucan will display dates the same for all users, wherever they are.
In a nutshell :
- dates provided without timezone information are stored and displayed as is
- while dates provided with timezone information are stored and displayed as GMT dates
This is the most common use case, both for flat files (Excel/CSV) & databases because:
- people don’t manually input dates with timezone info in spreadsheets (at least 99% of the time)
- the SQL standard requires the “default” datetime type to be equivalent to “timestamp without time zone” & databases like Snowflakes or PostgreSQL honors that behavior
So when providing dates without timezone information, like
21/03/2006 21:50:00, Toucan will display
(aka the same date), no matter where you are on the planet. The current
user timezone is not taken into account when displaying dates, period.
Dates with timezone information¶
This use case appear when using data from a database or an API that has such info encoded in its dates. It may not play well with the “dates-in equal dates-out” behavior but there is ways to help mitigating this case for most users.
When providing dates with an explicit timezone offset like
2006-03-21T21:50:00.000-07:00 (GMT-7 offset), they will be stored
and displayed as their GMT equivalent (here
matter where you are on the planet again.
Also, if your dates already are already on GMT, either explicitly like
2006-03-22T05:50:00.000Z (‘Z’ mean GMT) or implicitly as a GMT
1143006600, they will also be stored and display as
If you have enough control over your input data, you should consider
providing your dates to Toucan directly in the timezone you’d like them
to be displayed (like
2006-03-21T21:50:00) but without any timezone
info (like a
If not applicable and you “only” need to display dates in a single timezone other than GMT, you can shift them using the following succession of data transformation steps :
- convert dates to numbers, this will give you the corresponding GMT milliseconds timestamp
- use the formula step to apply your timezone offset, in milliseconds
offset_hours * 60 * 60 * 1000)
- convert the result from numbers to dates again and voilà
If you have a use case where you’d like to display dates in line with the current user timezone, please contact us, we’d like to here from you !
Use date parameters¶
Once Toucan understands your column is a date column, you can apply date parameters. This will allow you to modify how the date is displayed for your end-users for example.
You might want to change the way dates are displayed by default in your charts. In order to do just that, our linechart, particularly well-suited to display dates, has a built-in options that will allow you to manage dates. It’s called the ticks parameters.
In your optional parameters, you will be able to setup :
- X Ticks Format (
tickformatin code mode) : write here the format you wish to be displayed according to the same syntax we saw previously (ex: “%Y”)
- Hide Start and End Ticks (
hideXTickLimitsin code mode) : turn on this option to hide the first and last tick
- Display all X Ticks (
displayAllXTicksin code mode) : by default, Toucan displays a certain amount of ticks according to design rules. If you wish all you ticks, turn on that option
Two other options only available in code mode for the linechart are :
- dateFormat : write down the date format you wish to display in the legend
- selectLastDate : turn on that option to always display first the last date available
If your chart does not have a tickformat parameter and you need to modify how it’s diplayed, no worries ! We have a postprocess function to help you out :
Check out our change_date_format function.
Just define the output format you want!
If your dates need to be ordered, use the order option of your graph
groupsOrder for example.
Make sure the date is either displayed in the following format:
- Year - month
- Month - day
Why ? Because the order column sorts ascendingly and that way dates will be properly sorted. Ex: 201801 will be before 201802
Other cool dates stuff¶
- Date is one of the 2 most structuring variable you can define in a Toucan App : learn how to build the best date selector
- You can add aggregation columns based on the date : check out these postprocess functions
- compute_cumsum : to compute to date values;
- compute_evolution_by_frequency : to compute evolution values