时间戳 加载到 DATE 列中的数据无法转换

Question

我有一个简单的问题:我目前正试图将数据作为excel文件插入我的数据库(APEX)。我正在加载的一个表是一个时间戳列yyyy-mm-dd hh:mm:ss，但我正在加载它的列是日期格式mm/dd/yyyy。插入时，列显示为日期mm/dd/yyyy，但当我试图查询表查找日期时，没有找到数据。

这是我的查询:SELECT * FROM my_table WHERE DATE_f = '01/20/2023'

我想即使数据显示为Date格式作为列表是Date列，它仍然在Timestamp…

如果你知道问题是什么，我该如何解决它?(转换我的列类型可能)(我需要Data as Date格式，无法在Excel文件中进行转换)

谢谢你的帮助

Answer 1

在Oracle中，DATE是由7个字节组成的二进制数据类型，分别表示世纪、年月日、月、日、时、分和秒。总是它NEVER*以任何特定的人类可读格式存储。

因此，如果您使用年对秒组件加载数据，那么您的DATE将仍然具有年对秒组件。

(如果您加载的日期只有年-日组件，那么您的DATE将具有这些年-日组件，并将给予午夜的默认时间组件)

插入后，列显示为日期mm/dd/yyyy

这是显示您的客户端应用程序正在使用的格式;它与数据库存储在后台的二进制数据不一样。

您的客户端应用程序(Apex)正试图提供帮助，并将二进制值转换为您(用户)可以理解的东西，并且通过对日期应用默认格式模型来实现这一点。这个模型恰好是mm/dd/yyyy不显示日期的时间分量;但是，即使这些时间组件没有显示出来，它们仍然存在。

这是我的查询:SELECT * FROM my_table WHERE DATE_f = '01/20/2023'

你的查询有效:

SELECT *
FROM   my_table
WHERE  DATE_f = TO_DATE(
'01/20/2023',
( SELECT value
FROM   NLS_SESSION_PARAMETERS
WHERE  parameter = 'NLS_DATE_FORMAT' )
);

因为你的NLS_DATE_FORMAT参数是mm/dd/yyyy，那么转换是成功的，日期将被给予午夜的默认时间组件。因此，只有当数据库中的日期有午夜的时间成分时，这才会起作用;如果它有任何非昼夜时间组件那么它就不能匹配

你能做的是匹配一个范围:

SELECT *
FROM   my_table
WHERE  DATE_f >= DATE '2023-01-20'
AND    DATE_f <  DATE '2023-01-21'

或者，您也可以匹配时间组件:

SELECT *
FROM   my_table
WHERE  DATE_f = DATE '2023-01-20' + INTERVAL '12:34:56' HOUR TO SECOND

或者，使用TIMESTAMP文字:

SELECT *
FROM   my_table
WHERE  DATE_f = TIMESTAMP '2023-01-20 12:34:56'

或者，使用TO_DATE:

SELECT *
FROM   my_table
WHERE  DATE_f = TO_DATE('2023-01-20 12:34:56', 'YYYY-MM-DD HH24:MI:SS');

你也可以使用TRUNC:

SELECT *
FROM   my_table
WHERE  TRUNC(DATE_f) = DATE '2023-01-20';

但是Oracle不能在DATE_F列上使用索引，而需要在TRUNC(date_f)上使用基于函数的索引。使用一个范围或匹配一个精确的时间允许使用索引。

---

如果你想改变Oracle使用的从字符串到日期和日期到字符串的隐式转换的格式模型，那么你可以使用:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

或者，如果你想对日期应用显式格式，那么你可以使用TO_CHAR:

SELECT column1,
column2,
TO_CHAR(DATE_f, 'YYYY-MM-DD HH24:MI:SS') AS date_f
FROM   my_table
WHERE  DATE_f >= DATE '2023-01-20'
AND    DATE_f <  DATE '2023-01-21'