Converting local time to ISO 8601 time in Elasticsearch

Now posted on Elastic’s website

Nov 7, 2019 – This article has now been published on Elastic’s website:  https://www.elastic.co/blog/converting-local-time-to-iso-8601-time-in-elasticsearch

Introduction

If a timestamp field is sent into Elasticsearch without any timezone information, then it will be assumed to be UTC time (Coordinated Universal Time). However, if the timestamp actually represents a local time, then assuming that it is UTC will likely cause problems when displaying the data in Kibana or other applications. In this blog I describe how to use an ingest pipeline to convert timestamps from a local timezone into universal timestamps that conform to the ISO 8601 date and time format.

Converting timestamps from local into universal time

If the timezone of the originating data is known, then it is possible to use an ingest processor to convert from the local time to ISO 8601 format. Below is an example ingest pipeline that uses the date processor to convert the field called ‘my_time’ from ‘Europe/Madrid’ time into ISO 8601 (UTC + offset) format.

PUT _ingest/pipeline/chage_local_time_to_iso
{
  "processors": [
    {
      "date" : {
        "field" : "my_time",
        "target_field": "my_time", 
        "formats" : ["dd/MM/yyyy HH:mm:ss"],
        "timezone" : "Europe/Madrid"
      }
    }
  ]
}

In order to test the above pipeline, we can execute the following code to simulate inserting a document:

POST _ingest/pipeline/chage_local_time_to_iso/_simulate
{
  "docs": [
    {
      "_source": {
        "my_time": "12/10/2019 21:31:12",
        "other_field": "whatever"
      }
    }
  ]
}

The above will respond with output that looks as follows, which we can see has the correct ISO 8601 offset for ‘Europe/Madrid’ for October 12th when daylight savings is in effect. As expected, the field ‘my_time’ shows an offset of ‘+02:00’.

{
  "docs" : [
    {
      "doc" : {
        "_index" : "_index",
        "_type" : "_doc",
        "_id" : "_id",
        "_source" : {
          "my_time" : "2019-10-12T21:31:12.000+02:00",
          "other_field" : "whatever"
        },
        "_ingest" : {
          "timestamp" : "2019-10-16T19:28:20.999077Z"
        }
      }
    }
  ]
}

We can also verify that the above pipeline is respecting daylight savings (which ends on October 27th in Spain) by submitting a document with a date of October 30th as follows:

POST _ingest/pipeline/chage_local_time_to_iso/_simulate
{
  "docs": [
    {
      "_source": {
        "my_time": "30/10/2019 21:31:12",
        "other_field": "whatever"
      }
    }
  ]
}

Which responds with the following output, that has an offset of ‘+01:00’ as expected since daylight savings is no longer in effect:

{
  "docs" : [
    {
      "doc" : {
        "_index" : "_index",
        "_type" : "_doc",
        "_id" : "_id",
        "_source" : {
          "my_time" : "2019-10-30T21:31:12.000+01:00",
          "other_field" : "whatever"
        },
        "_ingest" : {
          "timestamp" : "2019-10-16T19:30:40.401948Z"
        }
      }
    }
  ]
}

Finally, we can insert a “real” document into Elasticsearch as follows:

PUT test_index/_doc/1?pipeline=chage_local_time_to_iso
{
  "my_time": "15/10/2019 01:11:55",
  "other_field": "whatever"
}

And we can retrieve the document as follows:

GET test_index/_doc/1

Which should respond with the following, that has the correct offset of ‘+02:00’ on Oct 15th for the ‘Europe/Madrid’ timezone:

{
  "_index" : "test_index",
  "_type" : "_doc",
  "_id" : "1",
  "_version" : 1,
  "_seq_no" : 0,
  "_primary_term" : 1,
  "found" : true,
  "_source" : {
    "my_time" : "2019-10-15T01:11:55.000+02:00",
    "other_field" : "whatever"
  }
}

Conclusion

In this blog we have demonstrated how Elasticsearch ingest processors can be used to ingest data into Elasticsearch with unambiguous ISO 8601 timestamps.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s