You have 1:HOURS in there?

but this is causing issue

"dateTimeFieldSpecs": [
    {
      "name": "Timestamp",
      "dataType": "STRING",
      "format": "1:HOURS:SIMPLE_DATE_FORMAT:dd/MM/yyyy HH:mm:ss",
      "granularity": "1:SECONDS"
    }
  ]

I think you need to escape

:

this is used as delimeter

its resolved now @Xiang Fu.. as per new pinot release as mentioned by @Rong R.. dd then mm then yyyy is no more supported .. it should be necassarily lexicographical date format ie yyyy followed by MM followed by dd

correct. because non lexicographically ordering format will cause the date-time ordering to be wrong. However I highly doubt the use case you have here is using this column as a "STRING". yes? in that case I suggest convert this into a long/epoch field before storing it.

Thanks ! @Mark Needham can you help add this into FAQ doc maybe

@Rong R what would be the use case for having a date string as a 'STRING' column type?

However I highly doubt the use case you have here is using this column as a "STRING". yes? =? yes we are giving data type as string and providing the format and I think internally .. the pinot is converting that string into date based on date format and input string .. this conversion from string to date is easily possible using any small java function

final SimpleDateFormat inputFmt = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'");
	final SimpleDateFormat outputFmt = new SimpleDateFormat("dd/MM/yyyy HH:mm");
	final SimpleDateFormat outputFmtLex = new SimpleDateFormat("yyyy/MM/dd HH:mm");
	final SimpleDateFormat outputFmtWithSecond = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
	final SimpleDateFormat outputFmtWithSecondLex = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
	final TimeZone zone = TimeZone.getTimeZone("UTC");

	public DateTransform() {
		inputFmt.setTimeZone(zone);//"UTC"));
		outputFmt.setTimeZone(zone);
		outputFmtLex.setTimeZone(zone);
		outputFmtWithSecond.setTimeZone(zone);
		outputFmtWithSecondLex.setTimeZone(zone);
	}

public   AllowFlowL3L4V2 dateTransform(AllowFlowL3L4V2 pojo, SimpleDateFormat inputFormat, SimpleDateFormat outputFormat,  String dateInput) {
		String newDateInput;
		if (dateInput.length()>22) {
			newDateInput = dateInput.substring(0,22);
			newDateInput = newDateInput+"Z";
		}
		else
			newDateInput = dateInput;
		Date d = null;
		try {
			d = inputFormat.parse(newDateInput);//"2018-02-02T06:54:57.744Z");
		} catch (ParseException e) {
			logger.error(e);
		}
		String formatted = "";
		try {
			formatted = outputFormat.format(d);
		} catch (NullPointerException e) {
			logger.error(e);
		}
		logger.debug("DATE " + formatted);
		pojo.setTime(formatted);
		return pojo;
	}

makes sense. I was more wondering why wouldn't you store the data as a

TIMESTAMP

field type, which IMO gives you the best of both worlds! You're able to see what the actual date is, as well as perform date calculations over the field. I wrote a couple of blog posts showing how to do it. https://www.markhneedham.com/blog/2021/12/07/apache-pinot-exploring-range-queries/ https://www.markhneedham.com/blog/2021/12/03/apache-pinot-convert-datetime-string-timestamp-invalid-timestamp/

This helps.. Thanks @Mark Needham

I guess this really depends on how the data is being used when queried from Pinot - if in most of the cases row of strings are being queried and returned so that downstream program can consume (as a specific string date time format) then it would be ideal to store in string format as there will be no conversion cost from timestamp (probably stored in Long although I am not 100% sure) to string. what I meant by “_*highly doubt the use case you have here is using this column as a “STRING”.*_ is there’s no reason to do

>=

or

<=

comparison on this date time string column - which is the limiting factor for why we can’t allow

mm/dd/yyyy

format because of the single-storage-format / 2-sorting-orders dilemma. I guess a more systematic way to fix this issue is to provide a new dataType called “TIMESTAMP” and “DATETIME”. which only support sorting in epoch order. while “TIMESTAMP” returns a “LONG” basic type, and “DATETIME” returns a “STRING” basic type

yep .. there seems to be issues if stored as string and doing comparisons without having lexicographical format .. but since its now fixed with lexicographical format and no issues with

>=

 or 

<=

 comparison on this date time string column ... thanks @Rong R for the detailed explanation

@Sadim Nadeem in the blog you mentioned, is there a way to provide regex to the fromDatetimefunction. As in my case, the timestamp im getting has varying number of decimal for milliseconds, which I would just like to ignore after 3rd decimal.