MySQL将整数转换为日期 [英] Mysql convert integer to date

问题描述

我有一个数据库，该日期和时间以整数形式存储在数据库列idate上，我想使用命令显示实际日期，但需要帮助.

I have a database that stores dates and time as integers on a database column idate, I would like to show the actual dates using a command but I need assistance.

这是表格的样子:

id  user_id amount  idate   status  
1   23  1000    60  1493723513  NULL
2   23  1000    60  1493724293  NULL
3   19  9000    120 1491239643  finished
4   19  9000    120 1493831643  NULL
5   27  1531.8  360 1493920525  ongoing
6   20  5618    30  1493832270  finished
7   20  4215    30  1494231929  finished
8   35  1000    60  1494325129  NULL
9   35  2000    90  1494325335  NULL
10  11  5000    90  1495364902  ongoing

使用第一列id列，我想以 2017-12-08 17:11:43 格式查看1493723513的日期.

Using the first id column I want to view the date of 1493723513 in format 2017-12-08 17:11:43.

解决方案:我使用了"

SELECT `id`, `user_id`, `amount`, `status`, DATE_FORMAT(FROM_UNIXTIME(`idate`), '%Y-%m-%d %H:%i:%s') AS `date` FROM `tablename`

问题:如何将日期和时间编辑为所需的其他日期，日期以id形式存储为"1493723513"，我如何从该整数中编辑日期和时间戳.

QUESTION: How can i edit the date and time to a different date as desired, date is stored as e.g "1493723513" on idate, how can i edit both date and timestap from this integer.

推荐答案

此查询将以所需格式输出记录时间戳中的格式化日期.

This query will output formatted date from record timestamp in required format.

SELECT `id`, `user_id`, `amount`, `status`, DATE_FORMAT(FROM_UNIXTIME(`idate`), '%Y-%m-%d %H:%i:%s') AS `date` FROM `tablename`

• FROM_UNIXTIME 和 DATE_FORMAT 发挥了所有作用.
• FROM_UNIXTIME and DATE_FORMAT do all the magic.

@BillKarwin 中提到 此MySQL参考页 可能有用.

As @BillKarwin mentioned this MySQL ref page can be useful.

这篇关于MySQL将整数转换为日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！