Mysql convert integer to date

Question

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.

Have you read dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html ? — Bill Karwin
– Bill Karwin, Commented Dec 9, 2017 at 20:48
Possible duplicate of How to convert timestamp to datetime in MySQL? — Paul T. Rawkeen
– Paul T. Rawkeen, Commented Dec 9, 2017 at 21:07

Paul T. Rawkeen · Accepted Answer · 2017-12-09 21:12:59Z

4

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 and DATE_FORMAT do all the magic.

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

edited Dec 9, 2017 at 21:12

answered Dec 9, 2017 at 21:00

Paul T. Rawkeen

4,1133 gold badges38 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kingx Over a year ago

Thank you so much for the answer, it worked. FROM_UNIXTIME and DATE_FORMAT really did the magic.

Kingx Over a year ago

Hello @Paul T. Rawkeen How can i edit the output easily to any desired date or time, i am trying to edit from the output but it won't let me, on column date is stored as e.g "1493723513" how can i edit this to a desired date, kindly assist me

Paul T. Rawkeen Over a year ago

@Kingx try reading DATE_FORMAT.

Kingx Over a year ago

Hi Paul, Thank you for your reply, i have read the article but i am still having some problems, kindly help me solve this with your own example command to edit the dates on the column "idate".

Collectives™ on Stack Overflow

Mysql convert integer to date

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related