How to Conserve a Date or Datetime Data Type

Charts

How to Conserve a Date or Datetime Data Type

Have you ever faced the problem where your date or datetime field is converted to 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 text this can also affect the way it is sorted. Although this can be frustrating at first, once you understand why this is happening you can employ steps to overcome this problem while still achieving the date format you want.

When a date is converted over to text it is 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 is no longer a valid date data type because it does not 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 over to text.

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


Related Charts Help Articles

See more