Mysql日期和时间总结

MySQL日期数据类型、MySQL时间类型使用总结

1，TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
在创建新记录和修改现有记录的时候都对这个数据列刷新
2，TIMESTAMP DEFAULT CURRENT_TIMESTAMP
在创建新记录的时候把这个字段设置为当前时间，但以后修改时，不再刷新它
3，TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
在创建新记录的时候把这个字段设置为0，以后修改时刷新它

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,   `create_at` timestamp NOT NULL DEFAULT 0000-00-00 00:00:00 COMMENT 创建时间,   `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 修改时间,   PRIMARY KEY (`id`)   ) ENGINE=InnoDB CHARSET=utf8;  
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,     `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,     `lastUpdated` DATETIME NOT NULL,     PRIMARY KEY (`id`)   ) ENGINE=InnoDB;   DROP TRIGGER IF EXISTS `update_example_trigger`;   DELIMITER //   CREATE TRIGGER `update_example_trigger` BEFORE UPDATE ON `example`    FOR EACH ROW SET NEW.`lastUpdated` = NOW()   DELIMITER ;  

日期类型        存储空间          日期格式                                   日期范围
------------         -------------    --------------------------------    -----------------------------------------------
datetime          8 bytes     YYYY-MM-DD HH:MM:SS   1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
timestamp       4 bytes     YYYY-MM-DD HH:MM:SS   1970-01-01 00:00:01 ~ 2038
date                 3 bytes     YYYY-MM-DD                      1000-01-01          ~ 9999-12-31
year                 1 bytes      YYYY                                  1901                ~ 2155
在 MySQL 中创建表时，对照上面的表格，很容易就能选择到合适自己的数据类型。不过到底是选择 datetime 还是 timestamp，可能会有点犯难。这两个日期时间类型各有优点：datetime 的日期范围比较大；timestamp 所占存储空间比较小，只是 datetime 的一半。另外，timestamp 类型的列还有个特性：默认情况下，在 insert, update 数据时，timestamp 列会自动以当前时间（CURRENT_TIMESTAMP）填充/更新。“自动”的意思就是，你不去管它，MySQL 会替你去处理。timestamp存储时对当前的时区进行转换，检索时再转换回当前的时区,datetime与时区无关

 

这里是一个使用日期函数的例子。 下面的查询选择了所有记录，其date_col的值是在最后30天以内：  
select timediff (now(),12:23:00)

select datediff (yy,date1,date2) from tb 

SELECT DATE_SUB(now(), INTERVAL 1 HOUR);  

SELECT "1998-01-01" - INTERVAL 1 HOUR; 

mysql  SELECT something FROM table  
WHERE TO_DAYS(NOW()) - TO_DAYS(date_col)  = 30;  

DAYOFWEEK(date)  
返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。  
mysql  select DAYOFWEEK(1998-02-03);  
-  3  

WEEKDAY(date)  
返回date的星期索引(0=星期一，1=星期二, ……6= 星 期天)。  
mysql  select WEEKDAY(1997-10-04 22:23:00);  
-  5  
mysql  select WEEKDAY(1997-11-05);  
-  2  

DAYOFMONTH(date)  
返回date的月份中日期，在1到31范围内。  
mysql  select DAYOFMONTH(1998-02-03);  
-  3  

DAYOFYEAR(date)  
返回date在一年中的日数, 在1到366范围内。  
mysql  select DAYOFYEAR(1998-02-03);  
-  34  

MONTH(date)  
返回date的月份，范围1到12。  
mysql  select MONTH(1998-02-03);  
-  2  

DAYNAME(date)  
返回date的星期名字。  
mysql  select DAYNAME("1998-02-05");  
-  Thursday  

MONTHNAME(date)  
返回date的月份名字。  
mysql  select MONTHNAME("1998-02-05");  
-  February  

QUARTER(date)  
返回date一年中的季度，范围1到4。  
mysql  select QUARTER(98-04-01);  
-  2  

WEEK(date)  
  
WEEK(date,first)  
对于星期天 是一周的第一天的地方，有一个单个参数，返回date的周数，范围在0到52。2个参数形式WEEK()允许 
你指定星期是否开始于星期天或星期 一。如果第二个参数是0，星期从星期天开始，如果第二个参数是1， 
从星期一开始。  
mysql  select WEEK(1998-02-20);  
-  7  
mysql  select WEEK(1998-02-20,0);  
-  7  
mysql  select WEEK(1998-02-20,1);  
-  8  

YEAR(date)  
返回date的年份，范围在1000到9999。  
mysql  select YEAR(98-02-03);  
-  1998  

HOUR(time)  
返回time的小时，范围是0到23。  
mysql  select HOUR(10:05:03);  
-  10  

MINUTE(time)  
返回time的分钟，范围是0到59。  
mysql  select MINUTE(98-02-03 10:05:03);  
-  5  

SECOND(time)  
回来time的秒数，范围是0到59。  
mysql  select SECOND(10:05:03);  
-  3  

PERIOD_ADD(P,N)  
增加N个月到阶段P（以格式YYMM或YYYYMM)。以格式 YYYYMM返回值。注意阶段参数P不是日期值。  
mysql  select PERIOD_ADD(9801,2);  
-  199803  

PERIOD_DIFF(P1,P2)  
返回在时期P1和P2之间月数，P1和P2应该以格式YYMM或YYYYMM。注 意，时期参数P1和P2不是日期值。  
mysql  select PERIOD_DIFF(9802,199703);  
-  11  

DATE_ADD(date,INTERVAL expr type)  
  
DATE_SUB(date,INTERVAL expr type)  
  
ADDDATE(date,INTERVAL expr type)  
  
SUBDATE(date,INTERVAL expr type)  
这些功能执行日期运算。对于MySQL 3.22，他们是新的。ADDDATE()和SUBDATE()是DATE_ADD()和 DATE_SUB()的同义词。 
在MySQL 3.23中，你可以使用+和-而不是DATE_ADD()和DATE_SUB()。（见例 子）date是一个指定开始日期的
DATETIME或DATE值，expr是指定加到开始日期或从开始日期减去的间隔值一个表达式，expr是 一个字符串；它可以以 
一个“-”开始表示负间隔。type是一个关键词，指明表达式应该如何被解释。 EXTRACT(type FROM date)函数从日期 
中返回“type”间隔。下表显示了type和expr参数怎样被关联： type 值 含义 期望的expr格式  
SECOND 秒 SECONDS  
MINUTE 分钟 MINUTES  
HOUR 时 间 HOURS  
DAY 天 DAYS  
MONTH 月 MONTHS  
YEAR 年 YEARS  
MINUTE_SECOND 分 钟和秒 "MINUTES:SECONDS"  
HOUR_MINUTE 小时和分钟 "HOURS:MINUTES"  
DAY_HOUR 天 和小时 "DAYS HOURS"  
YEAR_MONTH 年和月 "YEARS-MONTHS"  
HOUR_SECOND 小 时, 分钟， "HOURS:MINUTES:SECONDS"  
DAY_MINUTE 天, 小时, 分 钟 "DAYS HOURS:MINUTES"  
DAY_SECOND 天, 小时, 分 钟, 秒 "DAYS HOURS:MINUTES:SECONDS"  

MySQL在expr格式中允许任何标点分隔符。表示显示的是 建议的分隔符。如果date参数是一个DATE值并且你的计算仅仅 
包含YEAR、MONTH和DAY部分(即，没有时间部分)，结果是一个 DATE值。否则结果是一个DATETIME值。  

mysql  SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;  
-  1998-01-01 00:00:00  
mysql  SELECT INTERVAL 1 DAY + "1997-12-31";  
-  1998-01-01  
mysql  SELECT "1998-01-01" - INTERVAL 1 SECOND;  
-  1997-12-31 23:59:59  
mysql  SELECT DATE_ADD("1997-12-31 23:59:59",  
INTERVAL 1 SECOND);  
-  1998-01-01 00:00:00  
mysql  SELECT DATE_ADD("1997-12-31 23:59:59",  
INTERVAL 1 DAY);  
-  1998-01-01 23:59:59  
mysql  SELECT DATE_ADD("1997-12-31 23:59:59",  
INTERVAL "1:1" MINUTE_SECOND);  
-  1998-01-01 00:01:00  
mysql  SELECT DATE_SUB("1998-01-01 00:00:00",  
INTERVAL "1 1:1:1" DAY_SECOND);  
-  1997-12-30 22:58:59  
mysql  SELECT DATE_ADD("1998-01-01 00:00:00",  
INTERVAL "-1 10" DAY_HOUR);  
-  1997-12-30 14:00:00  
mysql  SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY);  
-  1997-12-02  
mysql  SELECT EXTRACT(YEAR FROM "1999-07-02");  
-  1999  
mysql  SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03");  
-  199907  
mysql  SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03");  
-  20102  

如果你指定太短的间隔值(不包括type关键词期望的间隔部分)，MySQL假设你省掉了间隔值的 最左面部分。例如， 
如果你指定一个type是DAY_SECOND，值expr被希望有天、小时、分钟和秒部分。如果你象"1:10"这样指定 值， 
MySQL假设日子和小时部分是丢失的并且值代表分钟和秒。换句话说，"1:10" DAY_SECOND以它等价 于"1:10" MINUTE_SECOND 
的方式解释，这对那MySQL解释TIME值表示经过的时间而非作为一天的时间的方式有二义性。如 果你使用确实不正确的日期， 
结果是NULL。如果你增加MONTH、YEAR_MONTH或YEAR并且结果日期大于新月份的最大值天数，日子 在新月用最大的天调整。  

mysql  select DATE_ADD(1998-01-30, Interval 1 month);  
-  1998-02-28  

注意，从前面的例子中词INTERVAL和type关键词不是区分大小写的。  
TO_DAYS(date)  
给出一个日期date，返回一个天数(从0年的天数)。  
mysql  select TO_DAYS(950501);  
-  728779  
mysql  select TO_DAYS(1997-10-07);  
-  729669  

TO_DAYS() 不打算用于使用格列高里历(1582)出现前的值。 

FROM_DAYS(N)  
给出一个天数N，返回一个DATE值。  
mysql  select FROM_DAYS(729669);  
-  1997-10-07  

TO_DAYS()不打算用于使用格列高里历(1582)出现前的值。 

 

TIME_FORMAT(time,format)  
这象上面的DATE_FORMAT()函数一样使用，但是format字符串只能包含处理 小时、分钟和秒的那些格式修饰符。 
其他修饰符产生一个NULL值或0。  
CURDATE()  
  
CURRENT_DATE  
以YYYY-MM-DD或YYYYMMDD格式返回今天日期值，取决于函数是在一个字符串还是数字上下文被使用。  
mysql  select CURRENT_DATE();  
-  1997-12-15  
mysql  select CURRENT_DATE() + 0;  
-  19971215  

CURTIME()   

以HH:MM:SS或HHMMSS格式返回当 前时间值，取决于函数是在一个字符串还是在数字的上下文被使用。  
mysql  select CURTIME();  
-  23:50:26  
mysql  select CURTIME() + 0;  
-  235026  

NOW() ，SYSDATE()，CURRENT_TIMESTAMP  
以YYYY-MM- DD HH:MM:SS或YYYYMMDDHHMMSS格式返回当前的日期和时间，取决于函数是在一个字符串还是在数字的 
上下文被使用。  
mysql  select NOW();  
-  1997-12-15 23:50:26  
mysql  select NOW() + 0;  
-  19971215235026  

UNIX_TIMESTAMP()  
  
UNIX_TIMESTAMP(date)  
如果没有参数调 用，返回一个Unix时间戳 记(从1970-01-01 00:00:00GMT开始的秒数)。如果UNIX_TIMESTAMP()用一 
个 date参数被调用，它返回从1970-01-01 00:00:00 GMT开始的秒数值 。date可以是一个DATE字符串、一个 DATETIME 
字符串、一个TIMESTAMP或以YYMMDD或YYYYMMDD格式的本地时间的一个数字。  
mysql  select UNIX_TIMESTAMP();  
-  882226357  
mysql  select UNIX_TIMESTAMP(1997-10-04 22:23:00);  
-  875996580  

当UNIX_TIMESTAMP被用于一个TIMESTAMP列，函数将直接接受值，没有 隐含的“string-to-unix-timestamp”变换。  

FROM_UNIXTIME(unix_timestamp)  
以YYYY-MM-DD HH:MM:SS或YYYYMMDDHHMMSS格式返回unix_timestamp参数所表示的值，取决于函 数是在一个字符串 
还是或数字上下文中被使用。  
mysql  select FROM_UNIXTIME(875996580);  
-  1997-10-04 22:23:00  
mysql  select FROM_UNIXTIME(875996580) + 0;  
-  19971004222300  

FROM_UNIXTIME (unix_timestamp,format)  
返回表示 Unix 时间标记的一个字符串，根据format字符串格式化。format可以包含与DATE_FORMAT()函数列出的条 
目 同样的修饰符。  
mysql SELECT FROM_UNIXTIME(1234567890, %Y-%m-%d %H:%i:%S)   
-  2009-02-14 07:31:30  

SEC_TO_TIME(seconds)  
返回seconds参数，变换成小时、分钟和秒，值以HH:MM:SS或HHMMSS格式化，取决于函数是在一个字符串还是在数字 
上 下文中被使用。  
mysql  select SEC_TO_TIME(2378);  
-  00:39:38  
mysql  select SEC_TO_TIME(2378) + 0;  
-  3938  

TIME_TO_SEC (time)  
返回time参数，转换成秒。  
mysql  select TIME_TO_SEC(22:23:00);  
-  80580  
mysql  select TIME_TO_SEC(00:39:38);  
-  2378

 

mysql中DATE_FORMAT (date, format)函数可根据format字符串格式化日期或日期和时间值date，返回结果串。 
也可用DATE_FORMAT( ) 来格式化DATE 或DATETIME 值，以便得到所希望的格式。根据format字符串格式化date值: 
下面是函数的参数说明: 
%S, %s 两位数字形式的秒（ 00,01, . . ., 59） 
%i 两位数字形式的分 （ 00,01, . . ., 59） 
%H 两位数字形式的小时 ，24 小时（00,01, . . ., 23） 
%h, %I 两位数字形式的小时，12 小时（01,02, . . ., 12） 
%k 数字形式的小时，24 小时（0,1, . . ., 23） 
%l 数字形式的小时，12 小时（1, 2, . . ., 12） 
%T 24 小时的时间形式 （hh : mm : s s） 
%r 12 小时的时间形式（hh:mm:ss AM 或hh:mm:ss PM） 
%p AM 或P M 
%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, . . ., 6=Saturday） 
%j 以三位数字表示年中的天数（ 001, 002, . . ., 366） 
% U 周（0, 1, 52），其中Sunday 为周中的第一天 
%u 周（0, 1, 52），其中Monday 为周中的第一天 
%M 月名（January, February, . . ., December） 
%b 缩写的月名（ January, February, . . ., December） 
%m 两位数字表示的月份 （ 01, 02, . . ., 12） 
%c 数字表示的月份（ 1, 2, . . ., 12） 
%Y 四位数字表示的年份 
%y 两位数字表示的年份 
%% 直接值“%” 
示例: 
select date_format(日期字段,’%Y-%m-%d’) as ‘日期’ from test 
mysql SELECT DATE_FORMAT(1997-10-04 22:23:00, %W %M %Y); 
- Saturday October 1997 
mysql SELECT DATE_FORMAT(1997-10-04 22:23:00, %H:%i:%s); 
- 22:23:00 
mysql SELECT DATE_FORMAT(1997-10-04 22:23:00, 
%D %y %a %d %m %b %j); 
- 4th 97 Sat 04 10 Oct 277 
mysql SELECT DATE_FORMAT(1997-10-04 22:23:00, 
%H %k %I %r %T %S %w); 
- 22 22 10 10:23:00 PM 22:23:00 00 6 
mysql SELECT DATE_FORMAT(1999-01-01, %X %V); 
- 1998 52

 

DATE_FORMAT(NOW(),%Y- %m- %d %h:%i %s)

DATE_FORMAT(NOW(),%b %d %Y %h:%i %p)

DATE_FORMAT(NOW(),%m-%d-%Y)

DATE_FORMAT(NOW(),%d %b %y)

DATE_FORMAT(NOW(),%d %b %Y %T:%f)

结果类似：

Dec 29 2008 11:45 PM

12-29-2008

29 Dec 08

29 Dec 2008 16:25:46.635

MySQL总结 一.SQL语句简介 1.什么是SQL？ SQL(Structured Query Language):结构化查询语言 其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”
MYSQL约束的总结和小练习 约束笔记： 1：主键约束默认是唯一且非空的 2：AUTO_INCREMENT在MYSQL8.0中会将自增主键保存到重做日志中，即使关闭后重启，存储引擎innoDB仍然会根据重做日志初始化计数器内存值。 3：设置了外键约束后，如果要删除主表的内容，则需要先把从表的相关内容给删除。 4：建表时一般设置not null default 或default 0，因为nul这个特殊值不好比较，而且查找效率低。 5：外键约束不能跨引擎使用，且mysql中其有一定成本，不适合大并发SQL操作，开发中一般在.