Conserve a date or datetime data type

Have you ever faced the problem where your date or datetime field is converted to a string (i.e., text) data type? When this happens you can no longer use the field with date functions like datediff, strftime, etc. When a date or datetime is converted to a string, this can also affect the way it’s sorted. Although this can be frustrating at first, once you understand why this is happening, you can use steps to overcome this problem while still achieving the date format you want.

When a date is converted to a string, it’s most likely due to the bucket method used for the column. In order to compare, sort, or filter dates or datetimes, you must have your column formatted as a date or datetime data type. To conserve this data type, you must bucket the field by the second for datetimes and by the day for dates.

The reason you must bucket by the second or day is because a valid date must consist of a day, month, and year. If you bucket a date by the month, it’s no longer a valid date data type because it doesn’t contain a value for the day. The same thing goes for datetime fields. A datetime must contain the second, minute, hour, day, month, and year to be valid. If you have all of those values then you can use the available functions to compare and manipulate dates and datetimes.

Here is an example table that shows how Chartio treats and classifies each value based on the bucket method used. You can see that only two columns maintain their original data type instead of being converted to a string.

In the Data Explorer interface

So how do I fix this? You’ll need to keep your data bucketed by the second or day until all filtering and sorting is complete. Once that’s done, you can use the Pipeline and the strftime function to turn your date into the desired format to be displayed on the final chart.