数据库中 日期的几种表示方法

Mysql中经常用来存储日期的数据类型有三种：Date、Datetime、Timestamp。

【1】Date数据类型：用来存储没有时间的日期。Mysql获取和显示这个类型的格式为“YYYY-MM-DD”。支持的时间范围为“1000-00-00”到“9999-12-31”。

【2】Datetime类型：存储既有日期又有时间的数据。存储和显示的格式为 “YYYY-MM-DD HH:MM:SS”。支持的时间范围是“1000-00-00 00:00:00”到“9999-12-31 23:59:59”。

【3】Timestamp类型：也是存储既有日期又有时间的数据。存储和显示的格式跟Datetime一样。支持的时间范围是“1970-01-01 00:00:01”到“2038-01-19 03:14:07”。

扩展资料

切记不要用字符串存储日期

这种存储日期的方式的优点还是有的，就是简单直白，容易上手。但是，这是不正确的做法，主要会有下面两个问题：

1、字符串占用的空间更大。

2、字符串存储的日期比较效率比较低（逐个字符进行比对），无法用日期相关的 API 进行计算和比较。

DateTime 类型没有时区信息的

DateTime 类型是没有时区信息的（时区无关） ，DateTime 类型保存的时间都是当前会话所设置的时区对应的时间。

当你的时区更换之后，比如你的服务器更换地址或者更换客户端连接时区设置的话，就会导致你从数据库中读出的时间错误。不要小看这个问题，很多系统就是因为这个问题闹出了很多笑话。

Timestamp 和时区有关。Timestamp 类型字段的值会随着服务器时区的变化而变化，自动换算成相应的时间，说简单点就是在不同时区，查询到同一个条记录此字段的值会不一样。

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(

9to_date('2010-2-12 10:20:30','YYYY-MM-DD HH24:MI:SS'),

10to_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-12Typ=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

MYDATA1MYDATA2

---------- ----------

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 043FRIDAY 207

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:062010 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

NEXTDAY1NEXTDAY2

----------- -----------

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

NEWDATE1NEWDATE2

----------- -----------

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

NEWDATE1NEWDATE2

----------- ------------------------

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

SQL 用于各种数据库的数据类型：

一、MySQL 数据类型：

在 MySQL 中，有三种主要的类型：Text（文本）、Number（数字）和 Date/Time（日期/时间）类型。

1、Text 类型。

2、Number 类型。这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数。

3、Date 类型。

即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。

TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。

二、SQL Server 数据类型：

1、String 类型。

2、Number 类型。

3、Date 类型。

4、其他数据类型。

扩展资料：

Microsoft Access的数据类型：

参考资料：

百度百科--SQL数据类型

---