回覆列表
  • 1 # 使用者2408710553568

    --建立測試表SQL>createtablet_timestamp(atimestamp);Tablecreated--插入幾條測試資料SQL>insertintot_timestampvalues(systimestamp-1);1rowinsertedSQL>insertintot_timestampvalues(systimestamp);1rowinsertedSQL>insertintot_timestampvalues(systimestamp+1);1rowinsertedSQL>commit;Commitcomplete--檢視測試資料SQL>select*fromt_timestamp;A--------------------------------------------------------------------------------27-FEB-1304.34.54.000000PM28-FEB-1304.35.00.641000PM01-MAR-1304.35.04.000000PM--檢視與timestamp相關初始化引數配置SQL>showparametertimestampNAMETYPEVALUE-----------------------------------------------------------------------------nls_timestamp_formatstringnls_timestamp_tz_formatstring--配置當前會話的timestamp格式SQL>altersessionsetnls_timestamp_format="yyyy-mm-ddhh24:mi:ss.ff";Sessionaltered--查詢測試表的timestamp格式是否如願顯示SQL>select*fromt_timestamp;A--------------------------------------------------------------------------------2013-02-2716:34:54.0000002013-02-2816:35:00.6410002013-03-0116:35:04.000000--trunc可加工timestamp,經加工的資料型別為dateSQL>selecttrunc(a),afromt_timestamp;TRUNC(A)A-------------------------------------------------------------------------------------------2013/2/272013-02-2716:34:54.0000002013/2/282013-02-2816:35:00.6410002013/3/12013-03-0116:35:04.000000--對trunc後的資料以to_char格式轉換SQL>selecttrunc(a),to_char(trunc(a),"yyyy-mm-dd"),afromt_timestamp;TRUNC(A)TO_CHAR(TRUNC(A),"YYYY-MM-DD")A-------------------------------------------------------------------------------------------------------------------------2013/2/272013-02-272013-02-2716:34:54.0000002013/2/282013-02-282013-02-2816:35:00.6410002013/3/12013-03-012013-03-0116:35:04.000000--使用to_timestamp及接接字串提取2013-02-27及2013-02-28兩天所有的資料SQL>selectafromt_timestampwhereabetweento_timestamp("2013-02-27"||"00:00:00.000000","yyyy-mm-ddhh24:mi:ss.ff")andto_timestamp("2013-02-28"||"23:59:59.000000","yyyy-mm-ddhh24:mi:ss.ff");A--------------------------------------------------------------------------------2013-02-2716:34:54.0000002013-02-2816:35:00.641000--顯式配置nls_timestamp_format後to_timestamp不用指定格式字串SQL>selectafromt_timestampwhereabetweento_timestamp("2013-02-27"||"00:00:00.000000")andto_timestamp("2013-02-28"||"23:59:59.000000");A--------------------------------------------------------------------------------2013-02-2716:34:54.0000002013-02-2816:35:00.641000小結:1,to_timestamp使用2,拼接字串3,或與不或配置nls_timestamp_format(非必選項)4,確定時間範圍的起始點:即最先的時間點及最末的時間點

  • 中秋節和大豐收的關聯?
  • 人到底該怎麼活著?