Oracle 时间戳 UTC 时间格式
CREATE TABLE DIALOGUE_TABLE(EXPIRE_TIME TIMESTAMP);
以下代码片段位于存储过程中：
PO_EXPIRETIME :- OUT PARAM of procedure a varchar2
SELECT TO_CHAR(SYS_EXTRACT_UTC(EXPIRE_TIME))
INTO PO_EXPIRETIME
FROM DIALOGUE_TABLE;
当我使用 EXEC 从服务器运行存储过程并打印 PO_EXPIRETIME 时间戳时，时间戳与 UTC 格式是正确的。
但是，当我从 OCCI 和客户端调用存储过程时，收到的时间戳不同，但这是表中的实际时间戳，而不是 UTC 格式。
也许我缺少一些但我不知道的东西？ 我需要在客户端做些什么吗？
CREATE TABLE DIALOGUE_TABLE(EXPIRE_TIME TIMESTAMP);
Following code snippet is inside stored proc :
PO_EXPIRETIME :- OUT PARAM of procedure a varchar2
SELECT TO_CHAR(SYS_EXTRACT_UTC(EXPIRE_TIME))
INTO PO_EXPIRETIME
FROM DIALOGUE_TABLE;
When I run Stored Proc from server using EXEC and print PO_EXPIRETIME timestamp is proper with UTC format.
But when I call stored procedure from OCCI and client the timestamp recieved is not same but that is the actual timestamp in table not UTC formatted.
Maybe something I am missing but what I don't know?
Is there something in client side I need to do?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果该列被声明为 TIMESTAMP，而不是 TIMESTAMP WITH TIME ZONE 或 TIMESTAMP WITH LOCAL TIME ZONE，则存储在表中的时间戳将不具有时区部分。因此，SYS_EXTRACT_UTC 将使用会话时区将时间戳转换为 UTC，该时区由客户端控制，并且在不同客户端计算机上可能有所不同。我怀疑，如果您
从 OCCI 应用程序和 SQL*Plus 会话运行，最终会得到不同的结果，从而导致返回的字符串不同。
如果您希望返回的字符串独立于客户端计算机，我倾向于建议将时区存储在数据库列中。是否可以更改架构定义以使用 TIMESTAMP WITH TIME ZONE？除此之外，您可以确保每个客户端计算机都配置了相同的时区和/或运行显式的 ALTER SESSION，即
在存储过程中确保转换始终从特定时区完成。
If the column is declared as a TIMESTAMP and not, say, a TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE, the timestamp that is stored in the table will not have a time zone component. As a result, SYS_EXTRACT_UTC will convert the timestamp to UTC using the session time zone which is something that is controlled by the client and may be different on different client machines. I suspect that if you run
from your OCCI application and from your SQL*Plus session that you will end up with different results which is causing the strings returned to be different.
If you want the string that is returned to be independent of the client machine, I would tend to suggest storing the time zone in the database column. Is changing the schema definition to use a TIMESTAMP WITH TIME ZONE an option? Barring that, you could ensure that every client machine has the same time zone configured and/or run an explicit ALTER SESSION, i.e.
in the stored procedure to ensure that the conversion is always done from a particular time zone.