如何在Oracle表中的TIMESTAMP数据类型中单独插入日期值

d8tt03nd  于 2023-10-16  发布在  Oracle
关注(0)|答案(2)|浏览(163)

我试图插入日期值到Oracle表中的时间戳数据类型。
我期待输出为“31/08/2023”。但不知何故,我无法成功。无论12.00.00.000000000我使用哪种格式,例如TO_DATE、DATE、trunc等,该值总是插入为“31-AUG-23 www.example.com AM”。
下面是我正在尝试的示例代码。

Insert into ABC.TABLE1 (SYSTEM,NAME,EMPID,CONNECTIONID,LOGDATE,DEPT) values ('B11','PATRICK','G18','B11A1',TO_DATE('31/08/2023','DD/MM/YYYY'),'ING');

输出始终存储为“31-AUG-23 12.00.00.000000000 AM”。
我尝试了下面的格式。

TO_DATE('31/08/2023','DD/MM/YYYY')
trunc('31/08/2023','DD/MM/YYYY')
DATE('31/08/2023','DD/MM/YYYY')

本人确认NLS_DATE_FORMAT = DD.MM.YYYY
我需要专业知识来解决这个问题。
提前感谢!!

2ledvvac

2ledvvac1#

在Oracle中,DATETIMESTAMP都是二进制数据类型,包含:

  • 日期和时间(世纪、世纪年、月、日、小时、分钟和秒为7个字节)
  • TIMESTAMP也可以包含小数秒(根据精度,在0到6字节之间)
  • 一个TIMESTAMP WITH TIME ZONE还将包括7个字节的时区信息。

对于DATETIMESTAMP,您将ALWAYS在数据类型中同时包含日期和时间组件。它也Never以任何特定的人类可读格式存储(它们存储为二进制值)。
是否有可能存储为“DD/MM/YYYY”格式。
不,如上所述,它是以二进制格式存储的,总是有一个时间组件。
当使用TO_DATE('31/08/2023','DD/MM/YYYY')时,您正在创建一个DATE,其中日期组件为2023-08-31,时间组件为00:00:00。当Oracle将其插入TIMESTAMP列时,它将隐式地从DATE转换为TIMESTAMP,并将任何小数秒设置为0。这就是插入到数据库列中的内容,并且您不能更改存储数据的格式(如前所述,它是没有任何特定人类可读格式的二进制数据)
您要做的是更改TIMESTAMP显示方式(而不是尝试更改它的存储方式)。
您可以:
1.查看表的内容时,在SELECT查询中使用TO_CHAR(log_date, 'DD.MM.YYYY');或
1.更改客户端应用程序的首选项,使其以不同的格式显示TIMESTAMP。您需要参考客户端应用程序的文档来了解如何执行此操作。
如果您正在使用Oracle的客户端应用程序之一(即SQL*Plus、SQL Developer或SQLCl),然后您可以更改NLS_TIMESTAMP_FORMAT

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD.MM.YYYY';

但这会改变它显示ALLTIMESTAMP s的方式,并始终隐藏时间组件(您可能不想这样做,只需使用TO_CHAR即可)。
顺便说一句,如果你没有使用任何时间组件,我会问你为什么要使用TIMESTAMP(9)数据类型来存储精度为9位数的小数秒?如果您不使用时间组件,则使用DATETIMESTAMP(0),因为它们都将以7字节存储。

rkue9o1l

rkue9o1l2#

您所观察到的行为是由于Oracle中TIMESTAMP数据类型的性质造成的。当您将DATE值插入TIMESTAMP列时,Oracle将通过添加默认时间午夜(00:00:00.000000000)将DATE隐式转换为TIMESTAMP。这就是为什么您会在输出中看到“31-AUG-23 12.00.00.000000000 AM”。
格式“31/08/2023”是日期的表示,数据库中的实际存储值不具有此格式。查询表时看到的格式由会话的NLS设置或用于查询数据库的工具的默认设置决定。
如果您真的希望只存储日期而不存储时间组件,则应该对LOGDATE列使用DATE数据类型。如果您需要使用TIMESTAMP数据类型用于其他原因(例如,存储小数秒),那么您将始终使用时间组件,即使只是午夜。
以下是一些澄清和建议:
1.更改列数据类型:如果您只想存储日期而不是时间,请考虑将LOGDATE列数据类型更改为DATE。这将只存储日期,而不存储时间组件。
1.验证日期:如果您想在查询时以特定格式显示日期,可以使用TO_CHAR函数:

SELECT TO_CHAR(LOGDATE, 'DD/MM/YYYY') FROM ABC.TABLE1;
  1. NLS_日期_格式:NLS_DATE_FORMAT设置会影响日期的默认显示格式,但不会更改实际存储的值。如果您使用的是SQL*Plus等工具,则可以设置会话的日期格式:
ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY';

设置后,当您查询LOGDATE列时,如果列数据类型为DATE,则应以“31/08/2023”格式显示。
1.插入值:当插入值时,你只需要使用TO_DATE函数:

Insert into ABC.TABLE1 (SYSTEM, NAME, EMPID, CONNECTIONID, LOGDATE, DEPT) 
values ('B11','PATRICK','G18','B11A1',TO_DATE('31/08/2023','DD/MM/YYYY'),'ING');

您尝试的其他格式(truncDATE)不适合此用例。
总而言之,如果你只想存储日期而不存储时间,请使用DATE数据类型。如果需要存储时间组件,那么在插入没有特定时间的日期时,必须接受默认时间午夜。

相关问题