CODEWITHSUNDEEP
mysqlsqldateunix-timestampdata-conversion
Lukáš Gibo Vaic
Lukáš Gibo Vaic

Reputation: 4420

Mysql cell conversion

I am facing a small problem in MySQL table. In one cell, date is written in unix_timestamp format, but I am unable to display it on web as human readable format. Is there any way to make new cell which would take the date and display as human readable format ? I have provided below a snapshot of my requirement to make it more clear:

    date   |        human readable format   |
-----------|--------------------------------|
1206624265 |would display date as YYYY MM/DD|

I am not concerned about format of the info in 'date' column in the above snapshot.

Upvotes: 0

Views: 42

Answers (1)

Zelldon
Zelldon

Reputation: 5516

You can use the built-in date functions from Mysql. In your case you should use the 'FROM_UNIXTIME' function which converts a unix timestamp to a formated date string.

FROM_UNIXTIME MySQL Documentation:

FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)

Returns a representation of the unix_timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context.

See the Examples from the MYSQL Documentation

mysql> SELECT FROM_UNIXTIME(1196440219);
        -> '2007-11-30 10:30:19'
mysql> SELECT FROM_UNIXTIME(1196440219) + 0;
        -> 20071130103019.000000
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
    ->                      '%Y %D %M %h:%i:%s %x');
        -> '2007 30th November 10:30:59 2007'

In your case I would prefer to create a view.

Like this:

CREATE VIEW viewOfYourTable AS
SELECT t.*, FROM_UNIXTIME(t.date) as human_date
FROM yourTable as t;

Because so you have no redudant data and you can use the view similar as a table.

Upvotes: 3

Related Questions