我想将MySQL中的时间戳转换为日期。
我想格式化用户。将注册字段作为yyyy-mm-dd输入到文本文件中。
这是我的SQL:
$sql = requestSQL("SELECT user.email,
info.name,
FROM_UNIXTIME(user.registration),
info.news
FROM user, info
WHERE user.id = info.id ", "export members");
我还尝试了日期转换:
DATE_FORMAT(user.registration, '%d/%m/%Y')
DATE(user.registration)
我在写入文本文件之前回显结果，我得到:
email1; name1 DATE_FORMAT(用户)。registration,‘d - m % % Y); news1
email2; name2 news2
如何将该字段转换为日期?
在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)
