FROM(UNIX_TIME) 时的 SQL 案例 [英] SQL case when FROM(UNIX_TIME)

问题描述

我正在编写简单的 sql select，但问题出现了.当我运行以下选择

I am writing simple sql select but the problem raised up. When I run following select

SELECT
      CASE
          WHEN t.escalation_time =0 THEN 987
          ELSE 789
      END
FROM ticket t WHERE t.id =1

一切正常.但是当运行这几行时，输出不匹配(它给出了随机"数字).

everything is fine. But when run these few lines, output is mismatched (it gives "random" numbers).

  SELECT
      CASE
          WHEN t.escalation_time = 0 THEN 0
          ELSE FROM_UNIXTIME( t.escalation_time )
      END
FROM ticket t WHERE t.id =1

我想知道名为 FROM_UNIXTIME 的函数可能有问题.但另一方面，作为一个独立的功能，它可以完美运行.等待的结果是显示 0(如果是 0)而不是 1970-01-01 01:00:00.提前致谢.

I am wondering it could be problem with the function called FROM_UNIXTIME. But on the other hand, as a standalone function it works perfectly. The awaiting result is to show 0 (in case 0) instead of 1970-01-01 01:00:00. Thanks in advance.

推荐答案

FROM_UNIXTIME() 返回时间戳值.来自手册:.

FROM_UNIXTIME() returns a timestamp value. From the manual:.

TIMESTAMP 的范围是 '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC.

TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

现在可能令人困惑的是，时区与时间戳一起被考虑.

What can be confusing now, is the fact that timezones are considered with timestamp.

如果您在 GMT+1h 时区，这个

If you're in the timezone GMT+1h, this

select unix_timestamp('1970-01-01 00:00:00');

返回

+---------------------------------------+
| unix_timestamp('1970-01-01 00:00:00') |
+---------------------------------------+
|                                     0 |
+---------------------------------------+

既然考虑了时区，这个

select unix_timestamp('1970-01-01 01:00:00');

仍然返回

+---------------------------------------+
| unix_timestamp('1970-01-01 01:00:00') |
+---------------------------------------+
|                                     0 |
+---------------------------------------+

但是这个...

select unix_timestamp('1970-01-01 01:00:01');
+---------------------------------------+
| unix_timestamp('1970-01-01 01:00:01') |
+---------------------------------------+
|                                     1 |
+---------------------------------------+

还有这个

select unix_timestamp('1970-01-01 02:00:00');
+---------------------------------------+
| unix_timestamp('1970-01-01 02:00:00') |
+---------------------------------------+
|                                  3600 |
+---------------------------------------+

现在返回正确"的值.

更新:

简单快捷的解决方案:

SELECT
      CASE
          WHEN t.escalation_time = 0 THEN 0
          WHEN FROM_UNIXTIME( t.escalation_time ) = '1970-01-01 00:00:00' THEN 0
          ELSE FROM_UNIXTIME( t.escalation_time )
      END
FROM ticket t WHERE t.id =1

这篇关于FROM(UNIX_TIME) 时的 SQL 案例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！