Search code examples
mysqlsqltimestampunix-timestamp

Format MySQL timestamp to mm/dd/yyyy

I am given a MySQL 12 digit TimeStamp (253402214400) to format it in mm/dd/yyyy without using any language. Database field is type of longtext and here is what I am doing.

SELECT time_stamp; returns 253402214400

SELECT DATE_FORMAT(time_stamp,'%m/%d/%Y'); returns NULL

SELECT FROM_UNIXTIME(time_stamp); also returns NULL

SELECT UNIX_TIMESTAMP(time_stamp); returns 0

I don't know what type of Timestamp is this, but please help me to format it in mm/dd/yyyy.

Thanks

Solution

  • I am sorry everybody, I think the time was givin in milliseconds, so by some research I could reach here https://stackoverflow.com/a/9483289/750302 and resolved it with DATE_FORMAT(FROM_UNIXTIME(time_stamp/1000), '%m/%d/%Y'); SQLFIDDLE! ...

    Thank You Everyone for your Support!