# ES|QL query message format **URL:** \ **Category:** Elasticsearch\ **Created:** [June 13, 2025, 7:21pm UTC](https://discuss.elastic.co/t/es-ql-query-message-format/379171 "2025-06-13T19:21:48Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![pruna](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pruna/32/143666_2.png) [@pruna](https://discuss.elastic.co/u/pruna)\ **Post date:** [June 13, 2025, 7:21pm UTC](https://discuss.elastic.co/t/es-ql-query-message-format/379171/1 "2025-06-13T19:21:48Z")
Hi! I need to query elements for a specific month in a date field. For that, I'm trying my first ES|QL query, as follows: ```auto { "query": """ FROM facts | WHERE DATE_PART('month', date_start) = '06' """ } ``` by doing a POST to /\_query?format=txt , using Postman. If I define the raw body as Text, I get this error: ```auto { "error": "Content-Type header [text/plain] is not supported", "status": 406 } ``` And if I try JSON, I get a different error about incorrect format (see below). How should I configure it to be accepted by ES? ```auto { "error": { "root_cause": [ { "type": "x_content_parse_exception", "reason": "[2:16] Unexpected character ('\"' (code 34)): was expecting comma to separate Object entries\n at [Source: (byte[])\"{\r\n \"query\": \"\"\"\r\nFROM facts\r\n| WHERE DATE_PART('month', date_start) = '06'\r\n \"\"\"\r\n}\"; line: 2, column: 16]" } ], "type": "x_content_parse_exception", "reason": "[2:16] Unexpected character ('\"' (code 34)): was expecting comma to separate Object entries\n at [Source: (byte[])\"{\r\n \"query\": \"\"\"\r\nFROM facts\r\n| WHERE DATE_PART('month', date_start) = '06'\r\n \"\"\"\r\n}\"; line: 2, column: 16]", "caused_by": { "type": "json_parse_exception", "reason": "Unexpected character ('\"' (code 34)): was expecting comma to separate Object entries\n at [Source: (byte[])\"{\r\n \"query\": \"\"\"\r\nFROM facts\r\n| WHERE DATE_PART('month', date_start) = '06'\r\n \"\"\"\r\n}\"; line: 2, column: 16]" } }, "status": 400 } ``` ---
**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\ **Post date:** [June 13, 2025, 8:23pm UTC](https://discuss.elastic.co/t/es-ql-query-message-format/379171/2 "2025-06-13T20:23:57Z")
Hi @pruna Welcome to the community... Use `raw` with type `json` The triple quotes don't work that is a kibana convention so yours will be ```auto { "query": "FROM facts | WHERE DATE_PART('month', date_start) = '06'" } ``` ![Screenshot 2025-06-13 at 1.30.45 PM](https://us1.discourse-cdn.com/elastic/original/3X/d/4/d448bca6ec43de5197c21cffaa2dd9e2b584a397.png) ---
**Author:** ![pruna](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pruna/32/143666_2.png) [@pruna](https://discuss.elastic.co/u/pruna)\ **Post date:** [June 14, 2025, 12:13am UTC](https://discuss.elastic.co/t/es-ql-query-message-format/379171/3 "2025-06-14T00:13:36Z")
Hi @stephenb , thank for the welcome, and the fast response! Yes, I was using **raw**. The change from triple quotes to just double quotes and one line did the trick. Now I can run ES|QL queries, but I'm struggling to get my month comparison working. Here's a list of several alternatives I tried, all reporting different errors: ```auto FROM facts | WHERE match(DATE_FORMAT(date_start, \"MM\"), \"06\") FROM facts | WHERE DATE_EXTRACT(\"month\", date_start) : 6 FROM facts | WHERE match(DATE_EXTRACT(\"month\", date_start),6) FROM facts | WHERE match(DATEPART(\"month\", date_start),6) FROM facts | WHERE match(EXTRACT(MONTH FROM date_start),6) FROM facts | WHERE EXTRACT(MONTH FROM date_start)=6 ``` The closer one seems to be the one using DATEPART, where the error says: > Unknown function [DATEPART], did you mean any of [date\_parse, date\_format, date\_extract, date\_trunc]? In the manual page I found [Date/time and interval functions and operators | Reference](https://www.elastic.co/docs/reference/query-languages/sql/sql-functions-datetime) it has that function, and not some of the alternatives presented. ---
**Author:** ![Tortoise](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tortoise/32/147587_2.png) [@Tortoise](https://discuss.elastic.co/u/Tortoise)\ **Post date:** [June 14, 2025, 1:46am UTC](https://discuss.elastic.co/t/es-ql-query-message-format/379171/4 "2025-06-14T01:46:04Z")
Hello @pruna I tried below and was able to extract the logs for particular month : ```auto FROM kibana_sample_data_logs | WHERE DATE_EXTRACT("month_of_year", @timestamp) == 6 ``` ![image](https://us1.discourse-cdn.com/elastic/original/3X/b/5/b584e05d7afb15b26e12c1fb6048ab481b20c83e.png) Thanks!! ---
**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\ **Post date:** [June 14, 2025, 2:43am UTC](https://discuss.elastic.co/t/es-ql-query-message-format/379171/5 "2025-06-14T02:43:34Z")
Hi @pruna You have to provide sample documents and mappings etc if you want help with a query otherwise it is just guessing on our part.... So provide some sample documents and their mapping and perhaps we can help. ---
**Author:** ![RainTown](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raintown/32/140206_2.png) [@RainTown](https://discuss.elastic.co/u/RainTown)\ **Post date:** [June 14, 2025, 11:16am UTC](https://discuss.elastic.co/t/es-ql-query-message-format/379171/6 "2025-06-14T11:16:55Z")
> [@pruna](#): > > In the manual page I found [Date/time and interval functions and operators | Reference](https://www.elastic.co/docs/reference/query-languages/sql/sql-functions-datetime) it has that function, and not some of the alternatives presented. Note the `/sql/` in the path there. That's SQL, not ES|QL, docs. For ES|QL you can look at: > **[ES|QL date-time functions | Reference](https://www.elastic.co/docs/reference/query-languages/esql/functions-operators/date-time-functions)** > > ES|QL supports these date-time functions: Its not clear you level of Elastic knowledge here. If in early stages I would suggest to use Kibana --\> Discover to build your knowledge - e.g. the link to the ES|QL docs is right there in kibana. ---
**Author:** ![pruna](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pruna/32/143666_2.png) [@pruna](https://discuss.elastic.co/u/pruna)\ **Post date:** [June 14, 2025, 2:59pm UTC](https://discuss.elastic.co/t/es-ql-query-message-format/379171/7 "2025-06-14T14:59:40Z")
Thanks @Tortoise , @RainTown & stephenb (I can only mention 2 in the post) for your answers. It's amazing to see this level of participation. Trying to answer to you all: That date query worked ok, thanks. I've been using Elasticsearch for several years now, but always as a goal-oriented tool, so my knowledge is very limited to what I need at each time. I use it for an educational project I made (an interactive historical atlas) **edumaps.ar**. I only use Elasticsearch, without Kibana or any other components from the stack. As I wanted to add an anniversaries search, I started to look for a way to query only month & date pieces of a date, and that bring me into SQL, ES|QL, etc, which I didn't use before. If I can simply do it with traditional queries, I'll prefer that. Also because I see now that the output format is quite different. About providing sample info, you are completely right, here would be some: ```auto PUT /anniversaries { "mappings": { "properties": { "id": { "type": "keyword" }, "name": { "type": "text" }, "date_start": { "type": "date" }, "date_end": { "type": "date" } } } } POST /anniversaries/_bulk?pretty {"index":{"_index":"anniversaries","_id":"1"}} {"id":"1","name":"Juan","date_start":"1832-07-23","date_end":"1883-04-30"} {"index":{"_index":"anniversaries","_id":"2"}} {"id":"2","name":"Pablo","date_start":"1880-07-14","date_end":"1881-03-24"} {"index":{"_index":"anniversaries","_id":"3"}} {"id":"3","name":"Luis","date_start":"1581-07-14","date_end":"1599-09-03"} POST /_query { "query": "FROM anniversaries | WHERE DATE_EXTRACT(\"month_of_year\", date_start) == 7 AND DATE_EXTRACT(\"day_of_month\", date_start) == 14" } ``` ---
**Author:** ![RainTown](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raintown/32/140206_2.png) [@RainTown](https://discuss.elastic.co/u/RainTown)\ **Post date:** [June 14, 2025, 4:19pm UTC](https://discuss.elastic.co/t/es-ql-query-message-format/379171/8 "2025-06-14T16:19:35Z")
thanks for clear answers/background. One thing you might wish to consider is an ingest pipeline, which could extract the dayofweek/dayofmonth/etc fields at indexing time, populating those fields in your index. You would have to re-index existing data too, but ti would make queries more easy on the eye. I'd also suggest to setup an instance of kibana, if only to test different things out. I think in long run it would save you time (e.g. kibana has autocompletion, inbuilt help, and IMO is just quicker once you are used to it. From your shared data ```auto FROM anniversaries | EVAL monthOfYearStart=DATE_EXTRACT("month_of_year", date_start), monthOfYearEnd=DATE_EXTRACT("month_of_year", date_end), dayOfMonthStart=DATE_EXTRACT("day_of_month", date_start), dayOfMonthEnd=DATE_EXTRACT("day_of_month", date_end) | WHERE monthOfYearStart == 7 AND dayOfMonthStart == 14 ``` seems to me to do the job ![Screenshot 2025-06-14 at 18.18.59](https://us1.discourse-cdn.com/elastic/original/3X/9/5/95973db1c3fcc77fae1d2bbda9a4ee768bb2ccad.jpeg) ( btw, someone more knowledgeable than me might know why kibana/ESQL part of Discover is _displaying_ Luiz's `date_start` as 13th July 1581 at 23:58:45, and similar for `date_end` !? A little googling found that the Gregorian calendar was adopted in 1582, might be related )