英文:
How to transformation Oracle TIMESTAMP?
问题
我想将 Oracle TIMESTAMP 转换为 'YYYY-MM-DD HH24:MI:SS'
如何转换 TIMESTAMP？
↓ 这种方法不好
选择
TO_CHAR(TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS:FF') AS TIME_STAMP
从
所有对象
请教我你的知识。
英文:
I want to transformation Oracle TIMESTAMP to 'YYYY-MM-DD HH24:MI:SS'
How to exchange TIMESTAMP ?
↓ This method is not good
SELECT
TO_CHAR(TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS:FF') AS TIME_STAMP
FROM
ALL_OBJECTS
Please teach me your knowledge
答案1
得分: 1
Your query doesn't work because there's no TIMESTAMP column in ALL_OBJECTS. Even if there were such a column, you'd get - as result - a lot of rows. In my 21cXE, that would be:
您的查询不起作用，因为在ALL_OBJECTS中没有TIMESTAMP列。即使有这样的列，结果也会有很多行。在我的21cXE中，行数为:
SQL> select count(*) from all_objects;
COUNT(*)
----------
64460
rows; what would you do with that?
行；您打算如何处理这些行？
Perhaps you meant to do this?
也许您想要执行这个操作：
SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS:FF') AS TIME_STAMP FROM dual;
TIME_STAMP
-----------------------------
2023-07-13 05:47:48:130000
Alternatively, you could alter session to avoid TO_CHAR, e.g.
或者，您可以更改会话以避免使用 TO_CHAR，例如：
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
13-JUL-23 05.52.06.291000 AM +02:00
SQL> alter session set nls_timestamp_tz_format = 'yyyy-mm-dd hh24:mi:ss:ff';
Session altered.
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
2023-07-13 05:56:58:734000
SQL>
To check what are default values, see e.g.
要检查默认值是什么，可以查看如下：
SQL> select parameter, value from v$nls_parameters
2 where parameter in ('NLS_DATE_FORMAT', 'NLS_TIMESTAMP_FORMAT', 'NLS_TIMESTAMP_TZ_FORMAT');
PARAMETER VALUE
------------------------------ ------------------------------
NLS_DATE_FORMAT DD-MON-RR
NLS_TIMESTAMP_FORMAT dd.mm.yyyy hh24:mi:ss.ff3
NLS_TIMESTAMP_TZ_FORMAT yyyy-mm-dd hh24:mi:ss:ff
SQL>
英文:
Your query doesn't work because there's no TIMESTAMP column in ALL_OBJECTS. Even if there were such a column, you'd get - as result - a lot of rows. In my 21cXE, that would be
SQL> select count(*) from all_objects;
COUNT(*)
----------
64460
rows; what would you do with that?
Perhaps you meant to do this?
SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS:FF') AS TIME_STAMP FROM dual;
TIME_STAMP
-----------------------------
2023-07-13 05:47:48:130000
Alternatively, you could alter session to avoid TO_CHAR, e.g.
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
13-JUL-23 05.52.06.291000 AM +02:00
SQL> alter session set nls_timestamp_tz_format = 'yyyy-mm-dd hh24:mi:ss:ff';
Session altered.
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
2023-07-13 05:56:58:734000
SQL>
To check what are default values, see e.g.
SQL> select parameter, value from v$nls_parameters
2 where parameter in ('NLS_DATE_FORMAT', 'NLS_TIMESTAMP_FORMAT', 'NLS_TIMESTAMP_TZ_FORMAT');
PARAMETER VALUE
------------------------------ ------------------------------
NLS_DATE_FORMAT DD-MON-RR
NLS_TIMESTAMP_FORMAT dd.mm.yyyy hh24:mi:ss.ff3
NLS_TIMESTAMP_TZ_FORMAT yyyy-mm-dd hh24:mi:ss:ff
SQL>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论