系统时间:
SELECT SYSTIMESTAMP FROM dual;----timestamp类型时间
SELECT SYSDATE FROM dual;----------date类型日期
timestamp转换为char:
SELECT to_char(SYSTIMESTAMP,'yyyy-mm-dd HH24:mi:ss:ff') FROM dual;----24小时制
SELECT to_char(SYSTIMESTAMP,'yyyy-mm-dd HH:mi:ss:ff am') FROM dual;----12小时制
char转换为timestamp:
select to_timestamp('2015-6-2 12:34:56.789', 'yyyy-mm-dd hh24:mi:ss.ff') from dual;
timestamp转换为date:
SELECT CAST(SYSTIMESTAMP AS DATE) AS cur_time FROM dual; ----cast函数
或者
SELECT SYSTIMESTAMP+0 FROM dual;----自动转换
date转换为timestamp:
SELECT CAST (SYSDATE AS TIMESTAMP) cur_time FROM dual; ----cast函数
两个timestamp时间类型相减:
select to_char(SYSTIMESTAMP,'yyyy-mm-dd HH24:mi:ss:ff') AS time_1,'2015-06-02 12:34:56.789' AS time_2,(to_timestamp('2015-6-2 12:34:56.789', 'syyyy-mm-dd hh24:mi:ss.ff')-SYSTIMESTAMP) AS time_1_2 from dual;
结果:
time_1 time_2 time_2_1
2015-06-02 13:26:50:539951 2015-06-02 12:34:56.789 -000000000 00:51:53.750951000
可见,两个timestamp相减之后,结果是还是时间,具体到 天,小时,分钟等等;
两个date日期类型的相减:
SELECT SYSDATE AS date_1,to_date('2015-6-2 12:34:56','yyyy-mm-dd hh24:mi:ss') AS date_2, (SYSDATE- to_date('2015-6-2 12:34:56','yyyy-mm-dd hh24:mi:ss')) AS date_1_2 FROM dual;
结果:
date_1 date_2 date_1_2
2015/6/2 13:35:29 2015/6/2 12:34:56 0.0420486111111111
可见,两个date类型的相减,是天数
如果相减之后再乘以 *24*3600,就是秒数了。
因此,对于timestamp类型的数值相减,可以先转换为date类型,再进行计算,即可转换为秒数:
select to_char(SYSTIMESTAMP,'yyyy-mm-dd HH24:mi:ss:ff') AS time_1,'2015-06-02 12:34:56.789' AS time_2
,'距现在'||((SYSTIMESTAMP+0)-(to_timestamp('2015-6-2 12:34:56.789', 'syyyy-mm-dd hh24:mi:ss.ff')+0))*24*3600||'秒' AS time_1_2 from dual;
结果:
time_1 time_2 time_1_2
2015-06-02 13:45:53:751646 2015-06-02 12:34:56.789 距现在4257秒
PS:
1:CAST():
在date转换为timestamp时,涉及到一个函数 cast();
此函数作用:进行数据类型转换
举例:
SELECT CAST('1111' AS VARCHAR2(12)) AS tt FROM dual;
SELECT CAST('1111' AS NUMBER(22)) AS tt FROM dual;
------------也可以转换一个集合等。