我正在尝试在MySQL客户端版本:5.1.41中使用CASE语句格式化我的日期。 reviewed_on int(11( 默认 0 旨在存储纪元/UNIX 时间戳。结果不是得到 0 或 dd/mm/yyyy,而是在 true 结果前面加上 3,即如果 true 结果为 0,则返回 30;如果是 10,则返回 3130...
我检查了各种链接以及一个SQL案例,当FROM(UNIX_TIME(
@Martin Orem的结论是升级sql版本。这是唯一的解决方案,还是我们仍然可以解决此问题并获得所需的输出?任何帮助将不胜感激。
mysql>SELECT reviewed_on AS reviewed_on FROM table_name
+----------------------+
| reviewed_on |
+----------------------+
| 0 |
+----------------------+
mysql>SELECT FROM_UNIXTIME(reviewed_on) AS reviewed_on FROM table_name
+----------------------+
| reviewed_on |
+----------------------+
| 1970-01-01 01:00:00 |
+----------------------+
mysql>SELECT CASE reviewed_on WHEN 0 THEN 0 ELSE 1 END AS reviewed_on
FROM table_name
+----------------------+
| reviewed_on |
+----------------------+
| 0 |
+----------------------+
mysql>SELECT CASE reviewed_on WHEN 0 THEN 0 ELSE FROM_UNIXTIME(reviewed_on, '%d/%m/%Y') END AS reviewed_on
FROM table_name
+----------------------+
| reviewed_on |
+----------------------+
| 30 |
+----------------------+
mysql>SELECT CASE reviewed_on WHEN 0 THEN 10 ELSE DATE_FORMAT(FROM_UNIXTIME(reviewed_on), '%d/%m/%Y') END AS reviewed_on
FROM table_name
+----------------------+
| reviewed_on |
+----------------------+
| 3130 |
+----------------------+
因为没有人回答这个问题。我做了一些变通方法,并将列reviewed_on
更改为type date DEFAULT NULL
。它有效。
mysql>SELECT IFNULL(DATE_FORMAT(reviewed_on, '%d/%m/%Y'), '0') AS reviewed_on FROM table_name
+----------------------+
| reviewed_on |
+----------------------+
| 27/03/2018 |
+----------------------+
| 0 |
+----------------------+