如何提取oracle timestamp列一個範圍的資料

--建立測試表
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,確定時間範圍的起始點：即最先的時間點及最末的時間點