# Rollup with max/min aggregation on @timestamp field typed as "float"

**URL:** <https://discuss.elastic.co/t/rollup-with-max-min-aggregation-on-timestamp-field-typed-as-float/317735>\
**Category:** Elasticsearch\
**Tags:** rollups\
**Created:** [October 29, 2022, 7:06pm UTC](https://discuss.elastic.co/t/rollup-with-max-min-aggregation-on-timestamp-field-typed-as-float/317735 "2022-10-29T19:06:06Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![ISim](https://avatars.discourse-cdn.com/v4/letter/i/3da27b/32.png) [@ISim](https://discuss.elastic.co/u/ISim)\
**Post date:** [October 29, 2022, 7:06pm UTC](https://discuss.elastic.co/t/rollup-with-max-min-aggregation-on-timestamp-field-typed-as-float/317735/1 "2022-10-29T19:06:07Z")

</div>

Hello all,

I have a index with accounting events with @timestamp (type "date"). There is a high volume of events so I created rollup job (elasticsearch 7.17.3) with

```auto
              "date_histogram" : {
                "delay" : "2h",
                "field" : "@timestamp",
                "time_zone" : "Europe/Prague",
                "calendar_interval" : "1d"
              },

```

with key terms (session, IP address,...) and required metrics (transfered data,...).

Because I need to know when the session started and finished exactly, I added metrics min and max for @timestamp field. The idea was to query elastic with

```auto
GET /rollup-sessions-daily/_search
{
  "size": 0, 
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "Session-Id.terms.value": "2eb6d2c49e32b7f18edbb4de6f3d960f"
          }
        }
      ]
    }
  },
  "aggs": {
    "startedAt": { "min": {"field": "@timestamp.min.value"}},
    "lastEventAt": {"max": {"field": "@timestamp.max.value"}}
  }
}

```

to get the exact time of the first and last session occurrence. Sessions sometimes run over days or weeks, sometimes start and finish the same day.

This approach works except one thing: min and max field are typed as float in rollup index ☹ This is a little bit inconvenient.

The rollup job API created mapping:

```auto
      "properties" : {
        "@timestamp" : {
          "properties" : {
            "date_histogram" : {
              "properties" : {
                "_count" : {
                  "type" : "long"
                },
                "interval" : {
                  "type" : "keyword"
                },
                "time_zone" : {
                  "type" : "keyword"
                },
                "timestamp" : {
                  "type" : "date"
                }
              }
            },
            "max" : {
              "properties" : {
                "value" : {
                  "type" : "float"
                }
              }
            },
            "min" : {
              "properties" : {
                "value" : {
                  "type" : "float"
                }
              }
            }
          }
        },

```

Is there any solution to keep `@timestamp.min.value` and `@timestamp.max.value` as `date`?

I also run into a problem with field typed as "ip". Although I can aggregate query using "terms" with this field

```auto
GET /events-*/_search
{
  "size": 0,
  "aggs": {
    "IP": {
      "terms": {
        "field": "IP-Address"
      }
    }
  }
}

```

the type "ip" is not allowed for rollup aggregations. It is described in documentation "_The terms group can be used on `keyword` or numeric fields to allow bucketing via the `terms` aggregation at a later point_"). Of course, I read this statement later then I needed....

I tried to create transformation job (pivoted index) - the same terms and metrics and created mapping is correct for @timestamp.max, @timestamp.min and even "ip" type is not an issue for aggregation.

```auto
     "properties" : {
        "@timestamp" : {
          "properties" : {
            "max" : {
              "type" : "date"
            },
            "min" : {
              "type" : "date"
            }
          }
        },
        "IP-Address" : {
          "type" : "ip"
        },
...

```

But running transform job is time and resource expensive for large set of events.

Thanks in advance for any hint.  
Ivan

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [October 31, 2022, 7:47am UTC](https://discuss.elastic.co/t/rollup-with-max-min-aggregation-on-timestamp-field-typed-as-float/317735/2 "2022-10-31T07:47:19Z")

</div>

You should setup an [index template](https://www.elastic.co/guide/en/elasticsearch/reference/8.4/index-templates.html) to do that for you, as the resultant rollup index is the same as a normal one.

---

<div class="post-metadata">

**Author:** ![ISim](https://avatars.discourse-cdn.com/v4/letter/i/3da27b/32.png) [@ISim](https://discuss.elastic.co/u/ISim)\
**Post date:** [October 31, 2022, 3:12pm UTC](https://discuss.elastic.co/t/rollup-with-max-min-aggregation-on-timestamp-field-typed-as-float/317735/3 "2022-10-31T15:12:52Z")

</div>

Hello Mark,  
thank you for your reply. I created template for rollup index as you mentioned above but I got my log flooded with:

```auto
org.elasticsearch.index.mapper.MapperParsingException: failed to parse field [@timestamp.max.value] of type [date] in document with id 'isp-rollup-radacct-sessions-daily-2$-korUCu3MF6sbSA1eOFwSg'. Preview of field's value: '1.65
1599427E12'
        at org.elasticsearch.index.mapper.FieldMapper.parse(FieldMapper.java:255) ~[elasticsearch-7.17.3.jar:7.17.3]
[...cut...]
Caused by: java.lang.IllegalArgumentException: failed to parse date field [1.651599427E12] with format [strict_date_optional_time||epoch_millis]

```

Well, it looks the float value could not be parsed with "epoch\_milis" format of date type. So I created pipeline

```auto
{
  "processors": [
    {
      "convert": {
        "field": "@timestamp.max.value",
        "type": "long",
        "on_failure": [
          {
            "set": {
              "description": "Set 'error.message'",
              "field": "error.message",
              "value": "Field @timestamp.max.value conversion error",
              "override": false
            }
          },
          {
            "set": {
              "field": "@timestamp.max.value",
               "value": 0
            }
          }
        ]
      }
    },
    {
      "convert": {
        "field": "@timestamp.min.value",
        "type": "long",
        "on_failure": [
          {
            "set": {
              "description": "Set 'error.message'",
              "field": "error.message",
              "value": "Field @timestamp.min.value conversion error",
              "override": false
            }
          },
          {
            "set": {
              "field": "@timestamp.min.value",
               "value": 0
            }
          }
        ]
      }
    }
  ]
}

```

and set the pipeline as default index pipeline.

Again, I failed - the indexed document looks like (\_source field has more field but I left only important fields here):

```auto
     {
        "_index" : "index-name",
        "_type" : "_doc",
        "_id" : "index-name-v4$AFnuE_v_Bc5BuJSwfF0oMQ",
        "_score" : 1.0,
        "_ignored" : [
          "@timestamp.max.value",
          "@timestamp.min.value"
        ],
        "_source" : {
          "@timestamp.date_histogram.time_zone" : "Europe/Prague",
          "@timestamp.max.value" : 1.650318963E12,
          "error" : {
            "message" : "Field @timestamp.max.value conversion error"
          },
          "Acct-Input-Packets.max.value" : 3988737.0,
          "@timestamp.date_histogram._count" : 134,
          "Acct-Unique-Session-Id.terms._count" : 134,
          "Acct-Unique-Session-Id.terms.value" : "5464f1a2a9d35d482f7e2d50166ce23d",
          "@timestamp.date_histogram.interval" : "1d",
          "@timestamp" : {
            "min" : {
              "value" : 0
            },
            "max" : {
              "value" : 0
            }
          },
          "_rollup.version" : 2        
        }
      },

```

The pipeline processor couldn't reach field with name "@timestamp.max.value" because expects object, not "flat" field with dots. Am I right?

Again, thank you for any hint.

Ivan

---

<div class="post-metadata">

**Author:** ![ISim](https://avatars.discourse-cdn.com/v4/letter/i/3da27b/32.png) [@ISim](https://discuss.elastic.co/u/ISim)\
**Post date:** [November 11, 2022, 1:46pm UTC](https://discuss.elastic.co/t/rollup-with-max-min-aggregation-on-timestamp-field-typed-as-float/317735/4 "2022-11-11T13:46:44Z")

</div>

So, I currently use runtime fields to convert float values of @timestamp.min and @timestamp.max values to date type fields:

```auto
PUT /rollup-sessions-daily/_mappings
{
  "runtime": {
    "@timestamp.first_seen": {
      "type": "date",
      "script": """
          emit((long)doc['@timestamp.min.value'].value);
          """
    },
        "@timestamp.last_seen": {
      "type": "date",
      "script": """
          emit((long)doc['@timestamp.max.value'].value);
          """
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [December 9, 2022, 1:47pm UTC](https://discuss.elastic.co/t/rollup-with-max-min-aggregation-on-timestamp-field-typed-as-float/317735/5 "2022-12-09T13:47:27Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.