如何以特定格式在Oracle中插入时间戳

问题描述

我不知道如何以不同于默认格式的格式插入当前时间.有人可以帮忙解释吗?

I am at a loss as how to insert the current time in a different format than the default. Can somebody help explain?

这是我的表格的创建方式:

Here is how my table was created:

CREATE TABLE ACTIVITY_LOG
(
TIME TIMESTAMP NOT NULL
, ACTIVITY VARCHAR2(200) NOT NULL
);

我的插入命令有效:

insert into activity_log
values (localtimestamp,'blah');

但是如何使用各种MM DD YY HH MM SS标签以不同的格式将localtimestamp值插入到表中?我已经尝试了以下方法，但是它给了我ORA-1830:日期格式图片在转换整个输入字符串错误之前结束.

But how do i insert the localtimestamp value into my table in a different format using the various MM DD YY HH MM SS tags? I've tried the following, but it gives me the ORA-1830: date format picture ends before converting entire input string error.

insert into activity_log
values (to_timestamp(localtimestamp,'YYYY/MM/DD'),'blah');

推荐答案

您不会以特定格式插入时间戳.时间戳(和日期)使用内部表示形式存储在数据库中，内部表示形式介于7和11个字节之间，具体取决于类型和精度. 在这个问题中还有更多关于此的问题，

You don't insert a timestamp in a particular format. Timestamps (and dates) are stored in the database using an internal representation, which is betwen 7 and 11 bytes depending on the type and precision. There is more about that in this question, among others.

您的客户端或应用程序决定如何以易于理解的字符串形式显示值.

Your client or application decides how to display the value in a human-readable string form.

当您这样做:

to_timestamp(localtimestamp,'YYYY/MM/DD')

您正在使用会话的NLS设置将localtimestamp隐式转换为字符串，然后将其转换回时间戳.这可能会偶然改变值-失去精度-但不会改变值在内部存储的方式.在您的情况下，NLS设置和您提供的格式不匹配会导致ORA-01830错误.

you are implicitly converting the localtimestamp to a string, using your session's NLS settings, and then converting it back to a timestamp. That may incidentally change the value - losing precision - but won't change how the value is stored internally. In your case the mismatch between the NLS setting and the format you are supplying is leading to an ORA-01830 error.

因此，您的第一次插入是正确的(假设您确实需要会话时间，而不是服务器时间).如果要查看以特定格式存储的值，则可以更改客户端会话的NLS设置，或者最好在查询时明确设置其格式，例如:

So your first insert is correct (assuming you really want the session time, not the server time). If you want to see the stored values in a particular format then either change your client session's NLS settings, or preferably format it explicitly when you query it, e.g.:

select to_char(time, 'YYYY-MM-DD HH24:MI:SS.FF3') from activity_log

这篇关于如何以特定格式在Oracle中插入时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！