MySQL JSON_OBJECT() datetime format

问题: I'm creating a JSON structure by using a JSON_OBJECT and JSON_ARRAY functions in my select query. The problem I have is with the format in which datetime columns are return...

问题:

I'm creating a JSON structure by using a JSON_OBJECT and JSON_ARRAY functions in my select query. The problem I have is with the format in which datetime columns are returned in the formatted JSON result.

For example I have a table titles

id (INT), title (VARCHAR), created_at (DATETIME)

and a row would be like this

1,"Title 1","2019-02-03 12:13:14"

If I now do the following query

SELECT JSON_OBJECT('title',title,'created_at',created_at) AS title_json FROM titles WHERE id = 1;

I will get the resulting title_json column as:

{
    "title": "Title 1",
    "created_at": "2019-02-03 12:13:14.000000"
}

I would like to have the datetime values returned in the standard YYYY-MM-DD HH:ii:ss format, without the trailing zeroes.

Is this possible?

I have looked through the JSON_OBJECT documentation but couldn't find any clues to this mystery. I'm thinking the format used might be defined somewhere in the server/database settings. The ideal solution for my case would be to optionally set the desired format in individual queries themselves.

I'm using: Server: MySQL Community Server (GPL) Version: 5.7.24


回答1:

You can use DATE_FORMAT

SELECT JSON_OBJECT('title',title,'created_at',DATE_FORMAT(created_at, "%Y-%c-%d %H:%i:%s")) AS title_json FROM titles WHERE id = 1;
  • 发表于 2019-03-19 02:52
  • 阅读 ( 189 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除