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

Question

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.

Possible duplicate of [Parsing a date’s ordinal indicator ( st, nd, rd, th ) in a date-time string](https://stackoverflow.com/questions/28514346/parsing-a-date-s-ordinal-indicator-st-nd-rd-th-in-a-date-time-string) — Basil Bourque, Apr 14 '18 at 20:23

score 1 · Answer 1 · answered Apr 13 '18 at 08:38

1

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"
        }
      }
    }
  }
}

answered Apr 13 '18 at 08:38

Val

207,596
13
358
360

I don't think this is the answer. Using this I can't filter by time in Kibana. If I try to parse my time in Java with JodaTime using your filter, I get an exception. I don't think it supports the || operator. See my edit to the original post. – Gamer1120 Apr 13 '18 at 09:50
Of course you can filter, but you need to use the same format at query time. Can you show your query as well? – Val Apr 13 '18 at 09:52
I've set Kibana to use the syslog-timestamp as a filter for the time. However, if I open the Discover interface, I get no results, even when I go 5 years back. https://i.imgur.com/7OxRo0P.png If I set it to @timestamp (which I don't want) it works fine. If I go to the console and use a search, I can see the data just fine. https://i.imgur.com/cCOBE15.png – Gamer1120 Apr 13 '18 at 10:00

score 0 · Answer 2 · answered Apr 13 '18 at 11:12

0

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.

answered Apr 13 '18 at 11:12

Gamer1120

236
1
8

Not sure what you mean by `||` doesn't work. This is something that works in ES when you need to specify multiple date formats to be supported. It has nothing to do with Joda or Java. – Val Apr 13 '18 at 11:17
I think the issue is somewhere else. I'm going to delete this question since it doesn't help anyone. – Gamer1120 Apr 13 '18 at 11:55

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

2 Answers2