# Convert keyword field to date for range query
**URL:** https://discuss.elastic.co/t/convert-keyword-field-to-date-for-range-query/365121
**Category:** Elastic Search
**Created:** [August 19, 2024, 9:03am UTC](https://discuss.elastic.co/t/convert-keyword-field-to-date-for-range-query/365121 "2024-08-19T09:03:22Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@NEWBIENEET](https://discuss.elastic.co/u/NEWBIENEET)
#### Post date: [August 19, 2024, 9:03am UTC](https://discuss.elastic.co/t/convert-keyword-field-to-date-for-range-query/365121/1 "2024-08-19T09:03:22Z")
One of the fields in the ES that my team used is "TIME\_STAMP" which contains a timestamp data but in keyword format. I want to query data based on this value, is there any way to convert the keyword to date? I'm new to ES stuff, so please don't get mad with me.
Mapping info about TIME\_STAMP field:
```auto
...
"TIME_STAMP": {
"type": "keyword",
"eager_global_ordinals": true,
"normalizer": "...",
"copy_to": [
"..."
]
},
...
```
Data sample of TIME\_STAMP field
```auto
...
"TIME_STAMP": "2023-12-25 15:39:21.094",
...
```
Without create another field to store TIME\_STAMP in date (or timestamp) format, is it possible to convert the datatype within the query?
As for why not stores in timestamp format in the first place (as I saw this mentioned in many posts), I don't know, when I joined the team, the field is there, and it seems I cannot change it myself.
---
### Author:  [@czek0](https://discuss.elastic.co/u/czek0)
#### Post date: [August 19, 2024, 11:43am UTC](https://discuss.elastic.co/t/convert-keyword-field-to-date-for-range-query/365121/2 "2024-08-19T11:43:27Z")
Hi you can runtime mappings and painless scripting to convert the keyword to be in date form during query:
```auto
GET /my_index/_search
{
"runtime_mappings": {
"parsed_timestamp": {
"type": "date",
"script": {
"source": """
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS");
emit(ZonedDateTime.parse(doc['TIME_STAMP'].value, formatter).toInstant().toEpochMilli());
"""
}
}
},
"query": {
"match_all": {}
},
"fields": ["parsed_timestamp"]
}
```
If you want to be able to search without creating a runtime mapping each time, you can create a new sup component called timestamp.data. Note you will have to reindex your data if you do the second method.
```auto
PUT /my_index_reindexed/_mapping
{
"properties": {
"TIME_STAMP": {
"type": "keyword", // existing field
"fields": {
"date": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss.SSS"
}
}
}
}
}
POST /_reindex
{
"source": {
"index": "my_index"
},
"dest": {
"index": "my_index_reindexed"
},
}
```
---
### Author:  [@NEWBIENEET](https://discuss.elastic.co/u/NEWBIENEET)
#### Post date: [August 20, 2024, 6:46am UTC](https://discuss.elastic.co/t/convert-keyword-field-to-date-for-range-query/365121/3 "2024-08-20T06:46:22Z")
I tried your first method, it first found a formatting error but after looking at the error cause, I tried adding `formatter.withZone(ZoneId.of('GMT+7'))` in formatter param and it works like a charm!
I'll look into the second method for a long-term solution.
Thank you!
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [September 17, 2024, 6:47am UTC](https://discuss.elastic.co/t/convert-keyword-field-to-date-for-range-query/365121/4 "2024-09-17T06:47:06Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.