mysql时间转换整数,MySQL将整数转换为日期

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.

Here's how the table looks like:

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

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

SOLUTION : I used "

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

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`