ES SQL result doesn't use correct date mapping

Question

I'm experimenting with the SQL options from Elasticsearch and I noticed that a timestamp field that I mapped as "strict_date_optional_time_nanos||epoch_millis" doesn't show up as it is indexed. This is what the timestamp column looks like when I do a SELECT * FROM index:

|       timeStamp        |
+------------------------+
|1970-01-20T04:38:39.243Z|

The actual value indexed is: 1675772407310 (9th of Feb 13:59:24). I cannot seem to find information as to why it's this way.

Answer 1

I believe that Elasticsearch already internally performs the conversion to the datetime type. In this case, you can do a cast to get the value in epoch format.

GET _sql?format=txt
{
  "query": """ SELECT cast(timestamp as bigint) FROM "test"
  """
}