将十进制数值转换为日期 - Informix DB

Question

我想在select语句中将十进制值转换为日期。

SELECT user_id, extend(dbinfo("UTC_TO_DATETIME",min(creationdate)), year to fraction) AS earned_date FROM message

但是我遇到了以下错误：

 Value exceeds limit of INTEGER precision

表中的字段定义如下：

  creationDate      decimal(14,0) NOT NULL

看起来我没有使用正确的方法进行转换，有什么想法吗？

- Ahmad Alkhawaja

Answer 1

我认为在这种情况下您不需要使用EXTEND - EXTEND用于更改DATETIME和INTERVAL值的比例。 UTC_TO_DATETIME返回一个DATETIME值。

您尝试过吗：

SELECT user_id, dbinfo("UTC_TO_DATETIME",min(creationdate)) AS earned_date
  FROM message

这会产生什么结果？

更新

啊哈，一个例子就能让一切变得不同！

您传递的值不是 UTC_TO_DATETIME 期望的 UTC 日期时间。该函数需要表示秒数的整数，而不是毫秒数。就是这么简单。

DBINFO('UTC_TO_DATETIME', 1329994172574) 超出了整数精度。

另一方面，DBINFO('UTC_TO_DATETIME', 1329994172574/1000) 产生了：

2012-02-23 21:49:32，我想这就是您所期望的？

如果您查看我之前提供的链接，它解释了如何处理小数秒。（剧透：它们被忽略了。）

如果小数秒非常重要，那么您可能需要将此函数的结果 EXTEND 到小数秒，然后将 MOD(creationdate,1000)/1000 添加到其中。

例如：

SELECT user_id, 
      (dbinfo("UTC_TO_DATETIME", MIN(creationdate)/1000 )::DATETIME YEAR TO FRACTION(3)
        + (MOD(MIN(creationdate),1000)/1000) UNITS FRACTION) AS earned_date
  FROM message

但是个人而言，我倾向于将这个逻辑放入SPL中，这样您就可以像这样调用它：SELECT user_id, millis_to_time(MIN(creationdate))...并避免在各个地方编写此类复杂算法。

- RET

---

它给了我与我在问题中提到的相同的错误信息，正如我澄清的那样，该字段定义为Decimal(14,0)，1329994172574是一个值示例，当尝试执行SQL时会导致错误。 - Ahmad Alkhawaja

请查看我的更新答案。你提供的示例值是所需的；列定义为DECIMAL(14,0)并不能告诉我们你放入了什么内容。 - RET

Answer 2

试试这样做，

select
    DT,
    DT_Date = convert(datetime,convert(varchar(8),left(dt,8)))
from
    (  -- Test data
    select DT = 19001231.000000
    ) a

这里，DT = 19001231

前4位数字（1900）- 年份， 接下来的2位数字（12）- 月份， 最后的2位数字（31）- 日期。

只有按照上述格式才能进行转换。希望对您有所帮助，谢谢。

- SuganR

---

当我尝试运行它时，似乎无法针对Informix DB运行，我遇到了语法错误。 - Ahmad Alkhawaja

不，我认为一定是SQL Server - 当你的问题中有一个“sql”标签时，这总是一个风险... - RET

我认为“sql”标签代表我需要SQL的帮助，而不是特定于某个数据库。 - Ahmad Alkhawaja

是的，这是一个相当合理的假设。我有这样的印象，很多SQL Server专家并不知道还有其他数据库技术存在... - RET

Answer 3

我已经成功编写了一个存储过程来解决这个问题，似乎这是唯一的解决方案？小数点后面的数字（14）表示毫秒，所以这就是解决方案：

create procedure "informix".millis_to_time(milli_val decimal(14,0)) returning datetime year to fraction(3);

  define retval datetime year to fraction(3);
  define num_days int;
  define num_seconds int;
  define millis_in_day int;

  let millis_in_day = 86400000;
  let num_days = trunc(milli_val/millis_in_day,0);
  let num_seconds = (milli_val - (num_days * millis_in_day))/1000;

  let retval = extend(mdy(1,1,1970), year to fraction(3));
  let retval = retval + num_days units day;
  let retval = retval + num_seconds units second;

  return retval;
end procedure;

- Ahmad Alkhawaja