Question

oracle timestamp类型数据怎么转换成数值

Answer 1

简单的方法就是把timestamp直接换成scn
SQL> col timestp for a35
SQL> select scn_to_timestamp(current_scn) timestp,current_scn from (select current_scn from v$database);

TIMESTP CURRENT_SCN
----------------------------------- -----------
12-2月 -15 09.01.51.000000000 上午 2719548

Answer 2

比较好的办法可能只有截取字符串了
SELECT substr((use_time),instr((use_time),' ')+10) m_seconds,
substr((use_time),instr((use_time),' ')+7,2) seconds,
substr((use_time),instr((use_time),' ')+4,2) minutes,
substr((use_time1),instr((use_time),' ')+1,2) hours,
trunc(to_number(substr((use_time),1,instr(use_time,' ')))) days,
trunc(to_number(substr((use_time),1,instr(use_time,' ')))/7) weeks
FROM dual;

Answer 3

先to_char
再 to_number