主页 / dba / 问题 / 16461
Accepted
Josh Smeaton
Asked: 2012-04-13 20:17:12 +0800 CST

将 Unix 时间戳转换为视图中的 DATETIME

  • 772

我有一个存储 unix 时间戳的表。要将其作为日期查询，我正在尝试将此时间戳转换为视图中的日期时间类型。不幸的是，我似乎只能取出 DATE 部分。

此链接描述了如何进行转换，但需要更改nls_date_format以包含时间部分。当前默认仅显示日期部分。我需要日期和时间。

不幸的是，该解决方案仅适用于会话级别。作为一名开发人员，我宁愿不必跑到我们的托管服务提供商那里要求他们更改数据库级别的值——尤其是因为它可能会影响其他应用程序。

有没有一种方法可以在不修改系统的情况下将 sql 中的时间戳转换为日期时间？

oracle oracle-11g

4 个回答

  1. Best Answer

    表达式的结果

    to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval(1244108886,'SECOND')

    是 oracle 数据类型 DATE 的值。此类型包含日期和时间。如果要向用户显示此值，则必须将其转换为字符串。这种转换可以由应用程序完成，或者您可以让 oracle 将其转换为字符串类型，就像在本示例中所做的那样。这种到 oracle 字符串类型 VARCHAR2 的自动转换由 nls_date_format 和一些服务器设置控制。如果您希望视图返回 VARCHAR2 值，其计算日期格式为 'YYYY-MM-DD HH24:MI:SS' 而不是 DATE 值，您可以使用转换函数执行此操作：表达式

    to_char(to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval(1244108886,'SECOND'),'YYYY-MM-DD HH24:MI:SS')

    但在这种情况下，视图的返回值是 VARCHAR2 而不是 DATE 值。

    • 14

  2. 最佳实践是避免在 SQL 查询中调用非 SQL 函数，因为该函数可能不是普遍可用的——在我的系统中没有 numtodsinterval 函数——也是因为将上下文从 SQL 切换到 PL/SQL 的额外开销，然后如果在 where 子句中调用该函数，则返回或测试每一行返回 SQL。

    这是一个仅限 SQL 的解决方案。

    TO_DATE('1970-01-01','YYYY-MM-DD') + unix_timestamp / 86400

    请注意，结果是一个 Oracle DATE 值，其中还包含 TIME。如果在您运行查询时它似乎没有 TIME，那么问题出在您的 NLS_DATE_FORMAT 或您的 UI 将日期转换为字符串的实现中。请务必使用格式字符串中的时间部分自己执行 TO_CHAR 以查看整个值。例如 TO_CHAR(a_date, 'yyyy-mm-dd hh24:mi:ss')

    • 6

  3. 我使用了另一个数字来计算这个值。

    select (TO_DATE('1970-01-01','YYYY-MM-DD') + unix_timestamp / 86400000) from dbname;

    这个脚本对我来说没问题。

    • 1

  4. 如果必须将 unix_timestamp 与 WHERE 子句中的某个日期范围进行比较，最好将日期转换为 unix_timestamps，而不是将所有 unix_timestamp 值转换为日期。

    例如，下面可以找到指定开始日期和结束日期之间带有 UNIX_TIMESTAMP 的行。

    WHERE unix_timestamp BETWEEN
       (:start_date - TO_DATE('1970-01-01','YYYY-MM-DD')) * 86400
       AND 
       (:end_date - TO_DATE('1970-01-01','YYYY-MM-DD')) * 86400

    这也将允许使用 UNIX_TIMESTAMP 上的索引（如果有的话）。

    • 0

相关问题