sqlserver 与 oracle 与 java时间数据类型

最新推荐文章于 2025-06-07 19:32:05 发布

wz_TXWY

最新推荐文章于 2025-06-07 19:32:05 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库

本文链接：https://blog.youkuaiyun.com/wz_TXWY/article/details/80118849

数据库专栏收录该内容

4 篇文章

订阅专栏

本文详细介绍了Java和SQL中的日期时间类型，包括Java中的Date、Time、Timestamp类及其相互转换方法，以及SQL Server和Oracle数据库中的日期时间类型如DATE、TIME、DATETIME等的使用和特点。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Java中的时间类型

　　java.sql包下给出三个与数据库相关的日期时间类型，分别是：

　　　　Date：表示日期，只有年月日，没有时分秒。会丢失时间；

　　　　Time：表示时间，只有时分秒，没有年月日。会丢失日期；

　　　　Timestamp：表示时间戳，有年月日时分秒，以及毫秒。

　　注：这三个类都是java.util.Date的子类。

时间类型相互转换

　　把数据库的三种时间类型赋给java.util.Date，基本不用转换，因为这是把子类对象给父类的引用，不需要转换。

　　　　java.sql.Date date = …

　　　　java.util.Date d = date;

　　　　java.sql.Time time = …

　　　　java.util.Date d = time;

　　　　java.sql.Timestamp timestamp = …

　　　　java.util.Date d = timestamp;

　　当需要把java.util.Date转换成数据库的三种时间类型时，这就不能直接赋值了，这需要使用数据库三种时间类型的构造器。java.sql包下的Date、Time、TimeStamp三个类的构造器都需要一个long类型的参数，表示毫秒值（时间戳）。创建这三个类型的对象，只需要有毫秒值即可。我们知道java.util.Date有getTime()方法可以获取毫秒值（时间戳），那么这个转换也就不是什么问题了。

　　　　java.utl.Date d = new java.util.Date();

　　　　java.sql.Date date = new java.sql.Date(d.getTime());　　//会丢失时分秒

　　　　Time time = new Time(d.getTime());　　　　　　　　　　 //会丢失年月日

　　　　Timestamp timestamp = new Timestamp(d.getTime());

sqlserver ：

日期时间类型

date：日期

time：时间

datetime：时间+日期，有效时间范围1753/1/1~9999/12/31,精确到3.33ms。

datetime2[(n)]：是精度可以改变的时间+日期，当然是指改变时间的精度啦

smalldatetime：结果类似于datetime，但是还是有区别的，smalldatetime的有效时间范围1900/1/1~2079/6/6，精确到分钟。

datetimeoffset

      DATE
     
      2017-09-19
     
      TIME
     
      09:05:09.2670000
     
      DATETIME
     
      9 19 2017  9:05AM
     
      DATETIME2可以设置精度
     
      DATETIME2
     
      2017-09-19 09:05:09.2670000
     
      DATETIME2(5)
     
      2017-09-19 09:05:09.26700
     
      SMALLDATETIME
     
      9  19 2017  9:05AM
     
      DATETIMEOFFSET
     
      2017-09-19 09:05:09.2670000 +00:00

输出上面的源码

      DECLARE 
      @dt 
      AS 
      DATE 
     
      SET 
      @dt=GETDATE() 
     
      PRINT 
      'DATE'
      ; 
     
      PRINT @dt; 
     
      /*2017-09-19*/ 
     
      DECLARE 
      @dt2 
      AS 
      TIME 
     
      SET 
      @dt2=GETDATE() 
     
      PRINT 
      'TIME'
      ; 
     
      PRINT @dt2; 
     
      /*08:49:53.7830000*/ 
     
      DECLARE 
      @dt3 
      AS 
      DATETIME 
     
      SET 
      @dt3=GETDATE() 
     
      PRINT 
      'DATETIME'
      ; 
     
      PRINT @dt3; 
     
      /*9  19 2017  8:50AM*/ 
     
      PRINT 
      'DATETIME2可以设置精度'
      ; 
     
      DECLARE 
      @dt4 
      AS 
      DATETIME2 
     
      SET 
      @dt4=GETDATE() 
     
      PRINT 
      'DATETIME2'
      ; 
     
      PRINT @dt4; 
     
      /*2017-09-19 09:16:03.6300000*/ 
     
      DECLARE 
      @dt5 
      AS 
      DATETIME2(5) 
     
      SET 
      @dt5=GETDATE() 
     
      PRINT 
      'DATETIME2(5)'
      ; 
     
      PRINT @dt5; 
     
      /*2017-09-19 08:53:03.63000*/ 
     
      DECLARE 
      @dt7 
      AS 
      SMALLDATETIME 
     
      SET 
      @dt7=GETDATE() 
     
      PRINT 
      'SMALLDATETIME'
      ; 
     
      PRINT @dt7; 
     
      /*9  19 2017  8:55AM*/ 
     
      DECLARE 
      @dt8 
      AS 
      DATETIMEOFFSET 
     
      SET 
      @dt8=GETDATE() 
     
      PRINT 
      'DATETIMEOFFSET'
      ; 
     
      PRINT @dt8; 
     
      /*2017-09-19 08:55:54.5770000 +00:00*/

直接从每个类型定义就可以看出区别：
date、time、datetime三个类型直接从意思上可以理解，date是日期，time是时间，datetime是日期跟时间。“（）”里面的数字是表示长度。

datetime2数据类型，类似于之前的datetime类型，不过其精度比较高，可以精确到小数点后面7位（100ns）；

datetimeoffset数据类型，加入了时区偏移量部分，时区偏移量表示为 [+|-] HH:MM。 HH 是范围从 00 到 14 的 2 位数，表示时区偏移量的小时数。 MM 是范围从 00 到 59 的 2 位数，表示时区偏移量的附加分钟数。时间格式支持到最小 100 毫微秒。必需的 + 或 - 符号指示在 UTC（通用协调时间或格林尼治标准时间）中是加上还是减去时区偏移量以获取本地时间。使用示例：

'2008-08-08 08:08:08.0 +8:00'

timestamp

timestamp 这种数据类型表现自动生成的二进制数，确保这些数在数据库中是唯一的。timestamp 一般用作给表行加版本戳的机制。存储大小为 8 字节。

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字节：秒+1

1.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 T
 2 (
 3   C1 DATE,
 4   C2 TIMESTAMP(9)
 5 );
 6 
 7 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 );
12 
13 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,203
2010-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,cb
12-FEB-10 01.24.52.123456000 PM          Typ=180 Len=11: 78,6e,2,c,e,19,35,7,5b,ca,0

SQL> select to_number('0df46fcb','xxxxxxxx') mydata1,to_number('075bca00','xxxxxxxx') mydata2 from dual;

MYDATA1 MYDATA2
---------- ----------
234123211 123456000

2、常见问题
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:16

2.3、如何求两个日期的间隔时间
可以直接把两个日期相减，返回的单位为天，小时及分秒会换算成小数

SQL> select date'2012-01-01'-sysdate from dual;

DATE'2012-01-01'-SYSDATE
------------------------
597.046030092593

2.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:38

TO_CHAR的其它用法示例

1 SQL> SELECT TO_CHAR(date '2010-02-12', 'D') week_dayth,--周第几天(1-7)，星期天=1，星期一=2,星期二=3，星期三=4,星期四=5，星期五=6,星期六=7
2 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 07

3.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-13

3.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 5
3.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 dual
6   ;

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-28

3.6、LAST_DAY(DATE)--返回日期所在月份的最后一天日期

SQL> select LAST_DAY(date '2010-2-12') from dual;

LAST_DAY(DATE'2010-2-12')
-------------------------
2010-2-28

3.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-1

TO_YMINTERVAL(CHAR)--返回[年-月]格式构成的时间间隔，一般用于日期加减运算

3.8、TO_DSINTERVAL(CHAR)--返回[天时:分:秒]格式构成的时间间隔，一般用于日期加减运算

SQL> select date'2010-2-12'+TO_YMINTERVAL('01-02') newdate from dual;

NEWDATE
------------------------------
2011-4-12

3.9、NUMTOYMINTERVAL(N,CHAR) --返回CHAR中指定单位的时间间隔数值，一般用于日期加减运算
char可以为YEAR,MONTH

1 SQL> select date '2010-2-12' + NUMTOYMINTERVAL(1, 'year') newdate1,
2 date '2010-2-12' + NUMTOYMINTERVAL(1, 'month') newdate2
3 from dual;

NEWDATE1 NEWDATE2
----------- -----------
2011-2-12 2010-3-12

3.10、NUMTODSINTERVAL(N,CHAR) --返回CHAR中指定单位的时间间隔数值，一般用于日期加减运算
char可以为DAY,HOUR,MINUTE,SECOND

1 SQL> select date '2010-2-12' + NUMTODSINTERVAL(1, 'DAY') newdate1,
2 date '2010-2-12' + NUMTODSINTERVAL(1, 'HOUR') newdate2
3 from dual;

NEWDATE1 NEWDATE2
----------- ------------------------
2010-2-13 2010-2-12 上午 01:00:00