mysql将时间戳转换成日期_在MySQL查询中将时间戳转换为日期

在MYSQL中将时间戳转换为日期

使表具有整数时间戳记：

mysql> create table foo(id INT, mytimestamp INT(11));

Query OK, 0 rows affected (0.02 sec)

插入一些值

mysql> insert into foo values(1, 1381262848);

Query OK, 1 row affected (0.01 sec)

看一看

mysql> select * from foo;

+------+-------------+

| id   | mytimestamp |

+------+-------------+

|    1 |  1381262848 |

+------+-------------+

1 row in set (0.00 sec)

将数字转换为时间戳：

mysql> select id, from_unixtime(mytimestamp) from foo;

+------+----------------------------+

| id   | from_unixtime(mytimestamp) |

+------+----------------------------+

|    1 | 2013-10-08 16:07:28        |

+------+----------------------------+

1 row in set (0.00 sec)

将其转换为可读格式：

mysql> select id, from_unixtime(mytimestamp, '%Y %D %M %H:%i:%s') from foo;

+------+-------------------------------------------------+

| id   | from_unixtime(mytimestamp, '%Y %D %M %H:%i:%s') |

+------+-------------------------------------------------+

|    1 | 2013 8th October 04:07:28                       |

+------+-------------------------------------------------+

1 row in set (0.00 sec)