Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[ES|QL] date_parse seems to ignore timezone #117680

Closed
kiju98 opened this issue Nov 28, 2024 · 4 comments · Fixed by #118603
Closed

[ES|QL] date_parse seems to ignore timezone #117680

kiju98 opened this issue Nov 28, 2024 · 4 comments · Fixed by #118603
Assignees
Labels
:Analytics/ES|QL AKA ESQL >bug Team:Analytics Meta label for analytical engine team (ESQL/Aggs/Geo)

Comments

@kiju98
Copy link

kiju98 commented Nov 28, 2024

Elasticsearch Version

8.16.0

Installed Plugins

No response

Java Version

bundled

OS Version

Linux amd64 5.15.0-1032-gcp

Problem Description

POST /_query
{
    "query": """
row message = "192.168.1.199 - - [12/Jul/2022:10:24:10 +0900] \"GET /cgi-bin/try/ HTTP/1.0\" 200 3005"
| grok message "%{COMMONAPACHELOG}"
| keep timestamp
| eval @timestamp = date_parse("dd/MMM/yyyy:HH:mm:ss Z", timestamp)
"""
}

produces

{
  "took": 15,
  "columns": [
    {
      "name": "timestamp",
      "type": "keyword"
    },
    {
      "name": "@timestamp",
      "type": "date"
    }
  ],
  "values": [
    [
      "12/Jul/2022:10:24:10 +0900",
      "2022-07-12T10:24:10.000Z"
    ]
  ]
}

but

POST /_ingest/pipeline/_simulate
{
  "pipeline": {
    "processors": [
      {
        "date": {
          "field": "timestamp",
          "formats": [
            "dd/MMM/yyyy:HH:mm:ss Z"
          ]
        }
      }
    ]
  },
  "docs": [
    {
      "_index": "index",
      "_id": "id",
      "_source": {
        "timestamp": "12/Jul/2022:10:24:10 +0900"
      }
    }
  ]
}

produces

{
  "docs": [
    {
      "doc": {
        "_index": "index",
        "_version": "-3",
        "_id": "id",
        "_source": {
          "timestamp": "12/Jul/2022:10:24:10 +0900",
          "@timestamp": "2022-07-12T01:24:10.000Z"
        },
        "_ingest": {
          "timestamp": "2024-11-28T07:11:48.58392818Z"
        }
      }
    }
  ]
}

I suspect date_parse in ES|QL ignores the timezone offset. Will you look into it, please?

Steps to Reproduce

You can run

POST /_query
{
    "query": """
row message = "192.168.1.199 - - [12/Jul/2022:10:24:10 +0900] \"GET /cgi-bin/try/ HTTP/1.0\" 200 3005"
| grok message "%{COMMONAPACHELOG}"
| keep timestamp
| eval @timestamp = date_parse("dd/MMM/yyyy:HH:mm:ss Z", timestamp)
"""
}

, which produces

{
  "took": 15,
  "columns": [
    {
      "name": "timestamp",
      "type": "keyword"
    },
    {
      "name": "@timestamp",
      "type": "date"
    }
  ],
  "values": [
    [
      "12/Jul/2022:10:24:10 +0900",
      "2022-07-12T10:24:10.000Z"
    ]
  ]
}

I think @timestamp should be "2022-07-12T01:24:10.000Z".

Logs (if relevant)

No response

@kiju98 kiju98 added >bug needs:triage Requires assignment of a team area label labels Nov 28, 2024
@astefan astefan added :Analytics/ES|QL AKA ESQL and removed needs:triage Requires assignment of a team area label labels Nov 28, 2024
@elasticsearchmachine elasticsearchmachine added the Team:Analytics Meta label for analytical engine team (ESQL/Aggs/Geo) label Nov 28, 2024
@elasticsearchmachine
Copy link
Collaborator

Pinging @elastic/es-analytical-engine (Team:Analytics)

@iverase
Copy link
Contributor

iverase commented Nov 29, 2024

Per documentation, ES|QL only supports UTC at the moment (e.g it does not support timezones) : https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-limitations.html#esql-limitations-timezone

@kiju98
Copy link
Author

kiju98 commented Nov 29, 2024

Thank you for the answer. That's sad though :(
I hope the timezone support will be added soon.

@bpintea
Copy link
Contributor

bpintea commented Nov 29, 2024

Not sure if the lack of support for UTC might be it. I think parsing should read the timezone as is and then only output in UTC, b/c we support no other TZs. I'll have a look.

@bpintea bpintea self-assigned this Nov 29, 2024
elasticsearchmachine pushed a commit that referenced this issue Dec 19, 2024
This just removes fixing a formatter to a timezone (UTC), allowing
`DATE_PARSE` to correctly read timezones.

Fixes #117680.
bpintea added a commit to bpintea/elasticsearch that referenced this issue Dec 19, 2024
This just removes fixing a formatter to a timezone (UTC), allowing
`DATE_PARSE` to correctly read timezones.

Fixes elastic#117680.
bpintea added a commit to bpintea/elasticsearch that referenced this issue Dec 19, 2024
This just removes fixing a formatter to a timezone (UTC), allowing
`DATE_PARSE` to correctly read timezones.

Fixes elastic#117680.
bpintea added a commit to bpintea/elasticsearch that referenced this issue Dec 19, 2024
This just removes fixing a formatter to a timezone (UTC), allowing
`DATE_PARSE` to correctly read timezones.

Fixes elastic#117680.
elasticsearchmachine pushed a commit that referenced this issue Dec 19, 2024
This just removes fixing a formatter to a timezone (UTC), allowing
`DATE_PARSE` to correctly read timezones.

Fixes #117680.
elasticsearchmachine pushed a commit that referenced this issue Dec 19, 2024
This just removes fixing a formatter to a timezone (UTC), allowing
`DATE_PARSE` to correctly read timezones.

Fixes #117680.
elasticsearchmachine pushed a commit that referenced this issue Dec 19, 2024
This just removes fixing a formatter to a timezone (UTC), allowing
`DATE_PARSE` to correctly read timezones.

Fixes #117680.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
:Analytics/ES|QL AKA ESQL >bug Team:Analytics Meta label for analytical engine team (ESQL/Aggs/Geo)
Projects
None yet
Development

Successfully merging a pull request may close this issue.

5 participants