--创建测试表
SQL> create table t_timestamp(a timestamp);
 
Table created

--插入几条测试数据
SQL> insert into t_timestamp values(systimestamp-1);
 
1 row inserted
 
SQL> insert into t_timestamp values(systimestamp);
 
1 row inserted
 
SQL> insert into t_timestamp values(systimestamp+1);
 
1 row inserted
 
SQL> commit;
 
Commit complete
--查看测试数据
SQL> select * from t_timestamp;
 
A
--------------------------------------------------------------------------------
27-FEB-13 04.34.54.000000 PM
28-FEB-13 04.35.00.641000 PM
01-MAR-13 04.35.04.000000 PM
--查看与timestamp相关初始化参数配置
SQL> show parameter timestamp
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_timestamp_format                 string     
nls_timestamp_tz_format              string     
--配置当前会话的timestamp格式
SQL> alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss.ff';
 
Session altered
--查询测试表的timestamp格式是否如愿显示
SQL> select * from t_timestamp;
 
A
--------------------------------------------------------------------------------
2013-02-27 16:34:54.000000
2013-02-28 16:35:00.641000
2013-03-01 16:35:04.000000
 
--trunc可加工timestamp,经加工的数据类型为date
SQL> select trunc(a),a from t_timestamp;
 
TRUNC(A)    A
----------- --------------------------------------------------------------------------------
2013/2/27   2013-02-27 16:34:54.000000
2013/2/28   2013-02-28 16:35:00.641000
2013/3/1    2013-03-01 16:35:04.000000
 
--对trunc后的数据以to_char格式转换
SQL> select trunc(a),to_char(trunc(a),'yyyy-mm-dd'),a from t_timestamp;
 
TRUNC(A)    TO_CHAR(TRUNC(A),'YYYY-MM-DD') A
----------- ------------------------------ --------------------------------------------------------------------------------
2013/2/27   2013-02-27                     2013-02-27 16:34:54.000000
2013/2/28   2013-02-28                     2013-02-28 16:35:00.641000
2013/3/1    2013-03-01                     2013-03-01 16:35:04.000000
 
--使用to_timestamp及接接字符串提取2013-02-27及2013-02-28两天所有的数据
SQL> select a from t_timestamp where a between to_timestamp('2013-02-27'||'00:00:00.000000','yyyy-mm-dd hh24:mi:ss.ff') and to_timestamp('2013-02-28'||'23:59:59.000000','yyyy-mm-dd hh24:mi:ss.ff');
 
A
--------------------------------------------------------------------------------
2013-02-27 16:34:54.000000
2013-02-28 16:35:00.641000

--显式配置nls_timestamp_format后to_timestamp不用指定格式字符串
SQL> select a from t_timestamp where a between to_timestamp('2013-02-27'||'00:00:00.000000') and to_timestamp('2013-02-28'||'23:59:59.000000');
 
A
--------------------------------------------------------------------------------
2013-02-27 16:34:54.000000
2013-02-28 16:35:00.641000

小结：

        1，to_timestamp使用

        2,拼接字符串

        3,或与不或配置nls_timestamp_format（非必选项）

        4,确定时间范围的起始点：即最先的时间点及最末的时间点