blocks|key|1819519|text|我不是Oracle用户(好吧，无论如何最近)，但是...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1819520|在大多数数据库中(使用精确的语言)，日期不包括时间。有一个日期并不意味着你在那个日期表示一个特定的第二天。通常，如果您既想要日期又想要时间，这就称为时间戳。|1819521|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

I'm not an Oracle user (well, lately anyhow), BUT...

In most databases (and in precise language), a date doesn't include a time. Having a date doesn't imply that you are denoting a specific second on that date. Generally if you want a time as well as a date, that's called a timestamp.

blocks|key|2033213|text|根据SQL标准，日期值为YYYY-MM-DD。因此，即使Oracle存储时间信息，我的猜测是它们以一种与SQL标准兼容的方式显示值。在标准中，存在包含日期和时间信息的TIMESTAMP数据类型。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2033214|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

A DATE value per the SQL standard is YYYY-MM-DD. So even though Oracle stores the time information, my guess is that they're displaying the value in a way that is compatible with the SQL standard. In the standard, there is the TIMESTAMP data type that includes date and time info.

blocks|key|2033266|text|无论是日期格式、货币格式、优化器模式还是其他任何格式，依赖默认值设置为一个特定值IMHO都是不明智的。您应该始终在服务器、客户端或应用程序中设置所需的日期格式值。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2033267|特别是，在转换日期或数字数据类型以用于显示时，切勿依赖默认值，因为对数据库的一次更改都可能中断应用程序。始终使用显式转换格式。多年来，我在Oracle系统上工作，开箱即用的默认日期显示格式是MM/DD/RR，这让我抓狂，但至少迫使我总是使用显式转换。|2033268|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

It's never wise to rely on defaults being set to a particular value, IMHO, whether it's for date formats, currency formats, optimiser modes or whatever. You should always set the value of date format that you need, in the server, the client, or the application.

In particular, never rely on defaults when converting date or numeric data types for display purposes, because a single change to the database can break your application. Always use an explicit conversion format. For years I worked on Oracle systems where the out of the box default date display format was MM/DD/RR, which drove me nuts but at least forced me to always use an explicit conversion.

blocks|key|2033307|text|您确定没有将Oracle数据库与Oracle+SQL+Developer混淆吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2033308|数据库本身没有日期格式，日期以原始形式从数据库中出来。这取决于客户端软件来呈现它，SQL+Developer确实使用YYYY-MM-DD作为其默认格式，我同意这几乎是无用的。|2033309|编辑:正如下面评论的那样，SQL+Developer可以重新配置为正确显示日期值，只是它有错误的默认值。|2033310|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Are you sure you're not confusing Oracle database with Oracle SQL Developer? 

The database itself has no date format, the date comes out of the database in raw form. It's up to the client software to render it, and SQL Developer does use YYYY-MM-DD as its default format, which is next to useless, I agree.

edit: As was commented below, SQL Developer can be reconfigured to display DATE values properly, it just has bad defaults.

blocks|key|2033344|text|如果使用此查询为数据仓库生成输入文件，则需要适当地格式化数据。从本质上讲，在这种情况下，您将日期(具有时间部分)转换为字符串。您需要显式格式化字符串或更改nls_date_format以设置默认值。在您的查询中，您可以简单地这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2033345|select+to_char(some_date,+'yyyy-mm-dd+hh24:mi:ss')+my_date
++from+some_table;|code-block|syntax|javascript|2033346|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you are using this query to generate an input file for your Data Warehouse, then you need to format the data appropriately. Essentially in that case you are converting the date (which does have a time component) to a string. You need to explicitly format your string or change your nls_date_format to set the default. In your query you could simply do:

<pre><code>select to_char(some_date, 'yyyy-mm-dd hh24:mi:ss') my_date
 from some_table;
</code></pre>

blocks|key|1819791|text|这是客户端的一个“问题”，而不是真正的Oracle问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1819792|它是客户端应用程序，它以这种方式格式化和显示日期。|1819793|在您的例子中，使用SQL*Plus进行格式化。|1819794|其他SQL客户端还有其他默认值。|1819795|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

This is a "problem" on the client side, not really an Oracle problem.

It's the client application which formats and displays the date this way. 

In your case it's SQL*Plus which does this formatting. 
Other SQL clients have other defaults.

blocks|key|1819891|text|Oracle同时具有Date和Timestamp数据类型。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1819892|根据Oracle+documentation的说法，Date和Timestamp之间的数据大小存在差异，因此当打算只包含日期字段时，显示日期格式是有意义的。此外，“它没有小数秒或时区。”-因此，当需要时间戳信息时，它不是最佳选择。|offset|length|1819893|日期字段可以很容易地格式化，以显示Oracle+SQL+Developer+-+Date+query+ran+in+PL/SQL+Developer+shows+time,+but+does+not+show+in+Oracle+SQL+Developer中的时间部分。但它不会显示小数秒或时区-为此，您需要Timestamp数据类型。|1819894|entityMap|0|LINK|mutability|MUTABLE|url|http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm#sthref52|1|https://stackoverflow.com/questions/6838744/date-query-ran-in-pl-sql-developer-shows-time-but-does-not-show-in-oracle-sql-d^0|0|2|K|0|0|14|2G|1|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@$D|T|E|U|1|V]]|A|$]]|$1|F|3|G|5|6|7|W|8|@]|9|@$D|X|E|Y|1|Z]]|A|$]]|$1|H|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|O]]|P|$5|K|L|M|A|$N|Q]]]]

Oracle has both the Date and the Timestamp data types.

According to <a href="http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm#sthref52" rel="nofollow noreferrer" title="Built-in Datatype Summary">Oracle documentation</a>, there are differences in data size between Date and Timestamp, so when the intention is to have a Date only field it makes sense to show the Date formatting. Also, "It does not have fractional seconds or a time zone." - so it is not the best choice when timestamp information is required.

The Date field can be easily formatted to show the time component in the Oracle SQL Developer - <a href="https://stackoverflow.com/questions/6838744/date-query-ran-in-pl-sql-developer-shows-time-but-does-not-show-in-oracle-sql-d">Date query ran in PL/SQL Developer shows time, but does not show in Oracle SQL Developer</a>. But it won't show the fractional seconds or the time zone - for this you need Timestamp data type.

blocks|key|2033551|text|在大多数IDE中，您可以为某些类型的数据配置默认掩码，如日期、货币、小数分隔符等。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2033552|如果您正在使用Oracle+SQL+Developer|2033553|工具>首选项>数据库>+NLS|2033554|日期格式:+YYYY-MM-DD+HH24:MI:SS|2033555|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

Most of the IDE you can configure the default mask for some kind of data as date, currency, decimal separator, etc.

If your are using Oracle SQL Developer:

Tool > Preferences > Database > NLS

Date Format: YYYY-MM-DD HH24:MI:SS

blocks|key|1820168|text|原因:如果您正在查看一列带有时间戳的数据，那么信息的_MOST_IMPORTANT_位就是年份。如果数据在数据库中已经存在了十年，了解这一点很重要。所以年份是第一位的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1820169|接下来是月，然后是日、小时、分钟，这是有道理的。客观地讲，这些是显示时间数据的最符合逻辑的顺序。|1820170|如果要在默认情况下显示数据，那么应该只显示数据的最重要部分，因此默认情况下只显示Y-M-D，这一点也很合理。可以显示其他所有内容，但默认情况下不会使您的sql报告变得混乱。|1820171|如果显示Y-M-D，则按日期排序是合乎逻辑的，因为它是连续的。计算机擅长顺序，而且看起来很有逻辑性。|1820172|终于来了。你想要M-D-Y的偏见就是你的偏见。即使在美国，也不是每个人都使用这种格式。因此，使用最符合逻辑的格式，当其他人决定默认逻辑时，不要感到愤怒。|1820173|(我是美国出生的，我不代表甲骨文。然而，我可以自己思考)|1820174|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|P|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|Q|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|S|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|M|$]]

reason:
if you are looking at a column of data with a time stamp, the _MOST_IMPORTANT_ bit of information is the year. If data has been in the db for ten years, that's important to know. so year comes first.

it makes sense that month would come next, then day, hour, minute. Objectively, these are the most logical sequence for time data to be displayed. 

it also makes sense that if you are going to display the data by default, you should display only the most significant portion of the data, so by default, only Y-M-D. everything else CAN be displayed, but it does not clutter your sql report by default. 

Ordering by date is logical if you display Y-M-D because it is sequential. Computers are good at sequential, and it looks logical.

finally. Your bias to want M-D-Y is your bias. Not everyone even in the US uses this format. So use the most logical format and don't be outraged when others decide to be logical by default.

(I am US born, and I do not represent Oracle. I can, however, think for myself)

Oracle's default date format is YYYY-MM-DD. Which means if I do:

<pre><code> select some_date from some_table
</code></pre>

...I lose the time portion of my date.

Yes, I know you can "fix" this with:

<pre><code> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
</code></pre>

But seriously, why isn't the above the default? Especially in a DBMS where the two primary time-tracking data types (DATE and TIMESTAMP) both have a time component that includes (at least) accuracy down to 1 second.

Oracle's default date format is YYYY-MM-DD, WHY?

Oracle数据库

数据仓库

数据库

服务器

Oracle的默认日期格式为YYYY-MM-DD。也就是说如果我这么做了： select some_date from some_table...I丢失了我约会的时间部分。是的，我知道你可以通过以下方式“修复”这个问题： alter session set nls_date_format='yyyy-mm-dd hh2...

问Oracle的默认日期格式是YYYY-MM-DD，为什么？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle的默认日期格式是YYYY-MM-DD，为什么？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle的默认日期格式是YYYY-MM-DD，为什么？
EN