2

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.

Kingx
  • 41
  • 2
  • 8
  • 1
    Have you read https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html ? – Bill Karwin Dec 09 '17 at 20:48
  • Possible duplicate of [How to convert timestamp to datetime in MySQL?](https://stackoverflow.com/questions/5362874/how-to-convert-timestamp-to-datetime-in-mysql) – Paul T. Rawkeen Dec 09 '17 at 21:07

1 Answers1

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`

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

Paul T. Rawkeen
  • 3,994
  • 3
  • 35
  • 51
  • Thank you so much for the answer, it worked. FROM_UNIXTIME and DATE_FORMAT really did the magic. – Kingx Dec 10 '17 at 00:20
  • 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 – Kingx Dec 11 '17 at 22:08
  • @Kingx try reading **[`DATE_FORMAT`](https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format)**. – Paul T. Rawkeen Dec 12 '17 at 06:23
  • 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". – Kingx Dec 12 '17 at 17:41