Datetime field

[fulmicoton]

We want a richer experience when dealing with datetime.

Ideally it should be an actual type...
Its configurations should make ingestion should be more flexible

- name: dt
  type: datetime
  input_format: "rfc3339" #  "unix_ts_secs" |  "unix_ts_millis"  | "unix_ts_micros" | "unix_ts_nanos"  
  fast: true
  precision: secs | millis | micros | nanos (default: secs)

We may also want to have a better indexing scheme... ( ie index days for instance)

[guilload]

People do crazy things with dates. Should we support a custom date format expressed in the syntax supported by the time crate? That does not seem like much extra work.

[fulmicoton]

That's a good idea... It already includes "well-known" formats. I am not sure we can extend the syntax with our own formats, but checking if the format is in a dictionary and then trying the time crate's format description parse function should do the job

[fmassot]

We may also want to have a better indexing scheme... ( ie index days for instance)

@fulmicoton not sure to understand what you meant, can you elaborate?

[fulmicoton]

Dates is something we want to run range queries on.
Right we do split pruning + fast field filtering at collection.

That works ok if the datetime field is indeed the timestamp field, but that is not necessarily the case.

THe normal way to do range queries on int fields is any variant of trie indexing.
We could just offer that on dates too... But it is tempting to, instead of using a base decomposition, align the decomposition of the date into the way human decompose dates.

Inverted list for hours & day specifically should be more efficient than an arbitrary base development.

[me-diru]

@fmassot continuing the conversation from discord here.

I made a small specification document to change and go through later on.

Few more questions I got are:

1. If, currently, we are using any data type to store the date information, how are we able to filter using search API?
   startTimeStamp, endTimeStamp -> use i64 specifically 🤔

2. why does higher accuracy lower the filtering speed/ increase the doc store space? accuracy doesn't change the number of documents/splits to go through I think?

Coming to the specification, I think this is a good start: let me know your thoughts on this, thanks

- name: timestamp
  type: datetime
  input_format:  should support any rfc3339 format and "unix_ts_secs"  "unix_ts_millis"  "unix_ts_micros" "unix_ts_nano". 
  fast: true

• If not specified, it assumes "date_optional_time (or) unix_ts_millis"
• I think we don't have to have precision while sending data. It is much easier to store the data using i64 and in unix_ts_millis only.
• We will use the input_format given before from metastore to send the data back in user given format. This way we can be flexible with the input data and also convert it into user desirable format. On that note, we should just leave the endTimeStamp and startTimeStamp to be in unix_ts_millis and i64 only. It might be better to add a new datetime-format variable to search API in the future to get the data in different formats other than user given format.

This will cover the resolution only till milliseconds, since we are not offering other functions(like aggregations/others) that work on nanoseconds, it would be better to not include that at the moment? elasticsearch has a similar limitation on their nano-date type. I presumed this, if we offer rich support for nanoseconds everywhere, then going with nanos as the base option or providing a new nano type or something else might be a better option.

[kstaken]

I have a question related to this. Is there a particular reason to be focusing on RFC3339 only instead of ISO8601? RFC3339 has the huge downside of requiring a fully specified time including timezone for all datetimes. In Elasticsearch date_optional_time is not a valid RFC3339 datetime.

[stranger-danger-zamu]

Continuing the thread from Discord:

Storage wise, whatever is most reasonable. There might be room for certain optimizations, but they're premature and can be done out of band through preprocessing steps.

For me the big thing is the ability to supply several parsing methods for a single datetime field and to support custom datetime parsing.

A simple example is twitter's datetime format which has changed several times over the years. Being able to supply the three or four strptime strings to parse any twitter record would be awesome. Usually, being lossy with other fields is fine, but not being able to parse datetime fields is painful.

And sometimes you do just have to parse a weird datetime format. The ability to just bang out a strptime string and ingest directly without requiring a preprocessing step would save so much effort.

That said, having human readable shorthands for known datetime formats (ie. rfc3339, iso8601, unix_ts_millis) is needed since they'll work for 95% of cases.

So using me-diru's spec for the twitter example gives:

- name: created_on
  type: datetime
  input_formats:
    - "unix_ts_millis"
    - "rfc3339"
    - "iso8601"
    - "%a %b %d %H:%M:%S %z %Y"
  fast: true

Each input_format should be applied in the order until it successfully parses.

Overall, it might slow down ingest, but I'd have to write that same code in an other process anyways and I'd much rather have it be in rust and not have to spin up another process or system just for something like datetime parsing.

You could also just have input_formats be an enum over a single input_format and a vec of input_formats so datasets with known formats could just skip the loop entirely.

Anyways, at the bare minimum having the ability to pass strptime strings for datetime parsing is a must.

[fulmicoton]

@kstaken Not much reasons. We should support several format. I am the culprit for sprinkling rfc3339 here and there in docs, and this is not an educated choice.

@stranger-danger-zamu Having several input formats may also be an interesting way to create "default configs" use cases.
I'm a bit scared of possible side effects and added complexity though.