[20200121]CURRENT_TIMESTAMP LOCALTIMESTAMP SYSTIMESTAMP的不同.TXT

[20200121]CURRENT_TIMESTAMP LOCALTIMESTAMP SYSTIMESTAMP的不同.TXT --//别人的问题，实际上我自己也不知道： --//我的环境定义如下： $ set | grep NLS NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' NLS_LANG=AMERICAN_AMERICA.ZHS16GBK NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF9' NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF9 TZH:TZM' NLS_TIME_TZ_FORMAT='HH24.MI.SSXFF TZH:TZM' 1.环境： SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.测试: SCOTT@book> select current_timestamp ,systimestamp from dual ; CURRENT_TIMESTAMP                    SYSTIMESTAMP ------------------------------------ ------------------------------------ 2020-01-21 09:01:43.030480000 +08:00 2020-01-21 09:01:43.030475000 +08:00 SCOTT@book> select current_timestamp ,systimestamp,current_timestamp from dual ; CURRENT_TIMESTAMP                    SYSTIMESTAMP                         CURRENT_TIMESTAMP ------------------------------------ ------------------------------------ ------------------------------------ 2020-01-21 09:01:50.968273000 +08:00 2020-01-21 09:01:50.968265000 +08:00 2020-01-21 09:01:50.968273000 +08:00 --//不知道大家注意没有，最后一个字段先执行，看秒后面数值。(030475000<030480000) --//而第2次执行，先执行第3个字段的current_timestamp，但是第1个current_timestamp数值从第3个输出值一致。 --//两个都带有时区，很明显格式使用NLS_TIMESTAMP_TZ_FORMAT的定义。 --//链接：https://community.oracle.com/thread/1009877 SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE. CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of data type TIMESTAMP WITH TIME ZONE. The time zone offset reflects the current local time of the SQL session. If you omit precision, then the default is 6. The difference between this function and LOCALTIMESTAMP is that CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value while LOCALTIMESTAMP returns a TIMESTAMP value. SCOTT@book> select current_timestamp ,systimestamp,localtimestamp  from dual ; CURRENT_TIMESTAMP                     SYSTIMESTAMP                         LOCALTIMESTAMP ------------------------------------- ------------------------------------ ----------------------------- 2020-01-21 09:07:52.129754000 +08:00  2020-01-21 09:07:52.129747000 +08:00 2020-01-21 09:07:52.129754000 SCOTT@book> select localtimestamp,systimestamp,current_timestamp  from dual ; LOCALTIMESTAMP                SYSTIMESTAMP                         CURRENT_TIMESTAMP ----------------------------- ------------------------------------ ------------------------------------ 2020-01-21 09:12:36.765938000 2020-01-21 09:12:36.765932000 +08:00 2020-01-21 09:12:36.765938000 +08:00 --//LOCALTIMESTAMP的格式受NLS_TIMESTAMP_FORMAT控制。 --//LOCALTIMESTAMP 的输出与CURRENT_TIMESTAMP的输出一样(不包括时区部分,当前客户时区与服务器一致)，也就是运算仅仅1次。 --//设置时区不一致看看。 SCOTT@book> alter session set time_zone='+0:00'; Session altered. SCOTT@book> select localtimestamp,systimestamp,current_timestamp  from dual ; LOCALTIMESTAMP                SYSTIMESTAMP                         CURRENT_TIMESTAMP ----------------------------- ------------------------------------ ------------------------------------ 2020-01-21 01:17:05.667409000 2020-01-21 09:17:05.667403000 +08:00 2020-01-21 01:17:05.667409000 +00:00 --//注意看秒后面的数值,CURRENT_TIMESTAMP与LOCALTIMESTAMP存在转换。一些细节自己慢慢体会。 --//注意一定注意要设置带时区与不带时区的格式，这样看数据库一些时间信息才比较准确。 --//我自己在上面犯过一些错误。像链接http://blog.itpub.net/267265/viewspace-2152520/，如果不带时区显示，很容易出现歧义性。 NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF9' NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF9 TZH:TZM' --//可以不要后面9。FF9改成FF，这样秒后面仅仅6位小数点，自己就是这样设置的，为了测试设置FF9。