logo标签列表

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

mysqlsqldate-formatting
292

我想将MySQL中的timestamp转换为日期。

我想将用户注册字段user.registration格式化为文本文件中的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(user.registration, '%d/%m/%Y');news1

email2;name2;news2

如何将该字段转换为日期？

它确实保存文件了吗？你用来运行 SQL 查询的代码是什么？ - seanbreeden
1
我编辑了我的问题，requeteSQL正在运行查询。 - remyremy
13个回答
509
DATE_FORMAT(FROM_UNIXTIME(`user.registration`), '%e %b %Y') AS 'date_formatted'
3
从MySQL 5.6.23版本开始，我发现应该直接输入传递给FROM_UNIXTIME()函数的值，即：DATE_FORMAT(FROM_UNIXTIME(user.registration), '%e %b %Y') AS 'date_formatted' - JESii
2
SELECT * FROM users WHERE DATE_FORMAT(FROM_UNIXTIME(users.user_created_at),'%Y-%b-%e') = '2015-03-06' 这个格式正确吗？还是需要修改？ - Dheeraj Thedijje
3
这段话的意思是：@DheerajThedijje看到的日期格式是2018年11月6日。你可能正在寻找'%Y-%m-%d'这个格式，在PHP中你会用它来格式化日期，例如(date('Y-m-d',$row->user_created_at))。这两种格式（SQL和PHP）都显示为2018年11月6日。 - Chris S.
2
如果您想以“hh:mm:ss”的方式添加时间，则将其添加到格式字符串中'%H:%i:%s'。 - luis.ap.uyen
这个是存储伦敦、纽约、触发器运行的地方还是客户端运行的地方的时间戳？ - William Entriken
99

为了仅获取日期，您可以使用cast函数进行转换

cast(user.registration as date)

如果要获得指定的日期和时间格式，请使用date_format函数。

date_format(registration, '%Y-%m-%d')

SQLFiddle演示

1
它会给出相同的结果，而不是字段的值，显示cast（user.registration as date）。 - remyremy
7
这个语句有效 - 将user.registration转换为日期格式：cast(from_unixtime(user.registration) AS date)。 - Eric Ness
我有特定的日期格式，它对我非常有效，谢谢。 - saber tabatabaee yazdi
2
使用"as DATETIME"来获取小时、分钟和秒。 - Enrique
85

将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)
请将“%h”更正为“%H”。 - Tomot
24
如果registration字段确实是TIMESTAMP类型，你应该只需要执行以下操作：
$sql = "SELECT user.email, 
           info.name, 
           DATE(user.registration), 
           info.news
      FROM user, 
           info 
     WHERE user.id = info.id ";

同时，注册应该显示为 yyyy-mm-dd。

我尝试了，但是我得到了相同的结果：DATE（user.registration）被显示出来，而不是字段的值。 - remyremy
正如有人建议的那样，你能否直接使用MySQL客户端成功运行查询？我没有听说过requeteSQL函数，它确切地是做什么的？ - cs0lar
很遗憾，我无法直接使用mysql客户端，因为我没有自己的服务器... 我提供了requeteSQL，基本上它只是运行查询并测试是否出错以发送电子邮件... - remyremy
2
实际上我意识到我的字段是BIGINT类型而不是TIMESTAMP。只有内容是时间戳（1328649722），所以它才不能工作。现在使用FROM_UNIXTIME一切都很好！ - remyremy
15

FROM_UNIXTIME(unix_timestamp, [format])就是你需要的

FROM_UNIXTIME(user.registration, '%Y-%m-%d') AS 'date_formatted'

FROM_UNIXTIME将数字值转换为DATE对象，如果给定格式字符串，则返回字符串。

以前的解决方案是获取初始日期对象，并使用第二个函数DATE_FORMAT进行格式化... 但现在不再需要这样做。

13

只需使用 MySQL 的 DATE 函数：

mysql> select DATE(mytimestamp) from foo;
7
在我的MySQL 5.7.14中，SELECT DATE(UNIX_TIMESTAMP()) 返回了 NULL。 - Xenos
尝试使用DATE(CURRENT_TIMESTAMP())。 - Morey
8
你应该将 timestamp 转换成 date。
select FROM_UNIXTIME(user.registration, '%Y-%m-%d %H:%i:%s') AS 'date_formatted'

FROM_UNIXTIME

3
我使用了“date”函数，正如这里所描述的：
(SELECT count(*) as the-counts,(date(timestamp)) as the-timestamps FROM `user_data` WHERE 1 group BY the-timestamps)
3
如果您在输出中收到查询，则需要向我们展示实际回显结果的代码。您可以发布调用requeteSQL的代码吗？
例如，如果您在php中使用单引号，它将打印变量名称而不是值。
echo 'foo is $foo'; // foo is $foo

这听起来就像你的问题，我敢肯定这就是原因。
此外，尝试删除@符号，看看是否可以提供更多信息。
所以，

$SQL_result = @mysql_query($SQL_requete); // run the query

变成

  $SQL_result = mysql_query($SQL_requete); // run the query

这将停止对查询的错误抑制，如果查询出现错误。
3

如果您想更改列的数据类型，可以先将其从 TIMESTAMP 转换为 INT:

ALTER TABLE table_name MODIFY column_name INT;

然后将 INT 转换为 DATE：

ALTER TABLE table_name MODIFY column_name DATE;

但是，如果您不想更改列，只是想选择数据，那么您可以使用 date() 函数：

SELECT date(your_timestamp_column) FROM your_table;
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接