Manage dates

How to :: manage your dates format

Overview¶

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:

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.

make sure dates are understood as such

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

Timezone-less 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 21/03/2006 21:50:00 (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 2006-03-22T05:50:00), no 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 timestamp like 1143006600, they will also be stored and display as is.

Tip

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 -07:00 offset).

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à

This is definitely not perfect as it will not take daylight savings into account.

If you’re using this kind of trick, we’d really like to here more about your use case!

Note

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.

Charts parameters¶

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 :

change the way dates are displayed by default in your charts

• X Ticks Format (tickformat in 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 (hideXTickLimits in code mode) : turn on this option to hide the first and last tick

• Display all X Ticks (displayAllXTicks in 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

Note

For the barchart, stackedbarchart and the barlinechart, the tickformat parameter is also available in code mode. Check their reference pages :

• barchart

• barlinechart

• stacked barchart

Postprocess¶

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!

Filters / Requesters¶

The filter slider or a requester is especially recommended to display dates. It has specific options you can use with dates :

• some date format parameters

• you can put the ‘default’ parameter on “last” to set it on the last date by default

• you can order it on specific column

Order dates¶

If your dates need to be ordered, use the order option of your graph like labelsOrder or groupsOrder for example.

Make sure the date is either displayed in the following format:

• Year - month

or

• 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