在 MySQL 查询中将时间戳转换为日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9251561/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert timestamp to date in MySQL query
提问by remyremy
I want to convert a timestampin MySQL to a date.
我想将timestampMySQL 中的 a转换为日期。
I would like to format the user.registration field into the text file as a yyyy-mm-dd.
我想将 user.registration 字段格式化为yyyy-mm-dd.
Here is my SQL:
这是我的 SQL：
$sql = requestSQL("SELECT user.email,
info.name,
FROM_UNIXTIME(user.registration),
info.news
FROM user, info
WHERE user.id = info.id ", "export members");
I also tried the date conversion with:
我还尝试了日期转换：
DATE_FORMAT(user.registration, '%d/%m/%Y')
DATE(user.registration)
I echo the result before to write the text file and I get :
我在写文本文件之前回显结果，我得到：
email1;name1;DATE_FORMAT(user.registration, '%d/%m/%Y');news1
email2;name2;news2
email1;name1;DATE_FORMAT(user.registration, '%d/%m/%Y');news1
电子邮件 2;姓名 2;新闻 2
How can I convert that field to date?
如何将该字段转换为日期？
回答by Yatin
DATE_FORMAT(FROM_UNIXTIME(`user.registration`), '%e %b %Y') AS 'date_formatted'
回答by Eric Leschinski
Convert timestamp to date in MYSQL
在 MYSQL 中将时间戳转换为日期
Make the table with an integer timestamp:
使用整数时间戳制作表：
mysql> create table foo(id INT, mytimestamp INT(11));
Query OK, 0 rows affected (0.02 sec)
Insert some values
插入一些值
mysql> insert into foo values(1, 1381262848);
Query OK, 1 row affected (0.01 sec)
Take a look
看一看
mysql> select * from foo;
+------+-------------+
| id | mytimestamp |
+------+-------------+
| 1 | 1381262848 |
+------+-------------+
1 row in set (0.00 sec)
Convert the number to a timestamp:
将数字转换为时间戳：
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)
Convert it into a readable format:
将其转换为可读格式：
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)
回答by juergen d
To just get a date you can castit
刚刚得到一个日期，你可以cast它
cast(user.registration as date)
and to get a specific format use date_format
并获得特定的格式使用 date_format
date_format(registration, '%Y-%m-%d')
SQLFiddle demo
SQLFiddle 演示
回答by cs0lar
If the registrationfield is indeed of type TIMESTAMPyou should be able to just do:
如果该registration字段确实是类型，TIMESTAMP您应该能够执行以下操作：
$sql = "SELECT user.email,
info.name,
DATE(user.registration),
info.news
FROM user,
info
WHERE user.id = info.id ";
and the registration should be showing as yyyy-mm-dd
并且注册应该显示为 yyyy-mm-dd
回答by Morey
Just use mysql's DATE function:
只需使用 mysql 的 DATE 函数：
mysql> select DATE(mytimestamp) from foo;
回答by Jaydeep Mor
You should convert timestampto date.
您应该转换timestamp为date.
select FROM_UNIXTIME(user.registration, '%Y-%m-%d %H:%i:%s') AS 'date_formatted'
回答by Emanuel Graf
回答by d.raev
FROM_UNIXTIME(unix_timestamp, [format])is all you need
FROM_UNIXTIME(unix_timestamp, [format])是你所需要的全部
FROM_UNIXTIME(user.registration, '%Y-%m-%d') AS 'date_formatted'
FROM_UNIXTIMEgets a number value and transforms it to a DATEobject,
or if given a format string, it returns it as a string.
FROM_UNIXTIME获取数字值并将其转换为DATE对象，
或者如果给定格式字符串，则将其作为字符串返回。
The older solution was to get the initial date object and format it with a second function DATE_FORMAT... but this is no longer necessary
较旧的解决方案是获取初始日期对象并使用第二个函数对其进行格式化DATE_FORMAT......但这不再是必要的
回答by Fraser
If you are getting the query in your output you need to show us the code that actually echos the result. Can you post the code that calls requeteSQL?
如果您在输出中获取查询，则需要向我们展示实际响应结果的代码。你能贴出调用requeteSQL的代码吗？
For example, if you have used single quotes in php, it will print the variable name, not the value
例如，如果您在 php 中使用了单引号，它将打印变量名称，而不是值
echo 'foo is $foo'; // foo is $foo
This sounds exactly like your problem and I am positive this is the cause.
这听起来与您的问题完全一样，我很确定这就是原因。
Also, try removing the @ symbol to see if that helps by giving you more infromation.
此外，尝试删除@ 符号，看看是否可以为您提供更多信息。
so that
以便
$SQL_result = @mysql_query($SQL_requete); // run the query
becomes
变成
$SQL_result = mysql_query($SQL_requete); // run the query
This will stop any error suppression if the query is throwing an error.
如果查询抛出错误，这将停止任何错误抑制。
回答by Vitaliy A
Try:
尝试：
SELECT strftime("%Y-%d-%m", col_name, 'unixepoch') AS col_name
It will format timestamp in milliseconds to yyyy-mm-dd string.
它将以毫秒为单位将时间戳格式化为 yyyy-mm-dd 字符串。
