CODEWITHSUNDEEP
elasticsearchjodatime
Gamer1120
Gamer1120

Reputation: 236

Parsing the date "April 10th 2018, 07:32:45.987" in Elasticsearch

Heyo, I'm trying to create an index pattern using Kibana, and for that I need to parse the date "April 10th 2018, 07:32:45.987" in Elasticsearch. My problem is the "th" after the 10. Elasticsearch documentation points me to the joda-time documentation: http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html , but that does not tell me how it can ignore or parse those two characters. So far I have : 

PUT mynewindex
{
  "mappings": {
    "mytype" : {
      "properties": {
        "syslog-timestamp" : {
          "type" : "date",
          "format" : "MMM dd?? yyyy, HH:mm:ss.SSS"
        }
      }
    }
  }
}

What should replace my question marks?

In Java it would look like this:

import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;

public class JodaTimeTest {


    public JodaTimeTest() {
        String timeString = "April 10th 2018, 07:32:45.987";
        DateTimeFormatter formatter = DateTimeFormat.forPattern("MMM dd'th' yyyy, HH:mm:ss.SSS||MMM dd'nd' yyyy, HH:mm:ss.SSS||MMM dd'rd' yyyy, HH:mm:ss.SSS");
        DateTime dt = formatter.parseDateTime(timeString);
    }

    public static void main(String[] args) {
        new JodaTimeTest();
    }
}

I've tried the given solution, but that results in an:

Exception in thread "main" java.lang.IllegalArgumentException: Invalid format: "April 10th 2018, 07:32:45.987" is too short
    at org.joda.time.format.DateTimeFormatter.parseDateTime(DateTimeFormatter.java:945)
    at JodaTimeTest.<init>(JodaTimeTest.java:11)
    at JodaTimeTest.main(JodaTimeTest.java:15)

Extra information:

GET myindex/_search
{
  "query": {
        "range" : {
            "date" : {
                "gt" :  "now"
            }
        }
    }
}

returns nothing, and 

GET myindex/_search
{
  "query": {
        "range" : {
            "date" : {
                "lt" :  "now"
            }
        }
    }
}

also returns nothing.

Upvotes: 0

Views: 383

Answers (2)

Gamer1120
Gamer1120

Reputation: 236

Val's answer doesn't work, since the || operator is not supported. For this limited test I wrote a script to remove the st, nd and rd from the logfiles. If the test is successful, haproxy will be changed to output the date without st, nd and rd.

Upvotes: 0

Val
Val

Reputation: 217554

You need to do it like this and also account for dates with nd and rd:

PUT mynewindex
{
  "mappings": {
    "mytype" : {
      "properties": {
        "syslog-timestamp" : {
          "type" : "date",
          "format" : "MMM dd'th' yyyy, HH:mm:ss.SSS||MMM dd'nd' yyyy, HH:mm:ss.SSS||MMM dd'rd' yyyy, HH:mm:ss.SSS"
        }
      }
    }
  }
}

Upvotes: 1

Related Questions