Autodetection of date formats for timestamp fields

[Nick-S-2018]

Autodetection of date formats for timestamp fields

[sanikolaev]

@Nick-S-2018 pls check what timezone ES uses when it's not provided, e.g.:

2019-03-23T21:34:46

You also wanted to add more examples with timezones.

[Nick-S-2018]

In such case, ES uses the UTC time without setting any default time zone.
I've added the examples with string time zones to the format list above.

[Nick-S-2018]

This issue blocks manticoresoftware/buddy-plugin-insert#6

[sanikolaev]

To think through:

• support timestamp64 and timestamp vs changing timestamp to always be bigint
• there's another related issue about storing datetime formats

[cappadaan]

After moving from ES to Manticore, this is by far the biggest hurdle we had to take during migration.
Would be nice if the auto-detection could be implemented.

[tomatolog]

Here is the list of date formats that are auto detected by Elastic:

yyyy-MM-ddTHH:mm:ss.SSSZ
2019-03-23T21:34:46.123-04:00
2019-03-23T21:34:46Asia/Novosibirsk

yyyy-MM-dd'T'HH:mm:ssZ
2019-03-23T21:34:46-04:00
2019-03-23T21:34:46Asia/Novosibirsk

yyyy-MM-ddTHH:mm:ss.S+
2019-03-23T21:34:46.1234567

yyyy-MM-ddTHH:mm:ss
2019-03-23T21:34:46

yyyy-MM-ddTHH:mm
2019-03-23T21:34

yyyy-MM-ddTHH
2019-03-23T21 

yyyy-MM-dd
2019-03-23

yyyy-MM
2019-03

but we also should support plain timestamp string along with these that allow to remove @timestamp constraint on conversion from the string

• need also to bench the speed of that list of conversions
• need to add string to timestamp conversion into plain index sources

[sanikolaev]

we also should support plain timestamp string

Yes, and what's called epoch_millis in Elasticsearch too since Logstash / Beats etc. can output logs in this format. As discussed, it's ok to cast that to int for now.

[tomatolog]

added list of the date formats for timestamp fields along with conversion of string for any timestamp attribute at 973703b

[tomatolog]

the benchmark of the conversion on insert data (batch of 10 documents) into RT table with timestamp attribute

insert data	laptop qps	linux qps
ins_tm_num.sql	9365.5	1513.8
ins_tm_num_str.sql	9306.8	1497.3
ins_tm_str2.sql	9009.5	1484.0
ins_tm_str1.sql	8759.8	1350.0
ins_tm_str_data.sql	5850.6	941.8

• ins_tm_num.sql replace into t (id, tid) values (10,1568577600), ...
  • here is data population of the timestamp integer without any conversion
• ins_tm_num_str.sql replace into t (id, tid) values (10,'1568577600'), ...
  • here is data population of the timestamp with conversion of the timestamp from the string value
• ins_tm_str2.sql replace into t (id, tid) values (10,'2019'), ...
• ins_tm_str1.sql replace into t (id, tid) values (10,'2019-10-15T20:00:00'), ...
  • here are data population of the timestamp with conversion of the timestamp from the string value with scan of the formats to find one that fits the data string
• ins_tm_str_data.sql replace into t (id, tid, title) values (10,'2019-10-15T20:00:00', 'Lorem ipsum dolor sit amet ...
  • here are data population of the timestamp with conversion of the timestamp from the string value with scan of the formats to find one that fits the data string and also has short (128 chars) text data

seems conversion from the string is slower than use integer timestamp however it should not be noticeable while inserting the real data along with timestamp attributes

[tomatolog]

documented conversion at the manual 33dc37f

[tomatolog]

this issue was fixed but the development will continue at

• #2156 for plain index data source conversion
• #421
• https://github.com/manticoresoftware/dev/issues/476
  • for timestamp64 or custom data format for conversion