oracle 更改表中数据的日期和时间戳格式

Change the date and time stamp format for data in the table

提问by Abhishek kumar

We have a huge Oracle database that contains data which has date and time stamps. Right now we are using mm-dd-yyyy format for date and EST Time zone for time stamp.

我们有一个巨大的 Oracle 数据库，其中包含带有日期和时间戳的数据。现在我们正在使用mm-dd-yyyy format for date and EST Time zone for time stamp.

Now we need to change the date format as yyyy-mm-dd and time stamps should be GMT timezone. We need to change already existing data in the tables to this new format as well as newly inserted data.

现在我们需要更改date format as yyyy-mm-dd and time stamps should be GMT timezone. 我们需要将表中已有的数据更改为这种新格式以及新插入的数据。

Any help on how to achieve this?

有关如何实现这一目标的任何帮助？

回答by kmkaplan

Changing the date format is just a matter of presentation. When selecting from you database, apply a format:

更改日期格式只是一个演示问题。从您的数据库中选择时，应用一种格式：

select to_char(sysdate, 'YYYY-MM-DD') from dual;

To get this format by default, set it in your session's NLS_DATE_FORMATparameter:

要默认获取此格式，请在会话的NLS_DATE_FORMAT参数中进行设置：

alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD';

You can also set the NLS_TIMESTAMP_FORMATand NLS_TIMESTAMP_TZ_FORMAT. See Setting Up a Globalization Support Environmentin Oracle's documentation.

您还可以设置NLS_TIMESTAMP_FORMAT和NLS_TIMESTAMP_TZ_FORMAT。请参阅Oracle 文档中的设置全球化支持环境。

Regarding time zones, normally it is the application that inserts the timestamp with time zonein the database that chooses what time zone it puts. But the application that queries these timestamps can request to get them back in another time zone. Compare the following queries:

关于时区，通常是应用程序timestamp with time zone在数据库中插入选择它放置的时区。但是查询这些时间戳的应用程序可以请求将它们取回另一个时区。比较以下查询：

select systimestamp from dual; -- This gives you the timestamp in the server configured time zone
select systimestamp at time zone 'EST' from dual; -- This is in your EST time zone
select systimestamp at time zone '+00:00' from dual; -- Finally the same timestamp in UTC.

To force a certain time zone you can force it with an after insert or updatetrigger. Something like:

要强制某个时区，您可以使用after insert or update触发器强制它。就像是：

create or replace trigger my_table_force_utc_timestamps
before insert or update on my_table
for each row
begin
    :new.my_timestamp_with_tz := :new.my_timestamp_with_tz at time zone '+00:00';
end;
/

If you reallywant to convert your timestamps to UTC you can try:

如果您真的想将时间戳转换为 UTC，您可以尝试：

update my_table set my_timestamp_with_tz = my_timestamp_with_tz at time zone '+00:00';

Edit: to change sysdatetime zone before inserting into tables you must in fact use systimestamp at time zone …that will be cast to a date value:

编辑：要sysdate在插入表之前更改时区，您实际上必须使用systimestamp at time zone …将转换为日期值的时区：

insert into my_table (date_column) values (systimestamp at time zone '+00:00');

You can read more about time zones in Oracle's documentation Datetime and Time Zone Parameters and Environment Variables. With the example table_dtthis would give:

您可以在 Oracle 的文档Datetime and Time Zone Parameters and Environment Variables 中阅读有关时区的更多信息。对于示例，table_dt这将给出：

SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

Session modifiee.

SQL> CREATE TABLE table_dt (c_id NUMBER, c_dt DATE);

Table creee.

SQL> insert into table_dt values (1, systimestamp);

1 ligne creee.

SQL> insert into table_dt values (2, systimestamp at time zone '+00:00');

1 ligne creee.

SQL> select * from table_dt;

      C_ID C_DT
---------- ----------------------
         1 13-JANV.-2013 16:31:41
         2 13-JANV.-2013 15:32:08

回答by rs.

You can use new_timeoracle function

您可以使用new_timeoracle 函数

SELECT to_char(new_time(to_date('01-03-2013','mm-dd-yyyy HH24:MI'), 
                        'EST', 'GMT'),'yyyy-mm-dd HH24:MI') d
FROM DUAL;

Use this to test on your database:

使用它来测试您的数据库：

SELECT to_char(new_time(sysdate, 'EST', 'GMT'),'yyyy-mm-dd HH24:MI') d
FROM DUAL;