# Can one specify format of datetime in query?
**URL:** https://discuss.elastic.co/t/can-one-specify-format-of-datetime-in-query/299263
**Category:** Elasticsearch
**Created:** [March 9, 2022, 9:48pm UTC](https://discuss.elastic.co/t/can-one-specify-format-of-datetime-in-query/299263 "2022-03-09T21:48:22Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@Russell\_Fulton](https://discuss.elastic.co/u/Russell_Fulton)
#### Post date: [March 9, 2022, 9:48pm UTC](https://discuss.elastic.co/t/can-one-specify-format-of-datetime-in-query/299263/1 "2022-03-09T21:48:23Z")
Is there a way of specifying the format of times used in a query? In most of my indexes I convert times to ISO8601 but one data source had a totally non standard format and I decided that it was easier to simply specify a custom format in the mapping.
Now I am trying to extract data using the API and I find that I have to use the same data format as the \_source.
---
### Author:  [@casterQ](https://discuss.elastic.co/u/casterQ)
#### Post date: [March 10, 2022, 2:37am UTC](https://discuss.elastic.co/t/can-one-specify-format-of-datetime-in-query/299263/2 "2022-03-10T02:37:29Z")
Once you successfully insert data as date type，you can query in any data format
```auto
PUT data
{
"mappings": {
"properties": {
"date": {
"type": "date"
}
}
}
}
PUT data/_doc/1
{ "date": "2015-01-01" }
PUT data/_doc/2
{ "date": "2015-01-01T12:10:30Z" }
PUT data/_doc/3
{ "date": 1420070400001 }
POST data/_search
{
"query": {
"range": {
"date": {
"gte": "2014-01-01T12:10:30Z",
"lte": "1520070400001"
}
}
}
}
```
---
### Author:  [@Russell\_Fulton](https://discuss.elastic.co/u/Russell_Fulton)
#### Post date: [March 10, 2022, 3:06am UTC](https://discuss.elastic.co/t/can-one-specify-format-of-datetime-in-query/299263/3 "2022-03-10T03:06:01Z")
Thanks Caster, that's what I expected!
So why do I get:
```auto
query >>>> index dns_ds {:size=>10000, :sort=>[{:@timestamp=>{:order=>"asc"}}], :query=>{:bool=>{:must=>[{:range=>{:@timestamp=>{:gte=>"02-Mar-2022 00:00:00 +1300", :lte=>"04-Mar-2022 00:00:00 +1300"}}}]}}}
in rescue [400] {"error":{"root_cause":[{"type":"parse_exception","reason":"failed to parse date field [02-Mar-2022 00:00:00 +1300] with format [dd-MMM-yyyy HH:mm:ss.SSS Z]:
```
mapping for @timestamp:
```auto
"@timestamp": {
"format": "dd-MMM-yyyy HH:mm:ss.SSS Z",
"index": true,
"ignore_malformed": false,
"store": false,
"type": "date",
"doc_values": true
},
```
not the missing millisecs.
I get parse errors if I use anything other than the exact format in the mapping.
---
### Author:  [@casterQ](https://discuss.elastic.co/u/casterQ)
#### Post date: [March 10, 2022, 3:19am UTC](https://discuss.elastic.co/t/can-one-specify-format-of-datetime-in-query/299263/4 "2022-03-10T03:19:17Z")
you should customize your own date format，as below
```auto
PUT data2
{
"mappings": {
"properties": {
"date": {
"type": "date",
"format": "dd-MMM-yyyy HH:mm:ss Z||strict_date_optional_time||epoch_millis"
}
}
}
}
POST data2/_doc
{
"date": "02-Mar-2022 00:00:00 +8"
}
POST data2/_doc
{
"date": "2015-01-01T12:10:30Z"
}
POST data2/_search
```
docs is here：
> **[format | Elasticsearch Guide \[8.1\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/8.1/mapping-date-format.html#built-in-date-formats)**
---
### Author:  [@casterQ](https://discuss.elastic.co/u/casterQ)
#### Post date: [March 10, 2022, 3:20am UTC](https://discuss.elastic.co/t/can-one-specify-format-of-datetime-in-query/299263/5 "2022-03-10T03:20:08Z")
not `02-Mar-2022 00:00:00 +8`
try your `04-Mar-2022 00:00:00 +1300`
---
### Author:  [@Russell\_Fulton](https://discuss.elastic.co/u/Russell_Fulton)
#### Post date: [March 11, 2022, 1:47am UTC](https://discuss.elastic.co/t/can-one-specify-format-of-datetime-in-query/299263/6 "2022-03-11T01:47:47Z")
: ). (timezones )
and thanks! So if you specify format in mapping you need to include the mappings that you want to search with too.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [April 8, 2022, 1:48am UTC](https://discuss.elastic.co/t/can-one-specify-format-of-datetime-in-query/299263/7 "2022-04-08T01:48:07Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.