to_dsinterval与timestamp学习记录
CREATE TABLE SCOTT.TIME_GROUP
(
ARRAY_ID NUMBER,
TIMETAG TIMESTAMP(9)
)；
SQL> select * from time_group;
ARRAY_ID TIMETAG
---------- --------------------------------------------------------------------------------
1 2011-10-17 11:06:05.123456789
1 2011-10-17 11:06:06.123456789
1 2011-10-17 11:06:07.123456789
1 2011-10-17 11:08:05.123456789
1 2011-10-17 11:08:07.123456789
1 2011-10-17 11:10:05.123456789
2 2011-10-17 11:06:05.123456789
2 2011-10-17 11:06:06.123456789
2 2011-10-17 11:06:07.123456789
2 2011-10-17 11:08:05.123456789
2 2011-10-17 11:08:07.123456789
2 2011-10-17 11:10:05.123456789
12 rows selected
SQL> select to_dsinterval('00 00:01:00' ) from dual;
TO_DSINTERVAL('0000:01:00')
---------------------------------------
+000000000 00:01:00.000000000
--加上5分钟
SQL> select to_dsinterval('00 00:01:00' )*5 from dual;
TO_DSINTERVAL('0000:01:00')*5
---------------------------------------
+000000000 00:05:00.000000000
--加上60分钟，会自动在小时位上加1
SQL> select to_dsinterval('00 00:01:00' )*60 from dual;
TO_DSINTERVAL('0000:01:00')*60
---------------------------------------
+000000000 01:00:00.000000000
--加上121分钟
SQL> select to_dsinterval('00 00:01:00' )*121 from dual;
TO_DSINTERVAL('0000:01:00')*12
---------------------------------------
+000000000 02:01:00.000000000
--加上1339分钟
SQL> select to_dsinterval('00 00:01:00' )*1439 from dual;
TO_DSINTERVAL('0000:01:00')*14
---------------------------------------
+000000000 23:59:00.000000000
--加上1440分钟，会在天数位上加1
SQL> select to_dsinterval('00 00:01:00' )*1440 from dual;
TO_DSINTERVAL('0000:01:00')*14
---------------------------------------
+000000001 00:00:00.000000000
--加上2880分钟，会在天数为上加2
SQL> select to_dsinterval('00 00:01:00' )*2880 from dual;
TO_DSINTERVAL('0000:01:00')*28
---------------------------------------
+000000002 00:00:00.000000000
SQL> select timetag ,array_id,
2 timetag -lag(timetag) over(partition by array_id order by timetag asc) t_interval
3 from time_group
4 ;
TIMETAG ARRAY_ID T_INTERVAL
-------------------------------------------------------------------------------- ---------- ---------------------------------------
2011-10-17 11:06:05.123456789 1
2011-10-17 11:06:06.123456789 1 +000000000 00:00:01.000000000
2011-10-17 11:06:07.123456789 1 +000000000 00:00:01.000000000
2011-10-17 11:08:05.123456789 1 +000000000 00:01:58.000000000
2011-10-17 11:08:07.123456789 1 +000000000 00:00:02.000000000
2011-10-17 11:10:05.123456789 1 +000000000 00:01:58.000000000
2011-10-17 11:06:05.123456789 2
2011-10-17 11:06:06.123456789 2 +000000000 00:00:01.000000000
2011-10-17 11:06:07.123456789 2 +000000000 00:00:01.000000000
2011-10-17 11:08:05.123456789 2 +000000000 00:01:58.000000000
2011-10-17 11:08:07.123456789 2 +000000000 00:00:02.000000000
2011-10-17 11:10:05.123456789 2 +000000000 00:01:58.000000000
12 rows selected
SQL> select timetag ,array_id,
2 (timetag -lag(timetag) over(partition by array_id order by timetag asc))*86400
3 from time_group
4 ;
TIMETAG ARRAY_ID (TIMETAG-LAG(TIMETAG)OVER(PART
-------------------------------------------------------------------------------- ---------- ---------------------------------------
2011-10-17 11:06:05.123456789 1
2011-10-17 11:06:06.123456789 1 +000000001 00:00:00.000000000
2011-10-17 11:06:07.123456789 1 +000000001 00:00:00.000000000
2011-10-17 11:08:05.123456789 1 +000000118 00:00:00.000000000
2011-10-17 11:08:07.123456789 1 +000000002 00:00:00.000000000
2011-10-17 11:10:05.123456789 1 +000000118 00:00:00.000000000
2011-10-17 11:06:05.123456789 2
2011-10-17 11:06:06.123456789 2 +000000001 00:00:00.000000000
2011-10-17 11:06:07.123456789 2 +000000001 00:00:00.000000000
2011-10-17 11:08:05.123456789 2 +000000118 00:00:00.000000000
2011-10-17 11:08:07.123456789 2 +000000002 00:00:00.000000000
2011-10-17 11:10:05.123456789 2 +000000118 00:00:00.000000000
SQL> select timetag ,array_id,
2 to_date(substr(to_char(timetag,'yyyy-mm-dd hh24:mi:ss.ff9'),1,19),'yyyy-mm-dd hh24:mi:ss') -
3 lag(to_date(substr(to_char(timetag,'yyyy-mm-dd hh24:mi:ss.ff9'),1,19),'yyyy-mm-dd hh24:mi:ss'))
4 over(partition by array_id order by timetag asc)
5 from time_group
6 ;
TIMETAG ARRAY_ID TO_DATE(SUBSTR(TO_CHAR(TIMETAG
-------------------------------------------------------------------------------- ---------- ------------------------------
2011-10-17 11:06:05.123456789 1
2011-10-17 11:06:06.123456789 1 1.15740740740741E-5
2011-10-17 11:06:07.123456789 1 1.15740740740741E-5
2011-10-17 11:08:05.123456789 1 0.00136574074074074
2011-10-17 11:08:07.123456789 1 2.31481481481481E-5
2011-10-17 11:10:05.123456789 1 0.00136574074074074
2011-10-17 11:06:05.123456789 2
2011-10-17 11:06:06.123456789 2 1.15740740740741E-5
2011-10-17 11:06:07.123456789 2 1.15740740740741E-5
2011-10-17 11:08:05.123456789 2 0.00136574074074074
2011-10-17 11:08:07.123456789 2 2.31481481481481E-5
2011-10-17 11:10:05.123456789 2 0.00136574074074074
12 rows selected
SQL> select timetag ,array_id,
2 (to_date(substr(to_char(timetag,'yyyy-mm-dd hh24:mi:ss.ff9'),1,19),'yyyy-mm-dd hh24:mi:ss') -
3 lag(to_date(substr(to_char(timetag,'yyyy-mm-dd hh24:mi:ss.ff9'),1,19),'yyyy-mm-dd hh24:mi:ss'))
4 over(partition by array_id order by timetag asc))*86400
5 from time_group
6 ;
TIMETAG ARRAY_ID (TO_DATE(SUBSTR(TO_CHAR(TIMETA
-------------------------------------------------------------------------------- ---------- ------------------------------
2011-10-17 11:06:05.123456789 1
2011-10-17 11:06:06.123456789 1 1
2011-10-17 11:06:07.123456789 1 1
2011-10-17 11:08:05.123456789 1 118
2011-10-17 11:08:07.123456789 1 2
2011-10-17 11:10:05.123456789 1 118
2011-10-17 11:06:05.123456789 2
2011-10-17 11:06:06.123456789 2 1
2011-10-17 11:06:07.123456789 2 1
2011-10-17 11:08:05.123456789 2 118
2011-10-17 11:08:07.123456789 2 2
2011-10-17 11:10:05.123456789 2 118
12 rows selected
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24802932/viewspace-711871/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
perl文本文件处理
下一篇：
利用正则表达式进行字段拆分
请登录后发表评论
登录
全部评论
