should datetime Elasticsearch's date for Elasticsearch query runner.

[toyama0919]

Hello.

I use Elasticsearch query runner.
Please advice.

I think Elasticsearch date is Re:dash datetime.

Mapping

"some_type": {
  "properties": {
    "@timestamp": {
      "type": "date",
      "format": "dateOptionalTime"
    },
    "url": {
      "type": "string"
    },
    "uuid": {
      "type": "string"
    }
  }
}

Data

{
  "@timestamp": "2016-05-25T18:49:26+09:00"
}

table view appeared 2016-05-25.

2 problem

• Elasticsearch's date is Re:dash's datetime.
• Elasticsearch @timestamp have timezone.
  • visualize localtime, need convert without timezone for localtime?

Thanks.

[arikfr]

Is this still an issue? @adamlwgriffiths do you know?

[adamlwgriffiths]

I'm still on the angular 1.2 branch.
We have experienced this and worked around it.
When we need granularity we explicitly request a formatted version of the date (found below).

Viewing the results of queries in a Table visualization I get the following:

• postgres/redshift: 2017-01-16 00:03.
• ES: 17/01/17.

However, if ask for a you specific datetime format from ES you can get the same format as postgres.

...
  	"aggs": {
            "transactions_by_date": {
               "date_histogram": {
                  "field": "timestamp",
                  "interval": "minute",
                  "format" : "yyyy-MM-dd'T'HH:mm:ss"
               },
 ...

The raw values of these are:

• "timestamp": "2017-01-17T23:03:35.566Z"
• "transactions_by_date": "2017-01-17T23:07:00" (ignore time difference, it's from a different query result).

The transactions_by_date value is listed as a string:
"columns": [..., {"friendly_name": "transactions_by_date", "name": "transactions_by_date", "type": "string"}]
It could be that requesting a formatted date type is working due to the string sorting properly, and the graphing libraries handling the string -> date conversion automatically.

Looking at the ES query runner, the date type is mapped to TYPE_DATE instead of TYPE_DATETIME, which means the time will be trimmed unless explicitly requested via the formatted aggregation above.

ELASTICSEARCH_TYPES_MAPPING = {
  "date": TYPE_DATE,
...

I'm not sure if it's safe to convert this to a straight TYPE_DATETIME. ES doesn't seem to differentiate them in the mappings, which is where this conversion information is retrieved from.
They are instead listed as a date with an optional time.
https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping.html#_field_datatypes
https://www.elastic.co/guide/en/elasticsearch/reference/current/date.html

GET "<url>/_mappings"
...
"timestamp":{"type":"date","format":"dateOptionalTime"}
...

[adamlwgriffiths]

Changing the type mapping from TYPE_DATA to TYPE_DATETIME seems to work, but my values all include a time, I'm not sure if this will be an issue with values that are only dates.

I've tried to trace the code to find the conversion to see if it's safe, but I can't find it.