# Date Math in Elasticsearch SQL
**URL:** https://discuss.elastic.co/t/date-math-in-elasticsearch-sql/191449
**Category:** Elasticsearch
**Tags:** elastic-stack-sql
**Created:** [July 19, 2019, 7:54pm UTC](https://discuss.elastic.co/t/date-math-in-elasticsearch-sql/191449 "2019-07-19T19:54:05Z")
**Posts on this page:** 3
**Page:** 1
### Author:  [@bschneiders](https://discuss.elastic.co/u/bschneiders)
#### Post date: [July 19, 2019, 7:54pm UTC](https://discuss.elastic.co/t/date-math-in-elasticsearch-sql/191449/1 "2019-07-19T19:54:05Z")
I'm on ES 6.7.0 with X-Pack. Based on the documentation I cannot figure out how to subtract two dates to get the time delta. My times are stored in epoch\_millis and mapped as shown below.
I've tried a variety of casting and using INTERVAL. I haven't found a single successful strategy.
Mapping:
```auto
"session_stime" : {
"type" : "date",
"format" : "epoch_millis"
},
```
Without casting or math:
```auto
GET /_xpack/sql?format=txt
{
"query": """
SELECT
max(session_stime),
min(session_stime)
FROM "dataset_*"
"""
}
```
Response
```auto
max(session_stime)|min(session_stime)
------------------+------------------
1.551035491645E12 |1.541957117399E12
```
Without casting and with math:
```auto
GET /_xpack/sql?format=txt
{
"query": """
SELECT
max(session_stime) -
min(session_stime)
FROM "dataset_*"
"""
}
```
Response:
```auto
{
"error": {
"root_cause": [
{
"type": "verification_exception",
"reason": "Found 1 problem(s)\nline 2:7: first argument of [max(session_stime) -\n min(session_stime)] must be [numeric], found value [max(session_stime)] type [datetime]"
}
],
"type": "verification_exception",
"reason": "Found 1 problem(s)\nline 2:7: first argument of [max(session_stime) -\n min(session_stime)] must be [numeric], found value [max(session_stime)] type [datetime]"
},
"status": 400
}
```
With casting and math:
```auto
GET /_xpack/sql?format=txt
{
"query": """
SELECT
CAST(max(session_stime) AS DOUBLE) -
CAST(min(session_stime) AS DOUBLE)
FROM "dataset_*"
"""
}
```
Response:
```auto
{
"error": {
"root_cause": [
{
"type": "class_cast_exception",
"reason": "java.lang.Double cannot be cast to java.time.ZonedDateTime"
}
],
"type": "class_cast_exception",
"reason": "java.lang.Double cannot be cast to java.time.ZonedDateTime"
},
"status": 500
}
```
---
### Author:  [@bschneiders](https://discuss.elastic.co/u/bschneiders)
#### Post date: [July 19, 2019, 8:00pm UTC](https://discuss.elastic.co/t/date-math-in-elasticsearch-sql/191449/2 "2019-07-19T20:00:54Z")
For what it's worth, I can cast the field when not using a min() or max() function. And do math on it.
```auto
GET /_xpack/sql?format=txt
{
"query": """
SELECT
CAST(session_stime AS DOUBLE) * 2
FROM "dataset_*"
LIMIT 1
"""
}
```
response:
```auto
CAST(session_stime AS DOUBLE) * 2
---------------------------------
3.102036724698E12
```
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [August 16, 2019, 8:01pm UTC](https://discuss.elastic.co/t/date-math-in-elasticsearch-sql/191449/3 "2019-08-16T20:01:12Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.