Oracle 特殊时间处理
1.时间处理
日期格式处理01
SELECT * FROM ksj_base_total a where a. end_gxsj is not null
and a. total<> 0 and to_date( a. end_gxsj, 'dd-mon-yy' ) > trunc( sysdate- 1 ) ;
1. oracle 处理
使用to_date: to_date( '01-3月 -20' , 'dd-mon-yy' , 'nls_date_language=' 'Simplified Chinese'' ')
2. 例:
select to_date( '01-3月 -20' , 'dd-mon-yy' , 'nls_date_language=' 'Simplified Chinese'' ') from dual;
SELECT to_date( a. end_gxsj, 'dd-mon-yy' , 'nls_date_language=' 'Simplified Chinese'' ') FROM ksj_base_total a;
SELECT to_date( '16-3月 -21' , 'dd-mon-yy' , 'nls_date_language=' 'Simplified Chinese'' ') FROM dual;
11 - 11 月- 20 02.54 .26 .000000 下午
select to_char( cast( cast( '26-9月 -17 06.46.00.000000000 下午' as timestamp ) as date ) , 'yyyy-mm-dd hh24:mi:ss' ) dateStr from dual;
select to_char( cast( cast( '11-11月-20 02.54.26.000000 下午' as timestamp ) as date ) , 'yyyy-mm-dd hh24:mi:ss' ) dateStr from dual;
select to_char( cast( cast( '11-11月-20 02.54.26.000000 下午' as timestamp ) as date ) , 'yyyy-mm-dd' ) dateStr from dual;
select to_timestamp( '2017-09-26 18:46:00' , 'yyyy-mm-dd hh24:mi:ss' ) from dual;
SELECT length( a. end_gxsj) , count ( 1 ) FROM ksj_base_total a group by length( a. end_gxsj) ;
SELECT a. end_gxsj FROM ksj_base_total a where length( a. end_gxsj) = '28' ;
SELECT length( a. end_gxsj) , a. end_gxsj, ( case when length( a. end_gxsj) = '10' then a. end_gxsj
when length( a. end_gxsj) = '9' then to_char( to_date( a. end_gxsj, 'dd-mon-yy' , 'nls_date_language=' 'Simplified Chinese' '' ) , 'yyyy-mm-dd' )
when length( a. end_gxsj) = '19' then substr( a. end_gxsj, 1 , 10 )
when length( a. end_gxsj) = '28' then to_char( cast( cast( a. end_gxsj as timestamp ) as date ) , 'yyyy-mm-dd' )
else '0' end ) as format_time
FROM ksj_base_total a where length( a. end_gxsj) = '28' ;
SELECT * FROM (
SELECT length( a. end_gxsj) as s_len, a. end_gxsj as s_gxsj, ( case when length( a. end_gxsj) = '10' then a. end_gxsj
when length( a. end_gxsj) = '9' then to_char( to_date( a. end_gxsj, 'dd-mon-yy' , 'nls_date_language=' 'Simplified Chinese' '' ) , 'yyyy-mm-dd' )
when length( a. end_gxsj) = '19' then substr( a. end_gxsj, 1 , 10 )
when length( a. end_gxsj) = '28' then to_char( cast( cast( a. end_gxsj as timestamp ) as date ) , 'yyyy-mm-dd' )
else '0' end ) as format_time, a. *
FROM ksj_base_total a where a. end_gxsj is not null
and a. total<> 0 ) a1 where to_date( a1. FORMAT_TIME, 'yyyy-mm-dd' ) >= trunc( sysdate- 1 ) ;
SELECT * FROM (
SELECT length( a. end_gxsj) as s_len, a. end_gxsj as s_gxsj, ( case when length( a. end_gxsj) = '10' then a. end_gxsj
when length( a. end_gxsj) = '9' then to_char( to_date( a. end_gxsj, 'dd-mon-yy' , 'nls_date_language=' 'Simplified Chinese' '' ) , 'yyyy-mm-dd' )
when length( a. end_gxsj) = '19' then substr( a. end_gxsj, 1 , 10 )
when length( a. end_gxsj) = '28' then to_char( cast( cast( a. end_gxsj as timestamp ) as date ) , 'yyyy-mm-dd' )
else null end ) as format_time, a. *
FROM ksj_base_total a where a. end_gxsj is not null
and a. total<> 0 ) a1 where to_date( a1. FORMAT_TIME, 'yyyy-mm-dd' ) >= trunc( sysdate- 1 ) ;
2.数据统计
Oracle 数据统计
SELECT * FROM (
SELECT a. tablespace_name "表空间名" ,
total / ( 1024 * 1024 * 1024 ) "表空间大小(G)" ,
free / ( 1024 * 1024 * 1024 ) "表空间剩余大小(G)" ,
( total - free) / ( 1024 * 1024 * 1024 ) "表空间使用大小(G)" ,
round ( ( total - free) / total, 4 ) * 100 "使用率 %"
FROM ( SELECT tablespace_name, SUM ( bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
( SELECT tablespace_name, SUM ( bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a. tablespace_name = b. tablespace_name) order by "使用率 %" desc ;