Oracle Timestamp类型计算

当并不需要microsecond信息的场景下，wait4friend一般不建议使用timestamp类型来存储日期信息（如生日）。不过一些遗留系统或者设计不严谨的系统中，总是有这样的数据存在。这样的设计会导致计算年龄等操作不太方便。

这两天就遇到一个例子，这个例子不单计算不方便，而且由于垃圾数据的存在还会在特定场景发生异常。

 

-- 表结构已经进行简化，只保留了ID和一个Timestamp字段，表中数据超过60W

SQL> desc tp1;
Name Type                        Nullable Default Comments 
---- --------------------------- -------- ------- -------- 
ID   NUMBER(19)                                            
BD   TIMESTAMP(6)                Y                         

 需求是统计年龄在50~100岁之间的总数。开发人员写的SQL使用了to_char/to_date的转换，该sql导致ORA-01877错误。

-- orginal sql
select count(id) from 
(
select sysdate - to_date(to_char(p.bd, 'yyyymmdd'),'yyyymmdd') as age_day , p.* from tp1 p
where p.bd is not null
)
where age_day between 365 * 50 and 365 * 100;

-- 执行过程中抛出异常
SQL> -- orginal sql
SQL> select count(id) from
  2  (
  3  select sysdate - to_date(to_char(p.bd, 'yyyymmdd'),'yyyymmdd') as age_day , p.* from tp1 p
  4  where p.bd is not null
  5  )
  6  where age_day between 365 * 50 and 365 * 100;
 
ORA-01877: 内部缓冲区的字符串太长

经过分析，把日期转换函数进行了修改，使用extract或者cast进行操作。这两个结果有一点差异，原因是extract方法是对year进行运算，而cast是对date进行运算得到天，然后把天转换成年导致数据失真。

-- modified sql 1
select count(1) from 
( 
select  extract(year from sysdate) - extract(year from p.bd) as age, p.* from tp1 p 
)
where age between 50 and 100;

-- modified sql 2
select count(1) from 
( 
select  sysdate - cast( p.bd as date) as age_day, p.* from tp1 p 
)
where age_day between 365 * 50 and 365 * 100;

 

到目前为止，已经得到了统计结果。不过wait4friend还想看看脏数据的问题，继续。

alter session set NLS_TIMESTAMP_FORMAT = 'yyyy/mm/dd hh24:mi:ssxff';

-- 查找了几条脏数据

-- wrong data

drop table tp2;
create table tp2 as
  select p.* from tp1 p where p.id in (7223,15271,3135525);


-- 使用dump查看数据内部存储
SQL> select id, dump(p.bd) as dump, cast(p.bd as date) as cast, p.bd from tp2 p;
 
      ID DUMP                                CAST        BD
-------- ----------------------------------- ----------- ------------------------------
    7223 Typ=180 Len=7: 120,133,9,1,1,1,1    2033-9-1    2033/09/01 00:00:00.000000
   15271 Typ=180 Len=7: 149,189,4,14,1,1,1   4989-4-14   4989/04/14 00:00:00.000000
 3135525 Typ=180 Len=7: 64,42,4,3,1,1,1      0000-0-0    3658/04/03 00:00:00.000000

在Oracle 时区(TimeZone ）-- DUMP信息这篇文章里面，wait4friend介绍了使用dump查看timestamp类型的方法。我们可以观察到，ID为7223和15271的两条数据是明显的脏数据，应该是插入的时候造成的。

 

奇怪的是ID为3135525的这条数据，内部存储格式和显示的3658/04/03对应不上。经过cast函数，结果为0000-0-0,明显是错误数据。目前造成这样数据的原因未知，惭愧。