如何以我想要的格式获取此时间戳，Oracle SQL [英] How do I get this timestamp in the format I want, Oracle SQL

问题描述

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

I firstly run the following command, and get the following result:

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

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

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

This is the format I want to have a TIMESTAMP object stored in.

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

When I convert it back though, it does not come in the format I want:

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.

In fact, it changes 2018 to be at the end, sets 07 to be "JUL" and the 10 is now at the front. Also the time is separated by dots, has many 0's and an AM.

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

How can I fix this? I am new to SQL development so I am not sure about the formatting.

非常感谢

推荐答案

正如@Gordon 所说，时间戳(和日期)的存储格式不是您可以识别的，Oracle 使用的是您永远不需要了解或真正需要了解的内部表示形式.检查(但如果您对这类事情感兴趣，它会被记录在案).

As @Gordon said, timestamps (and dates) are not stored in a format you would recognise Oracle uses an internal representation that you never really need to know about or examine (but it is documented if you're interested in that sort of thing).

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

When you query a timestamp it is displayed using your client's NLS settings, unless you have a client that overrides those. I can set my session up to match what you are seeing:

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

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

And I can change it see what you want to see:

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 来做:

But all you are doing is converting from a timestamp with time zone (which is what systimestamp is) to a string and then back to a timestamp. You are losing the time zone portion, and any fractional seconds; which you could also do with a cast:

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

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

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

You can see the timezone and fraction seconds with your default timestamp_tz format:

select systimestamp from dual;

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

并使用不同的 alter 更改它:

and change it with a different 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

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

Which isn't entirely relevant if you're really talking about storing timestamps in a table, but shows that there are variations.

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

In your table make the data type timestamp (or timestamp with time zone or timestamp with local time zone), and only worry about formatting the value as a string for presentation to the end user, at the last possible moment.

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

When you do need to display it, if the display format is important to you then use to_char() with an explicit format mask - do not assume that anyone else running your queries will have the same NLS settings. As you can see, it's easy to change those to modify the output. (Most clients have a way to let you set the defaults so you don't have to do the same alter commands every time you connect; e.g. in SQL Developer, from Tools->Preferences->Database->NLS). If you want to always show the same format then use something like:

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'

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

which also shows the column value being filtered (as a timestamp still) using a timestamp literal.

这篇关于如何以我想要的格式获取此时间戳，Oracle SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！