sql - 如何以我想要的格式获取此时间戳，Oracle SQL

Question

我首先运行以下命令，得到如下结果:

select to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS') from dual;

TO_CHAR(SYSTIMESTAM
-------------------
2018-07-10 10:21:40

这是我想要存储 TIMESTAMP 对象的格式。

当我将它转换回来时，它不是我想要的格式:

select to_timestamp(to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') from dual;

TO_TIMESTAMP(TO_CHAR(SYSTIMESTA
-------------------------------
10-JUL-18 10.21.40.000000000 AM

实际上，它将 2018 更改为末尾，将 07 设置为“JUL”，而 10 现在位于最前面。时间也由点分隔，有许多 0 和一个 AM。

我该如何解决这个问题？我是 SQL 开发的新手，所以我不确定格式。

非常感谢

Answer 1

正如@Gordon 所说，时间戳(和日期)未以您会识别的格式存储的事情)。

当您查询时间戳时，它会使用您客户端的 NLS 设置显示，除非您有覆盖这些设置的客户端。我可以设置我的 session 以匹配您所看到的:

alter session set nls_timestamp_format = 'DD-MON-RR HH.MI.SS.FF AM';

select to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS') from dual;

TO_CHAR(SYSTIMESTAM
-------------------
2018-07-10 15:37:31

select to_timestamp(to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') from dual;

TO_TIMESTAMP(TO_CHAR(SYSTIMESTA
-------------------------------
10-JUL-18 03.37.31.000000000 PM

我可以改变它，看看你想看到什么:

alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS';

select to_timestamp(to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') from dual;

TO_TIMESTAMP(TO_CHA
-------------------
2018-07-10 15:37:32

但是您所做的只是将带时区的时间戳(systimestamp 是什么)转换为字符串，然后再转换回时间戳。您正在丢失时区部分和任何小数秒；你也可以用 cast 来做:

select cast(systimestamp as timestamp(0)) from dual;

CAST(SYSTIMESTAMPAS
-------------------
2018-07-10 15:37:32

您可以使用默认的 timestamp_tz 格式查看时区和小数秒:

select systimestamp from dual;

SYSTIMESTAMP                        
------------------------------------
2018-07-10 15:37:33.776469000 +01:00

并用不同的alter改变它:

alter session set nls_timestamp_tz_format = 'YYYY-MM-DD HH24:MI:SS.FF3 TZH:TZM';

select systimestamp from dual;

SYSTIMESTAMP                  
------------------------------
2018-07-10 15:37:34.070 +01:00

如果您真的在谈论在表中存储时间戳，这并不完全相关，但表明存在变化。

在您的表中创建数据类型timestamp(或timestamp with time zone 或timestamp with local time zone)，只担心将值格式化为字符串，以便在最后可能的时刻呈现给最终用户。

当您确实需要显示它时，如果显示格式对您很重要，则使用带有显式格式掩码的 to_char() - 不要假设运行您的查询的其他任何人都具有相同的格式NLS 设置。如您所见，更改这些内容以修改输出很容易。 (大多数客户端都有办法让您设置默认值，这样您就不必在每次连接时都执行相同的 alter 命令；例如，在 SQL Developer 中，从 Tools->Preferences->Database->NLS)。如果您想始终显示相同的格式，请使用类似的内容:

select to_char(your_column, 'YYYY-MM-DD HH24:MI:SS') as column_alias
from your_table
where your_column < timestamp '2018-01-01 00:00:00'

它还显示了使用时间戳文字过滤的列值(仍然作为时间戳)。