一、先解决你SQL里的疑问 #

你写的SQL其实已经正确实现需求了！咱们拆解下关键部分：

• FROM_UNIXTIME(create_time)：这个函数专门用来把秒级整数时间戳（比如你提到的1600506000）转换成对应的日期时间格式（比如2020-09-19 11:00:00）。MySQL默认处理的就是秒级时间戳，如果你的时间戳是毫秒级的，只需要先除以1000再传入这个函数就行。
• HOUR(...)：从转换后的日期时间里提取小时部分，最后按小时分组求和，完全匹配你的业务需求。

要是想验证的话，可以单独执行SELECT FROM_UNIXTIME(1600506000);，就能看到对应的具体时间，再套上HOUR()就能直接拿到提取的小时数了。

二、Elasticsearch中的实现方法 #

在Elasticsearch里，分两种场景处理，优先推荐索引阶段的字段映射优化，查询时处理更适合临时需求。

1. 索引阶段：将整数时间戳映射为日期类型（推荐） #

如果你的数据还没索引，或者可以重新调整字段映射，直接把create_time定义为date类型，并指定支持秒级时间戳的格式，这样Elasticsearch会自动完成转换，后续查询效率更高：

PUT /your_index
{
  "mappings": {
    "properties": {
      "create_time": {
        "type": "date",
        "format": "epoch_second" // 声明该字段是秒级整数时间戳
      },
      "a": {
        "type": "integer"
      }
    }
  }
}

之后查询时，直接用date_histogram聚合按小时分组，或者用脚本提取小时：

按小时聚合求和的DSL示例：

GET /your_index/_search
{
  "size": 0,
  "aggs": {
    "hourly_count": {
      "date_histogram": {
        "field": "create_time",
        "interval": "hour", // 按小时分组
        "format": "HH" // 返回00-23格式的小时数
      },
      "aggs": {
        "total_count": {
          "sum": {
            "field": "a"
          }
        }
      }
    }
  }
}

2. 查询阶段：临时转换整数时间戳并提取小时 #

如果无法修改字段映射，只能在查询时用脚本临时处理：

用script_fields提取小时（仅查看单条数据的小时结果）：

GET /your_index/_search
{
  "script_fields": {
    "hour": {
      "script": {
        "source": "ZonedDateTime.ofInstant(Instant.ofEpochSecond(doc['create_time'].value), ZoneId.of('UTC')).getHour()"
      }
    }
  }
}

按小时聚合求和的DSL示例：

GET /your_index/_search
{
  "size": 0,
  "aggs": {
    "group_by_hour": {
      "terms": {
        "script": {
          "source": "ZonedDateTime.ofInstant(Instant.ofEpochSecond(doc['create_time'].value), ZoneId.of('UTC')).getHour()"
        }
      },
      "aggs": {
        "total_count": {
          "sum": {
            "field": "a"
          }
        }
      }
    }
  }
}

注意：如果你的时间戳是毫秒级的，把Instant.ofEpochSecond改成Instant.ofEpochMilli即可。

内容的提问来源于stack exchange，提问作者wyx