oracletimestamp转date（oracle timestamp 是什么类型数据）

oracle数据类型看起来非常简单，但用起来会发现有许多知识点，本文是我对ORACLE日期数据类型的一些整理，都是开发入门资料，与大家分享：注：由于INTERVAL及TIME ZONE实际用得比较少，所以本文内容未涉及这两个方面。1、常用日期型数据类型1.1、DATE这是ORACLE最常用的日期类型，它可以保存日期和时间，常用日期处理都可以采用这种类型。DATE表示的日期范围可以是公元前4712年1月1日至公元9999年12月31日date类型在数据库中的存储固定为7个字节，格式为：第1字节：世纪+100第2字节：年第3字节：月第4字节：天第5字节：小时+1第6字节：分+1第7字节：秒+11.2、TIMESTAMP(p)这也是ORACLE常用的日期类型，它与date的区别是不仅可以保 存日期和时间，还能保存小数秒，小数位数可以指定为0-9，默认为6位，所以最高精度可以到ns(纳秒)，数据库内部用7或者11个字节存储，如果精度为 0，则用7字节存储，与date类型功能相同，如果精度大于0则用11字节存储。格式为：第1字节：世纪+100第2字节：年第3字节：月第4字节：天第5字节：小时+1第6字节：分+1第7字节：秒+1第8-11字节：纳秒，采用4个字节存储，内部运算类型为整形注：TIMESTAMP日期类型如果与数值进行加减运算会自动转换为DATE型，也就是说小数秒会自动去除。1.3、DATE与TIMESTAMP类型内部存储验证复制代码1 create table T2 (3 C1 DATE,4 C2 TIMESTAMP(9)5 );67 insert into t(c1,c2) values(date’2010-2-12’,timestamp’2010-2-12 13:24:52.234123211’);8 insert into t(c1,c2) values(9 to_date(’2010-2-12 10:20:30’,’YYYY-MM-DD HH24:MI:SS’),10 to_timestamp(’2010-2-12 13:24:52.123456’,’YYYY-MM-DD HH24:MI:SS.FF6’)11 );1213 SQL》 select c1,dump(c1) c1_d,c2,dump(c2) c2_d from t;复制代码C1 C1_D C2 C2_D------------------------ ---------------------------------------- ---------------------------------------- -----------------------------------------------------2010-2-12 Typ=12 Len=7: 120,110,2,12,1,1,1 12-FEB-10 01.24.52.234123211 PM Typ=180 Len=11: 120,110,2,12,14,25,53,13,244,111,2032010-2-12 上午 10:20:30 Typ=12 Len=7: 120,110,2,12,11,21,31 12-FEB-10 01.24.52.123456000 PM Typ=180 Len=11: 120,110,2,12,14,25,53,7,91,202,0以下是为了测试是为了验证TIMESTAMP的小数位存储算法：1 SQL》 select c2,dump(c2,16) c2_d16 from t;C2 C2_D16---------------------------------------- --------------------------------------------------------------------------------12-FEB-10 01.24.52.234123211 PM Typ=180 Len=11: 78,6e,2,c,e,19,35,d,f4,6f,cb12-FEB-10 01.24.52.123456000 PM Typ=180 Len=11: 78,6e,2,c,e,19,35,7,5b,ca,0SQL》 select to_number(’0df46fcb’,’xxxxxxxx’) mydata1,to_number(’075bca00’,’xxxxxxxx’) mydata2 from dual;MYDATA1 MYDATA2---------- ----------234123211 1234560002、常见问题2.1、如何取当前时间sysdate--返回当前系统日期和时间，精确到秒systimestamp--返回当前系统日期和时间，精确到毫秒2.2、如何进行日期运算日期型数据可以与数值加减得到新的日期，加减数值单位为天sysdate+1--取明天的当前时间sysdate-1/24--取当前时间的前一个小时SQL》 select sysdate d1,sysdate+1 d2,sysdate-1/24 d3 from dual;D1 D2 D3------------------------ ------------------------ ------------------------2010-5-13 下午 10:55:16 2010-5-14 下午 10:55:16 2010-5-13 下午 09:55:162.3、如何求两个日期的间隔时间可以直接把两个日期相减，返回的单位为天，小时及分秒会换算成小数SQL》 select date’2012-01-01’-sysdate from dual;DATE’2012-01-01’-SYSDATE------------------------597.0460300925932.4、如何将日期转字符to_char(sysdate,’YYYY-MM-DD HH24:MI:SS’)2.5、如何将字符转日期to_date(’2010-02-24 15:01:54’,’YYYY-MM-DD HH24:MI:SS’)to_timestamp(’1999-12-01 11:00:00.123456’,’YYYY-MM-DD HH:MI:SS.FF6’)3、常用日期函数3.1、TO_CHAR(DATE,FORMATSTR)--格式化日期成字符SQL》 select to_char(sysdate,’YYYY-MM-DD HH24:MI:SS’) d1 from dual;D1------------------------2010-05-13 22:56:38TO_CHAR的其它用法示例复制代码1 SQL》 SELECT TO_CHAR(date ’2010-02-12’, ’D’) week_dayth,--周第几天(1-7)，星期天=1，星期一=2,星期二=3，星期三=4,星期四=5，星期五=6,星期六=72 TO_CHAR(date ’2010-02-12’, ’DD’) month_dayth,--月第几天3 TO_CHAR(date ’2010-02-12’, ’DDD’) year_dayth,--年第几天4 TO_CHAR(date ’2010-02-12’, ’DAY’) weekdayname,--英文星期名5 _CHAR(date ’2010-02-12’, ’w’) month_weekth,--月第几周(0-4)6 TO_CHAR(date ’2010-02-12’, ’ww’) year_weekth--年第几周(0-53)7 FROM DUAL;复制代码WEEK_DAYTH MONTH_DAYTH YEAR_DAYTH WEEKDAYNAME MONTH_WEEKTH YEAR_WEEKTH---------- ----------- ---------- ----------- ------------ -----------6 12 043 FRIDAY 2 073.2、TO_DATE(CHAR,FORMATSTR) --将字符转换成日期to_date(’2010-02-24 15:01:54’,’YYYY-MM-DD HH24:MI:SS’)格式备注：HH表示12小时进制，HH24表示采用24小时进制，MM表示月份，MI表示分钟。3.3、TRUNC(DATE)--返回DATE的日期部分，时间为0点0分0秒SQL》 select sysdate d1,trunc(sysdate) d2 from dual;D1 D2------------------------ ------------------------2010-5-13 下午 10:59:18 2010-5-133.4、EXTRACT(DATA FROM DATEVALUE)--返回DATE的某一部份内容如果DATEVALUE为DATE类型，则DATA可以是(YEAR、MONTH、DAY)如果DATEVALUE为TIMESTAMP类型，则DATA可以是(YEAR、MONTH,DAY、HOUR、MINUTE、SECOND)SQL》 select sysdate d1,EXTRACT(YEAR FROM sysdate) thisyear,EXTRACT(MINUTE FROM systimestamp) thism from dual;D1 THISYEAR THISM------------------------ ---------- ----------2010-5-13 下午 11:05:06 2010 53.5、ADD_MONTHS(DATE,MONTHS) --在DATE增加月份得到新日期ADD_MONTHS(sysdate,3) --当前日期加3个月ADD_MONTHS是一个比较有意思的函数，它会自动处理大小月及闰月，如下所示1 SQL》 select ADD_MONTHS(date ’2010-2-12’, 1),2 ADD_MONTHS(date ’2010-2-27’, 1),3 ADD_MONTHS(date ’2010-2-28’, 1),4 ADD_MONTHS(date ’2010-1-31’, 1)5 from dual6 ;ADD_MONTHS(DATE’2010-2-12’,1) ADD_MONTHS(DATE’2010-2-27’,1) ADD_MONTHS(DATE’2010-2-28’,1) ADD_MONTHS(DATE’2010-1-31’,1)----------------------------- ----------------------------- ----------------------------- -----------------------------2010-3-12 2010-3-27 2010-3-31 2010-2-283.6、LAST_DAY(DATE)--返回日期所在月份的最后一天日期SQL》 select LAST_DAY(date ’2010-2-12’) from dual;LAST_DAY(DATE’2010-2-12’)-------------------------2010-2-283.7、NEXT_DAY(DATE,CHAR) --从给定日期开始返回下个CHAR指定星期的日期SQL》 SELECT NEXT_DAY(date’2010-2-21’, ’MONDAY’) NEXTDAY1,NEXT_DAY(date’2010-2-22’, ’MONDAY’) NEXTDAY2 FROM DUAL;NEXTDAY1 NEXTDAY2----------- -----------2010-2-22 2010-3-1TO_YMINTERVAL(CHAR)--返回格式构成的时间间隔，一般用于日期加减运算3.8、TO_DSINTERVAL(CHAR)--返回格式构成的时间间隔，一般用于日期加减运算SQL》 select date’2010-2-12’+TO_YMINTERVAL(’01-02’) newdate from dual;NEWDATE------------------------------2011-4-123.9、NUMTOYMINTERVAL(N,CHAR) --返回CHAR中指定单位的时间间隔数值，一般用于日期加减运算char可以为YEAR,MONTH1 SQL》 select date ’2010-2-12’ + NUMTOYMINTERVAL(1, ’year’) newdate1,2 date ’2010-2-12’ + NUMTOYMINTERVAL(1, ’month’) newdate23 from dual;NEWDATE1 NEWDATE2----------- -----------2011-2-12 2010-3-123.10、NUMTODSINTERVAL(N,CHAR) --返回CHAR中指定单位的时间间隔数值，一般用于日期加减运算char可以为DAY,HOUR,MINUTE,SECOND1 SQL》 select date ’2010-2-12’ + NUMTODSINTERVAL(1, ’DAY’) newdate1,2 date ’2010-2-12’ + NUMTODSINTERVAL(1, ’HOUR’) newdate23 from dual;NEWDATE1 NEWDATE2----------- ------------------------2010-2-13 2010-2-12 上午 01:00:00

select to_date(to_char(systimestamp,’yyyy-mm-dd hh34:mi:ss’),’yyyy-mm-dd hh34:mi:ss’),

       to_char(systimestamp,’yyyy-mm-dd hh34:mi:ss’)

 from dual;

前面得到的是日期类型，后面得到的是字符类型日期。

SELECT systimestamp,       Trunc(systimestamp, ’hh’),  --时       Trunc(systimestamp, ’mi’),  --时分       To_Date(To_Char(systimestamp, ’yyyy/mm/dd hh34:mi:ss’),               ’yyyy/mm/dd hh34:mi:ss’)--时分秒  from dual;

使用oracle内置函数to_timestamp就可以了select to_timestamp(’1700’,’HH24MI’) from dual1、字符型转成timestamp代码如下:select to_timestamp(’01-10月-08 07.46.41.000000000 上午’,’dd-MON-yy hh:mi:ss.ff AM’)from dual;2、timestamp转成date型代码如下:select cast(to_timestamp(’01-10月-08 07.46.41.000000000 上午’,’dd-MON-yy hh:mi:ss.ff AM’) as date) timestamp_to_datefrom dual;3、date型转成timestamp代码如下:select cast(sysdate as timestamp) date_to_timestampfrom dual;4、获取timestamp格式的系统时间代码如下:select systimestamp from dual;5、两date的日期相减得出的是天数，而两timestamp的日期相减得出的是完整的年月日时分秒小数秒代码如下:select systimestamp-systimestamp from dual;select sysdate-sysdate from dual;注：所以，timestamp要算出两日期间隔了多少秒，要用函数转换一下。6、to_char函数支持date和timestamp，但是trunc却不支持TIMESTAMP数据类型。7、timestamp只支持秒的小数点后面六位。代码如下:select to_char(systimestamp, ’yyyymmdd hh34:mi:ssxff6’) FROM dual;注：ssxff6也可以为ssxff7、ssxff8、ssxff9等，但只到6有效。8、获取系统时间的语句：代码如下:SELECT sysdate,systimestamp,to_char(systimestamp, ’yyyymmdd hh34:mi:ssxff6’) FROM dual;

你可以转换成char类型的，用to_char(date,’yyyymmdd hh34:mi:ss’)来转换，然后可以进行排序，插入的话，没必要必须是date类型的，char类型的也可以插入

oracle中，日期转换函数有很多，常用命令如下：to_char()命令将时间戳转换为用户规定的日期格式，如：SELECTTO_CHAR(sysdate,’YYYY-MM-DDhh34:mi:ss’)FROMDUAL;语法：TO_CHAR(X)说明：将X按format格式转换成字符串。X可以是日期或者数字或时间戳，format是一个规定了X采用何种格式转换的格式字符串to_date()命令可以将一个日期格式的字符串转换成date类型数据，如：SELECTTO_DATE(’2014-05-0713:23:44’,’yyyy-mm-ddhh34:mi:ss’)FROMDUAL;语法：TO_DATE(C)说明：将符合format指定的特定日期格式的字符串C转换成DATE类型的数据TO_TIMESTAMP（）函数可以将一个时间格式的字符串转换成时间戳SELECTTO_TIMESTAMP(’2014-06-2012:11:11’,’YYYY-MM-DDHH24:MI:SS’)fromdual;语法：TO_TIMESTAMP(C)说明：将字符串C转换为一个时间戳数据类型---to_char()命令可以将时间戳转换成字符串：selectto_char(TO_TIMESTAMP(’2014-06-2012:11:11’,’YYYY-MM-DDHH24:MI:SS’),’YYYY-MM-DDHH24:MI:SS’)fromdual;当需要进行两个日期的比较时，不需要比较时间，可采用trunc（）命令来截取日期SQL》selectto_char(trunc(sysdate),’YYYY-MM-DDHH24:MI:SS’)fromdual;SQL》selectto_char(sysdate,’YYYY-MM-DDHH24:MI:SS’)fromdual;比较两句的运行结果，可以看到差别。

stmt.setDate(4, new java.sql.Date(project.getEndDate().getTime()))弄个jdk的api查看java.sql.Date的构造函数就知道怎么转了.

一、区别：

1）由于oracle中date类型只支持到秒，不支持到毫秒，所以to_date()不能取到毫秒。

2）如果要取到毫秒，oracle 9i以上版本，可以使用timestamp类型，

timestamp是date的扩展类型，能支持到毫秒，毫秒的显示精度是6位，不过有效位是3位，即最大值达到999，满1000ms就进为1s。

操作演示示例如下：

1、创建测试表，create table test_date(v_date varchar2(100));

2、插入测试数据，

insert into test_date values(’20190101 12:00:05’);

insert into test_date values(’20191205 00:05:22’);

insert into test_date values(’20191110 11:11:51’);

3、执行to_date语句sql，

select v_date, to_date(v_date,’yyyymmdd hh34:mi:ss’) v_date2 from TEST_DATE t

4、执行to_timestamp语句sql，

select v_date, to_timestamp(v_date,’yyyymmdd hh34:mi:ss’) v_date2 from TEST_DATE t；

1、Date类型转换为String类型：SimpleDateFormat sdf = new SimpleDateFormat(“yyyy年MM月dd日 HH:mm:ss“);SimpleDateFormat sdf1 = new SimpleDateFormat(“yyyyMMdd“);String str = sdf.format(date1);String str1 =sdf1.format(date1);System.out.println(str);System.out.println(str1);说明：sdf和sdf1只是两个不懂的格式化类型的定义，类型可以自由定义2、String与Timestamp类型转换：1、String转换为Timestamp类型：String str3 = “2011-02-02 12:12:12“;Timestamp.valueOf(str3);System.out.println(Timestamp.valueOf(str3));

SQL》 SELECT  2    TO_TIMESTAMP(TO_CHAR(SYSDATE, ’YYYY-MM-DD HH24:MI:SS’),  ’YYYY-MM-DD HH24:MI:SS’) A  3  FROM   4    dual;A---------------------------------------------------------------------------21-11月-13 09.01.09.000000000 上午

这个效果?