mysql month函数值类型_MySQL 时间日期类型和相关函数

MySQL 时间日期类型和相关函数

一、时间日期类型

MySQL中的日期和时间类型

类型

字节

最小值

最大值

零值

DATETIME

8

1000-01-01 00:00:00

9999-12-31 23:59:59

0000-00-00 00:00:00

DATE

4

1000-01-01

9999-12-31

0000-00-00

TIMESTAMP

4

19700101080001

2038年某时刻

00000000000000

TIME

3

-838:59:59

838:59:59

00:00:00

YEAR

1

1901

2155

0000

TIMESTAMP返回的格式为"YYYY-MM-DD HH:MM:SS"，宽度固定为19个字符，要想获得数字值，可以 select current_timestamp+0 的方式：

YEAR有2位(5.5.27之前的版本有)或4位格式的年，默认是4位格式，在4位格式中，允许的值1901 ~ 2155和0000，在2位格式中，允许的值是70 ~ 69，表示1970 ~ 2069，YEAR返回的格式为“YYYY”。

对于以上所有时间和日期类型，若想插入当前时间对应的值，可以用current_timestamp、now()：

二、时间日期函数

MySQL中的日期时间函数

函数

功能

格式

CURDATE()

返回当前日期

2016-10-29

CURTIME()

返回当前时间

11:20:34

NOW()

返回当前的日期和时间

2016-10-29 11:21:02

UNIX_TIMESTAMP(date)

UNIX_TIMESTAMP()

返回日期date的UNIX时间戳

执行：select unix_timestamp()

输出：1477711822

FROM_UNIXTIME(unix_timestamp)

返回UNIX时间戳的日期值

执行：select from_unixtime(1477670400)

输出：2016-10-29 11:30:22

WEEK(date)

返回日期date为一年中的第几周

执行：select week(curdate())

输出：43

YEAR(date)

返回日期date的年份

执行： select year(curdate())

输出：2016

HOUR(time)

返回time的小时值

执行：select hour(curtime());

输出：11

MINUTE(time)

返回time的分钟值

执行：select minute(curtime())

输出：36

MONTHNAME(date)

返回date的月份名

执行：select monthname(curdate())

输出October

DATE_FORMAT(date, format)

返回按字符串format格式化日期date值

执行：select date_format(now(), '%Y年%c月%e日 %T时%i分%S秒');

输出：2016年10月29日 11:44:57时44分57秒

DATE_ADD(date,INTERVAL expr type)

返回一个日期值加上一个时间间隔的时间值

执行：select now() current,date_add(now(),INTERVAL 1 day) after_one_day;

输出：2016-10-29 11:52:10 | 2016-10-30 11:52:10

DATEDIFF(expr1,expr2)

返回起始时间expr1和结束时间expr2之间的天数

执行：select datediff('2016-10-20',now());

输出：-9

执行：select datediff(now(),'2016-10-20')

输出：9

UNIX_TIMESTAMP(date)和 FROM_UNIXTIME(unix_timestamp)是互逆作用的.

DATE_FORMAT(date,format)函数：

format字符串中的格式符：

MySQL中的日期和时间格式

格式符

格式说明

%S和%s

两位数字形式的秒(00, 01, ... , 59)

%i

两位数字的分(00, 01, ... , 59)

%H

两位数字形式的小时，24小时(00,01,...,23)

%h和%I

两位数字形式的小时，24小时(01,02,...,12)

%k

数字形式的小时，24小时(0,1,2,...,23)

%l

数字形式的小时，12小时(1,2,...,12)

%T

24小时的时间形式(hh:mm:ss)

%r

12小时的时间形式(hh:mm:ssAM或hh:mm:ssPM)

%p

AM或PM

%W

一周中每一天的名称(Sunday,Monday,...,Saturday)

%a

一周中每一天的名称缩写(Sun,Mon,...,Sat)

%d

两位数字表示月中的天数(00,01,...,31)

%e

数字表示月中天数(1,2,...,31)

%D

英文后缀表示月中的天数(1st,2nd,3rd,...)

%w

数字形式表示周中的天数(0=Sunday,1=Monday...)

%j

以3位数字表示年中天数(001,002,...,366)

%U

年的周(0,1...,52)，其中Sunday位周第一天

%u

年的周(0,1...,52)，其中Monday位周第一天

%M

月名(January,February,...,December)

%b

缩写的月名(Jan,Feb,...Dec)

%m

两位数字表示的月份(01,02,...,12)

%c

数字表示的月份(1,2,...,12)

%Y

4位数字表示的年份

%y

两位数字表示的年份

%%

转义

实例：

DATE_ADD(date,INTERVAL expr type)：其中INTERVAL时间隔类型关键字，expr是一个表达式，对应后面的类型，type时间隔类型，其值如下表

MySQL日期间隔类型

表达式类型

描述

格式

HOUR

小时

hh

MINUTE

分

mm

SECOND

秒

ss

YEAR

年

YY

MONTH

月

MM

DAY

日

DD

YEAR_MONTH

年和月

YY-MM

DAY_HOUR

日和小时

DD hh

DAY_MINUTE

日和分钟

DD hh:mm

DAY_SECOND

日和秒

DD hh:mm:ss

HOUR_MINUTE

小时和分

hh:mm

HOUR_SECOND

小时和秒

hh:ss

MINUTE_SECOND

分钟和秒

mm:ss

例如，得到3天5小时后的时间：

得到3天5小时前的时间：

关于datetime和timestamp

MySQL中datetime能保存的日期范围从1001年到9999年，精度为秒，存储时把日期和时间封装为YYYYMMDDHHMMSS的格式的整数中，与时区无关，使用8个字节存储。

timestamp类型能保存的日期范围从1970年1月1日到2038年12月31日，它存储的是时间戳，只用了4个字节，所以范围比datetime小很多，timestamp是和时区相关的。默认情况下，如果插入时没有指定第一个timestamp列的值，那么mysql会默认给设置当前时间。在更新一行记录时，也会更新第一个timestamp列的值为当前时间(除非指定了该列的值)