Oracle 语法01

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 下午
--转换成正常日期格式（yyyy-mm-dd hh24:mi:ss）--
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;