How to transform date string in Druid

Question

I am adding a Druid ingestion spec and one my fields called maturityDate is a string in the format "dd/MM/yyyy". Sample data: 5/01/2023

I am using Druid UI console and in the Transform phase, adding the following expression:

timestamp("maturityDate", "dd/MM/yyyy")

But as I apply this transform, Druid results in an error. Can someone suggest the right way of converting a string to long (millis since epoch) in Druid?

Answer 1

You can use the following way:

TIME_PARSE('05/01/2023', 'dd/MM/yyyy')

Additional short tips for druid usage:

• be aware between " and ' usages, because it's two different options;
• according to druid docs: druid metadata tables (such as "sys") doesn't support almost all aggregations

INFORMATION_SCHEMA tables do not currently support Druid-specific functions like TIME_PARSE