目录
数据类型DATE和TIMESTAMP
DATE
TIMESTAMP
TIMESTAMP
TIMESTAMP with time zone
TIMESTAMP with local time zone
TZ_OFFSET
CURRENT_DATE
CURRENT_TIMESTAMP
LOCALTIMESTAMP
DBTIMEZONE
SESSIONTIMEZONE
TO_TIMESTAMP
TO_TIMESTAMP_TZ
Interval Expressions
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
使用方式
TO_YMINTERVAL
数据类型DATE和TIMESTAMP
DATE
它可以存放年、月、日、世纪、时、分、秒，他的最小粒度是秒
可以使用TO_CHAR函数将其进行数据类型转换。
创建表tb_date01，x、y字段分别采用date和varchar2
| SQL> create table tb_date01(x date,y varchar2(50));
Table created.
SQL> insert into tb_date01 values(sysdate,to_char(sysdate));
1 row created.
SQL> select * from tb_date01;
X Y
------------------- -------------------------------
2021-01-13 09:23:27 2021-01-13 09:23:27
SQL> desc tb_date01;
Name Null? Type
----------------------------------------- -------- -------------------
X DATE
Y VARCHAR2(50)
SQL> insert into tb_date01 values(sysdate,to_char(sysdate));
1 row created.
SQL> insert into tb_date01 values(to_char(sysdate),sysdate);
1 row created.
SQL> select * from tb_date01;
SQL> insert into tb_date01 values('2222-01-13 09:26:26',to_date('2222-01-13 09:26:26'));
1 row created.
1 row created.
SQL> select * from tb_date01;
X Y
------------------- --------------------------------------------------
2021-01-13 09:23:27 2021-01-13 09:23:27
2021-01-13 09:26:26 2021-01-13 09:26:26
2222-01-13 09:26:26 2222-01-13 09:26:26
2222-01-13 09:26:26 2222-01-13 09:26:26 |
可以看出无论是否指定数据类型，都可以自动进行隐式转换。
TIMESTAMP
当DATE类型数据不足以区分事件前后的时候就需要它了，它不仅包含了DATE的所有信息，更提供了精确到小数秒的信息
TIMESTAMP包括三个种类
TIMESTAMP
不带时区的时间戳
TIMESTAMP with time zone
带有时区的时间戳
TIMESTAMP with local time zone
带有本地时区的时间戳，本地时区不显示
创建表tb_date02
| SQL> create table tb_date02(d1 timestamp,d2 timestamp with time zone,d3 timestamp with local time zone,d4 date);
Table created.
SQL> desc tb_date02;
Name Null? Type
----------------------------------------- -------- ----------------------------
D1 TIMESTAMP(6)
D2 TIMESTAMP(6) WITH TIME ZONE
D3 TIMESTAMP(6) WITH LOCAL TIME
ZONE
D4 DATE
SQL> insert into tb_date02 values(systimestamp,systimestamp,systimestamp,systimestamp);
1 row created.
SQL> insert into tb_date02 values(sysdate,sysdate,sysdate,sysdate);
1 row created.
SQL> select * from tb_date02;
D1 D2 D3 D4
---------------------------- ----------------------------------- ------------------------------ -------------------
13-JAN-21 09.51.35.360964 AM 13-JAN-21 09.51.35.360964 AM +08:00 13-JAN-21 09.51.35.360964 AM 2021-01-13 09:51:35
13-JAN-21 09.51.55.000000 AM 13-JAN-21 09.51.55.000000 AM +08:00 13-JAN-21 09.51.55.000000 AM 2021-01-13 09:51:55 |
可以看出imestamp with local time zone，在存储时间时，将会话时间存为数据库服务器所在时区的时 间，当在其他时区进行查询时，数据库将其转化为会话所在时区的时间进行显示
TZ_OFFSET
此函数表示语句执行的时候所在地区同标准时区偏移了多少时区
官方给出解释是这样的
TZ_OFFSET returns the time zone offset corresponding to the argument based on the date the statement is executed. You can enter a valid time zone name, a time zone offset from UTC (which simply returns itself), or the keyword SESSIONTIMEZONE or DBTIMEZONE. For a listing of valid values for time_zone_name, query the TZNAME column of the V$TIMEZONE_NAMES dynamic performance view.
示例
| SQL> elect tz_offset('US/Eastern') from dual;
TZ_OFFS
-------
-05:00
SQL> select tz_offset('PRC') from dual;
TZ_OFFS
-------
+08:00 |
地区名可以在v$timezone_names中查看
| SQL> select * from v$timezone_names;
TZNAME TZABBREV CON_ID
-------------------- -------------------- ----------
Africa/Abidjan LMT 0
Africa/Abidjan GMT 0
Africa/Accra LMT 0
Africa/Accra GMT 0
.
.
.
Zulu UTC 0
2312 rows selected. |
CURRENT_DATE
显示当前会话所在的时区日期和时间
CURRENT_DATE returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE.
| SQL> ALTER SESSION SET TIME_ZONE = '-5:0';
Session altered.
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;
SESSIONTIMEZONE CURRENT_DATE
------------------------------ ----------------------------------------
-05:00 12-JAN-2021 21:29:17
SQL> ALTER SESSION SET TIME_ZONE = '-8:0';
Session altered.
SQL> SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;
SESSIONTIMEZONE CURRENT_DATE
------------------------------ ----------------------------------------
-08:00 12-JAN-2021 18:29:25 |
可见对于会话所在的时区敏感，使用的时候需要考虑时区问题
CURRENT_TIMESTAMP
显示当前会话所在的时区日期和时间，包括时、分、秒
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.
| SQL> ALTER SESSION SET TIME_ZONE = '-5:0';
Session altered.
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;
SESSIONTIMEZONE CURRENT_TIMESTAMP
------------------------------ ----------------------------------------
-05:00 12-JAN-21 09.37.16.040116 PM -05:00
SQL> ALTER SESSION SET TIME_ZONE = '-8:0';
Session altered.
SQL> SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;
SESSIONTIMEZONE CURRENT_TIMESTAMP
------------------------------ ----------------------------------------
-08:00 12-JAN-21 06.37.26.520437 PM -08:00 |
可见对于会话所在的时区敏感，使用的时候需要考虑时区问题
返回值类型是 timestamp with time zone
| SQL> CREATE TABLE current_test (col1 TIMESTAMP WITH TIME ZONE);
Table created.
SQL> INSERT INTO current_test VALUES(TO_TIMESTAMP_TZ(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));
(TO_TIMESTAMP_TZ(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'))
*
ERROR at line 1:
ORA-01830: datecha format picture ends before converting entire input string |
因为插入的类型不包含时区部分
| SQL> INSERT INTO current_test VALUES(TO_TIMESTAMP_TZ(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM TZH:TZM'));
1 row created.
SQL> select * from current_test;
COL1
-------------------------------------------------------------------------
12-JAN-21 06.39.26.051731 PM -08:00 |
LOCALTIMESTAMP
显示当前会话所在的时区日期和时间，包括时、分、秒，和CURRENT_TIMESTAMP的区别是不带时区，为timestamp
LOCALTIMESTAMP returns the current date and time in the session time zone in a value of data type TIMESTAMP. The difference between this function and CURRENT_TIMESTAMP is that LOCALTIMESTAMP returns a TIMESTAMP value while CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value.
| SQL> ALTER SESSION SET TIME_ZONE = '-5:00';
Session altered.
SQL> SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;
CURRENT_TIMESTAMP LOCALTIMESTAMP
---------------------------------------- ---------------------------------
12-JAN-21 09.54.55.179188 PM -05:00 12-JAN-21 09.54.55.179188 PM
SQL> ALTER SESSION SET TIME_ZONE = '-8:00';
Session altered.
SQL> SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;
CURRENT_TIMESTAMP LOCALTIMESTAMP
---------------------------------------- ---------------------------------
12-JAN-21 06.55.00.594384 PM -08:00 12-JAN-21 06.55.00.594384 PM |
| SQL> INSERT INTO local_testVALUES (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF'));
VALUES (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF'))
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string |
因为插入的类型不包含时区部分
| SQL> INSERT INTO local_testVALUES (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));
1 row created.
SQL> select * from local_test;
COL1
--------------------------------------------------------------------------
12-JAN-21 06.55.26.401598 PM |
DBTIMEZONE
数据库服务器所在的时区
DBTIMEZONE returns the value of the database time zone. The return type is a time zone offset (a character type in the format '[+|-]TZH:TZM') or a time zone region name, depending on how the user specified the database time zone value in the most recent CREATE DATABASE or ALTER DATABASE statement.
| SQL> select dbtimezone from dual;
DBTIME
------
+00:00 |
SESSIONTIMEZONE
会话所在时区
SESSIONTIMEZONE returns the time zone of the current session. The return type is a time zone offset (a character type in the format '[+|-]TZH:TZM') or a time zone region name, depending on how the user specified the session time zone value in the most recent ALTER SESSION statement.
| SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
------------------------------
-08:00 |
从时间中抽取对应的信息
其中包括下图所示
| SQL> select extract(year from sysdate) year,extract(month from sysdate) month,extract(day from sysdate) day,extract(hour from systimestamp) hour,extract(minute from systimestamp) minute,extract(second from systimestamp) secone from dual;
YEAR MONTH DAY HOUR MINUTE SECONE
---------- ---------- ---------- ---------- ---------- ----------
2021 1 13 3 12 8.274569 |
由于产生了歧义，因此UNKNOW
| SQL> SELECT EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP) TIMEZONE_HOUR,EXTRACT(TIMEZONE_MINUTE FROM SYSTIMESTAMP) TIMEZONE_MINUTE,EXTRACT(TIMEZONE_REGION FROM SYSTIMESTAMP) TIMEZONE_REGION,EXTRACT(TIMEZONE_ABBR FROM SYSTIMESTAMP) TIMEZONE_ABBR FROM DUAL;
TIMEZONE_HOUR TIMEZONE_MINUTE TIMEZONE_R TIMEZONE_A
------------- --------------- ---------- ----------
8 0 UNKNOWN UNK |
TO_TIMESTAMP
将字符转换为timestamp
| SQL> select to_timestamp('22-Feb-02 22:22:22.222222','DD-Mon-RR HH24:MI:SS.FF') from dual;
TO_TIMESTAMP('22-FEB-0222:22:22.222222','DD-MON-RRHH24:MI:SS.FF')
-------------------------------------------------------------------
22-FEB-02 10.22.22.222222000 PM |
| SQL> SELECT TO_TIMESTAMP ('10-Sept-02 14:10:10.123000'
2 DEFAULT NULL ON CONVERSION ERROR,
3 'DD-Mon-RR HH24:MI:SS.FF',
4 'NLS_DATE_LANGUAGE = American') "Value"
5 FROM DUAL;
Value
-----------------------------------------------------------
|
TO_TIMESTAMP_TZ
将字符串转换为 timestamp with time zone
| SQL> select to_timestamp_tz('22-Feb-02 22:22:22.222222','DD-Mon-RR HH24:MI:SS.FF') from dual;
TO_TIMESTAMP_TZ('22-FEB-0222:22:22.222222','DD-MON-RRHH24:MI:SS.FF')
------------------------------------------------------------------
22-FEB-02 10.22.22.222222000 PM -08:00 |
在UNION中可以将空列强制转换为具有本地时区时间戳
| SELECT order_id, line_item_id,
CAST(NULL AS TIMESTAMP WITH LOCAL TIME ZONE) order_date
FROM order_items
UNION
SELECT order_id, to_number(null), order_date
FROM orders;
ORDER_ID LINE_ITEM_ID ORDER_DATE
---------- ------------ -----------------------------------
2354 1
2354 2
2354 3
2354 4
2354 5
2354 6
2354 7
2354 8
2354 9
2354 10
2354 11
2354 12
2354 13
2354 14-JUL-00 05.18.23.234567 PM
2355 1
2355 2
. . . |
Interval Expressions
INTERVAL又分为两种 INTERVAL YEAR TO MONTH 和 INTERVAL DAY TO SECOND
INTERVAL YEAR TO MONTH
表示储存经过多少年多少月的一个时间段
year:长度受 BC4712.1.1~AD9999.12.31 限制;month 类似
| SQL> create table tb_interval_year(t1 interval year(3) to month);
Table created.
SQL> desc tb_interval_year;
Name Null? Type
------------------ ------- -------------------------------
T1 INTERVAL YEAR(3) TO MONTH
SQL> insert into tb_interval_year values(interval '123-4' year(3) to month);
1 row created.
SQL> insert into tb_interval_year values(interval '123' year(3));
1 row created.
SQL> insert into tb_interval_year values(interval '123' month(3));
1 row created.
SQL> insert into tb_interval_year values(interval '0-9' year to month);
1 row created.
SQL> select * from tb_interval_year;
T1
----------------------------------- +001-02
+123-04
+123-00
+010-03
+000-09 |
INTERVAL DAY TO SECOND
储存经过多少天、时、分、秒的一个时间段
day:长度 0~9，默认为 2;
second:毫秒的长度 0~9，默认 6
| SQL> create table tb_interval_date(T2 interval day(3) to second);
Table created.
SQL> desc tb_interval_date;
Name Null? Type
----------- -------- ------------------------------------
T2 INTERVAL DAY(3) TO SECOND(6)
SQL> insert into tb_interval_date values(interval '1 2:34:56.789' day to second(3));
1 row created.
SQL> insert into tb_interval_date values(interval '1 2:34' day to minute);
1 row created.
SQL> insert into tb_interval_date values(interval '2 3'day to hour);
1 row created.
SQL> insert into tb_interval_date values(interval '123' day(3));
1 row created.
SQL> insert into tb_interval_date values(interval '0 12:34:56.789876' day to second(6));
1 row created.
SQL> select * from tb_interval_date;
T2
-----------------------------------------------------------
+001 02:34:56.789000
+001 02:34:00.000000
+002 03:00:00.000000
+123 00:00:00.000000
+000 12:34:56.789876 |
使用方式
| SQL> select sysdate s0,sysdate + t1 s1 from tb_interval_year;
S0 S1
----------------------------- -----------------------------
13-JAN-2021 17:07:44 13-MAR-2022 17:07:44
13-JAN-2021 17:07:44 13-MAY-2144 17:07:44
13-JAN-2021 17:07:44 13-JAN-2144 17:07:44
13-JAN-2021 17:07:44 13-APR-2031 17:07:44
13-JAN-2021 17:07:44 13-OCT-2021 17:07:44
SQL> select sysdate s3,sysdate + t2 s4 from tb_interval_date;
S3 S4
----------------------------- -----------------------------
13-JAN-2021 17:08:49 14-JAN-2021 19:43:45
13-JAN-2021 17:08:49 14-JAN-2021 19:42:49
13-JAN-2021 17:08:49 15-JAN-2021 20:08:49
13-JAN-2021 17:08:49 16-MAY-2021 17:08:49
13-JAN-2021 17:08:49 14-JAN-2021 05:43:45 |
可以对sysdate直接进行加减运算
TO_YMINTERVAL
将字符串转换为interval year to month类型
Syntax
ym_iso_format::=
查询系统之间之后一年两个月的时间
| SQL> select sysdate now,sysdate + to_yminterval('1-2') future from dual;
NOW FUTURE
----------------------------- -----------------------------
13-JAN-2021 17:16:35 13-MAR-2022 17:16:35 |
年份默认长度 2 位，月份不要超多 12
在查一下系统时间之前一年两个月的时间
| SQL> select sysdate now,sysdate + to_yminterval('-1-2') future from dual;
NOW FUTURE
----------------------------- -----------------------------
13-JAN-2021 17:17:09 13-NOV-2019 17:17:09 |
7564
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
