将日期转换为秒，创建间隔分区-凯发app官方网站

drop table everyday_interval;
create table everyday_interval
(
time_seconds number,
car_no varchar2(20),
addr varchar2(200),
birthday date
)
partition by range( time_seconds )
interval (86400) store in (users)
(
partition part1 values less than(1659369600) -- 2022-8-1
);
insert into everyday_interval values(1659369600,'1111','aaaaaa',sysdate);
insert into everyday_interval values(1659542400,'2222','bbbbbb',sysdate-5);
insert into everyday_interval values(1661961600,'3333','cccccc',sysdate-1);
insert into everyday_interval values(1661961600,'5555','hhhhh',sysdate-6);
commit;
select to_number(to_date('2022-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') - to_date('1970-01-01 8:0:0', 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60 p0801 from dual;
select to_number(to_date('2022-08-02 00:00:00', 'yyyy-mm-dd hh24:mi:ss') - to_date('1970-01-01 8:0:0', 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60 p0802 from dual;
select to_number(to_date('2022-08-03 00:00:00', 'yyyy-mm-dd hh24:mi:ss') - to_date('1970-01-01 8:0:0', 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60 p0803 from dual;
select to_number(to_date('2022-08-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss') - to_date('1970-01-01 8:0:0', 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60 p0804 from dual;
select to_number(to_date('2022-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') - to_date('1970-01-01 8:0:0', 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60 p0901 from dual;
日期转秒
select to_number(sysdate - to_date('1970-01-01 8:0:0', 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60 from dual;
秒转日期
select to_date('1970-1-1 8:0:0','yyyy-mm-dd hh24:mi:ss')(1659369600/(3600*24)) from dual;

exec dbms_stats.gather_table_stats('a','everyday_interval')

set lin 200
col tablespace_name for a12
col table_name for a20
col partition_name for a20
col high_value for a20
col partition_position for 999
select table_name,partition_name,high_value,partition_position,tablespace_name,num_rows from dba_tab_partitions where table_name=upper('everyday_interval');

阅读(366) | 评论(0) | 转发(0) |

上一篇：间隔分区：每日一个示例

下一篇：删除1000万条数据会产生多少归档？

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们