使用日期和格式

Working with Dates and Times

1.Changing MySQL's Date Format

select d,date_format(d,'%m %d,%y') from date_val;
使用date_format()函数，可以按照用户期望的格式重写日期值。该函数接收两个参数，一个date、datetime或者timestamp类型值，和一个定义输出格式的格式化串。这个格式化串使用与str_to_date()函数的格式化串一样的定义方式和特殊字符。

2.Setting the Client Time Zone

set session time_zone='+6:00';
select @@session.time_zone;
客户端从服务器查询到的timestamp值，就会从UTC自动转换到其所属时区

3.Determining the Current Date or Time

select curdate(),curtime(),now();
current_date和current_time函数与curdatea()和curtime()函数是等价的;current_timestamp和now()是等价的。

上面提到的函数返回值都是客户端连接所属时区的当前日期和时间。而UTC_DATE()和UTC_TIMESTAMP()则返回UTC当前日期和时间。

4.Using TIMESTAMP to Track Row Modification Times

使用timestamp数据类型，它具有自动初始化和自动更新的特性

create table ts1(ts timestamp,val int);
创建timestamp类型列的表
insert into ts1(ts,val)values(null,10);
插入一条语句
update ts1 set val=6 where val=5;
结果会发现ts列也发生了变化。

如果不希望timestamp的列发生变化

create table ts2(t_create timestamp default current_timestamp,var int);
创建表之后执行插入和更新的操作后会发现该列的值没有变化

5.Extracting Parts of Dates or Times

select d,dayname(d),left(dayname(d),3) from date_val;
dayname()函数返回date的星期名字，通过left()获取datename()的前三个字母

select d,dayname(d),dayofweek(d),weekday(d) from date_var;
dayofweek()和weekday()函数返回date的星期值，dayofweek()返回值对应星期天到星期六为从1到7；weekday()的返回值则是从0到6代表星期一到星期天。

select dt,extract(day from dt),extract(hour from dt) from datetime;
extract()是另一个用于分解日期或时间值的函数。

select curdate(),year(curdate()) as year,month(curdate()) as month,monthname(curdate()) as monthname,dayofmonth(curdate()) as day,dayname(curdate()) as dayname;
获取当前时刻的年、月、日或者是星期几。

select now(),hour(now()) as hour,minute(now()) as minute,second(now()) as second;
获取当前时刻的小时、分钟或者秒。

select dt,date_format(dt,'%y-%m-%d') as 'date part',time_format(dt,'%t') as 'time part' from datetime_val;
从datetime()值中分解出完整的日期或时间值。

select dt,left(dt,4) as year,mid(9,2) as day,right(dt,2) as second from datetime_val;
使用left()和mid()函数从时间日期值中分解出一个字串。

6.Synthesizing Dates or Times from Component Values

select maketime(10,30,58),maketime(-5,0,11);
maketime()函数接收三个参数：小时、分钟和秒，并组合得到一个时间值

select makedate(2007,60);
makedate()函数可以组合生成一个日期值，它接收的参数为年和当年的第几天。

select d,date_format(d,'%y-%m-01') from date_val;
要生成某一日期对应的当月第一天的日期，可以首先使用date_format()函数从一个日期值中分解出年和月，然后再在这个部分日期值得末尾加上。

select t1,time_format(t1,'%h:%i:00') from time_val;
生成秒部分为00的时间值。

select d,concat(year(d),'-',lpad(month(d),2,'0'),'-01') from date_val;
使用lpad函数，保证所有的月份值都由两位数组成，该函数会在需要的时候在数值右侧添加0。

select ts,concat(lpad(hour(ts),2,'0'),':',lpad(minute(ts),2,'0'),':00') as recombined from t;
对一个time类型值进行处理，使其秒部分为00，首先可以从这个time值中分解出小时和分种的部分值，然后使用concat()函数与00串联。

7.Converting Between Temporal Data Types and Basic Units

在时间值和秒之间进行转换

select ts,time_to_sec(ts) as 'time to seconds',sec_to_time(time_to_sec(ts)) as 'time to seconds to time' from t;
time_to_sec()函数将一个time类型值转换为对应的秒数，sec_to_time()则相反。

select ts,time_to_sec(ts) as 'seconds',time_to_sec(ts)/60 as 'minutes',time_to_sec(ts)/(60*60) as 'hours',time_to_sec(ts)/(24*60*60) as 'days' from ts1;
用分钟、小时或者天来表示一个时间值。

select ts,time_to_sec(ts) as 'seconds',floor(time_to_sec(ts)/60) as 'minutes',floor(time_to_sec(ts)/(60*60)) as 'hours',floor(time_to_sec(ts)/(24*60*60)) as 'days' from ts1;
floor()函数截除一个数值的小数部分。

select dt,time_to_sec(dt) as 'time part in seconds',sec_to_time(time_to_sec(dt)) as 'time part as time' from datetime_val;
将一个datetime或者timestamp类型值传递给time_to_sec()函数作为参数，那么其中的日期部分值将会被忽略。利用这一特性可以从datetime或者timestamp类型值中提取时间部分值。

select d,to_days(d) as 'date to days',from_days(to_days(d)) as 'date to days to date' from date_val;
使用to_days()和from_days()函数可以在一个日期值和对应的天数之间进行转化。

在日期值和天数之间进行转换

select dt,to_days(dt) as 'date part in days',from_days(to_days(dt)) as 'date part as date' from datetime_val;
如果给to_days()函数传递了一个datetime或者timestamp类型值，该函数会从参数中提取出日期部分，而忽略时间值。这就提供了一种从datetime或者timestamp类型值中分解出日期值的新方法。

在datetime或者timestamp类型值和秒数之间进行转换

select dt,unix_timestamp(dt) seconds,from_unixtime(unix_timestamp(dt)) as timestamp from datetime_val;
将一个timestamp或者datetime类型转换为秒数，比转换为天数更精确，但同时对所要进行转换的值的取值范围也有了限制。

8.Calculating the interval Between Two Dates or Times


使用时间差函数

set @d1='2011-12-06',@d2='2012-01-23';
select datediff(@d1,@d2) as 'd1-d2',datediff(@d2,@d1) as 'd2-d1';
计算两个日期值之间间隔的天数，可以使用datediff()函数。

set @t1='12:00:00',@t2='16:30:00';
select timediff(@t1,@t2) as 't1-t2',timediff(@t2,@t1) as 't2-t1';
timidiff()函数计算两个time类型值之间的时间间隔。

select t1,t2,timediff(t2,t1) as 't2-t1 as time',if(timediff(t2,t1)>=0,'+','-') as sign,hour(timediff(t2,t1)) as hour,minute(timediff(t2,t1)) as minute,second(timediff(t2,t1)) as second from time_val;
将一个时间间隔分为时、分、秒值显示，在sql中使用hour()、minute()、second()函数计算时间间隔的各个部分。

set@dt1='1900-01-01 00:00:00',@dt2='1910-01-01 00:00:00';
select timestampdiff(minute,@dt1,@dt2) as minutes,timestampdiff(hour,@dt1,@dt2) as hours,timestampdiff(day,@dt1,@dt2) as days,timestampdiff(week,@dt1,@dt2) as weeks,timestampdiff(year,@dt1,@dt2) as years;
timestampdiff(()函数来计算时间间隔，在使用该函数时，可以指定函数返回的时间间隔使用的基本单位。
timestampdiff(unit,val1,val2)
其中unit为计算时间间隔所用的基本单位(天或者秒),val1和val2分别是所要计算相互间隔的两个时间值。

利用基本时间单位计算时间间隔

select t1,t2,time_to_sec(t2) - time_to_sec(t1) as 't2 - t1 (in seconds)',sec_to_time(time_to_sec(t2) - time_to_sec(t1)) as 't2 - t1 (as time)' from time_val;
要计算两个时间之间间隔的秒数，首先使用time_to_sec()将这两个时间都转换为秒，然后计算差值。如果希望计算结果仍然以一个时间值的形式出现，需要使用sec_to_time()对计算结果再做一次转换。
上面的语句计算了time_val表中，t1列和t2列值之间的时间间隔，分别用秒和time类型值显示计算结果。

使用基本单位计算两个date类型值，或者两个date-and-time类型值的时间间隔

select to_days('1884-02-02') - to_days('1883-06-05') as days;
使用to_days()函数将所要处理的值转换为多少天。

select (to_days('1884-01-01') - to_days('1883-06-05'))/7 as weeks;
以星期为单位来计算时间间隔，使用和上例一样的方法，最后结果再除以7。

set @dt1='1984-01-01 09:00:00';
set @dt2=@dt1+interval 14 day;
select unix_timestamp(@dt2) - unix_timestamp(@dt1) as seconds;
以秒为单位，来计算相隔两个星期的两个日期之间的时间间隔

set @interval=unix_timestamp(@dt2) - unix_timestamp(@dt1);
select @interval as seconds;
@interval / 60 as minutes,
@interval / (60*60) as hours,
@interval / (24 * 60 * 60) as days,
@interval / (7 * 24 * 60 * 60) as weeks;
多少秒可以转换为多少分钟、多少小时、多少天或者是多少个星期。

9.Adding Date or Time Values

使用时间加法函数或者操作符进行时间值求和运算

set @t1='12:00:00',@t2='15:30:00';
select addtime(@t1,@t2);
使用addtime()函数，把一个时间值或者一个date-and-time类型值和一个时间值相加。

set @d='1984-03-01',@t='15:30:00';
select timestamp(@d,@t);
使用timestamp()函数，把一个时间值或者一个date-and-time类型值和一个时间值相加。

mysql也提供了date_add()和date_sub()函数，来对一个日期值和一个时间值进行加法或减法运算。每个函数都以一个日期(或日期和时间)值d和一个时间间隔为参数，语法如下：
date_add(d,interval val unit)
date_sub(d,interval val unit)
+interval和-interval 操作符也是类似语法：
d + interval val unit
d - interval val unit
unit就是计量时间间隔的时间单位，val指明多少个unit。常用的时间单位有second、minute、hour、day、month和year。
注：所有的时间单位都是单数。

使用date_add()或者date_sub()函数，对日期值可以进行如下数学运算

求出即日第三天的日期值
select curdate(),date_add(curdate(),interval 3 day);

一个星期以前的日期值
select curdate(),date_sub(curdate(),interval 7 day);

60小时后是什么时间？
select now(),date_add(now(),interval 60 hour);

在当前时刻上，加上了14.5小时
select now(),date_add(now(),interval '14:30' hour_minute);

在当前时刻加上3天4小时
select now(),date_add(now(),interval '3 4' day_hour);

也可以使用+interval或者-interval操作符对日期值进行加减运算
select curdate(),curdate()+interval 1 year;
select now(),now() - interval '1 12' day_hour;

timestampadd(unit,interval,d) = date_add(d,INTERVAL  interval unit)

要将一个以秒为单位的时间值和另一个time类型值相加，首先将time值转换为以秒为单位，使得两个操作值使用相同的时间单位，然后相加，最后再将结果转换回time类型值。

select t1,set_to_time(time_to_sec(t1)+7200) as 't1 plus 2 hours' from time_val;
在time_val表中t1列的每一个值加上2小时

如果所要加上的时间值也是一个time类型值，首先将其转换为以秒为单位，然后再相加。

select t1,t2,time_to_sec(t1)+time_to_sec(t2) as 't1+t2(in seconds)',sec_to_time(time_to_sec(t1)+time_to_sec(t2)) from time_val;
将time_val表中每一行上的两个time类型列值相加

10.Calculating Ages

计算年龄最简单的方法，就是使用timestampdiff()函数，因为可以传递一个出生日期，一个当前日期以及你所希望使用的计量年龄的时间单位
timestampdiff(unit,birth,current)

select name,birth,curdate() as today,timestampdiff(year,birth,curdate()) as 'age in years' from sibling;
Smith家的小孩今天多大？

select name,birth,'1953-03-05' as 'Franz' 'birthday',timestampdiff(year,birth,'1953-03-05') as 'age in years' from sibling where name !='Franz';
Franz出生时Gretchen和Wilbur年纪多大？

select name,birth,curdate() as today,timestampdiff(month,birth,curdate()) as 'age in months' from sibling;
以月为单位的Smith家的小孩的年龄

11.Shifting a Date-and-Time Value to a Different Time Zone

set @dt='2006-11-23 09:00:00';
select @dt as Chicago, convert_tz(@dt,'us/central','europe/berlin') as berlin, convert_tz(@dt,'us/central','europe/london') as london, convert_tz(@dt,'us/central','america/edmonton') as edmonton, convert_tz(@dt,'us/central','australia/brisbane') as brisbane\G
使用convert_tz()函数计算对应每一个时区的时间

12.Finding the First Day,Last Day,or Length of a Month

select ts,date_sub(ts,interval dayofmonth(ts)-1 day) as '1st of month' from t;
为了找到所给日期所处月份的第一天的日期，将该日期值向后推移dayofmonth()-1天。

date_add(date_sub(d,interval dayofmonth(d)-1 day),interval n month)
为了找到一个与给定日期所处月份间隔n个月的某一个月的第一天的日期，首先计算该日期所处月份的第一天的日期，然后将得到的日期值推移n个月

select d,date_add(date_sub(d,interval dayofmonth(d)-1 day),interval -1 month) as '1st of previous month',date_add(date_sub(d,interval dayofmonth(d)-1 day),interval 1 month) from date_val;
为了找到一个给定日期所处月的前一个和后一个月的第一天，n为-1和1

select d,last_day(d) as 'last of month' from date_val;
找到一个给定的日期所处月份的最后一天

last_day(date_add(d,interval n month))
为了找到与一个给定日期间隔n个月的某一个月份的最后一天，首先将该日期推移n个月，然后将推移的结果作为last_day()函数的参数

select d,last_day(date_add(d,interval -1 month)) as 'last of previous month',last_day(date_add(d,interval 1 month)) as 'last of following month' from date_val;
为了找到一个给定日期所处月份的前一个和后一个月的最后一天，n为-1和1

select d,dayofmonth(last_day(d)) as 'days in month' from date_val;
为了计算一个月有多少天，首先用last_day()函数计算当月最后一天的日期，然后使用dayofmonth()函数从结果中分解出day-of-month部分

13.Finding the Day of the Week for a Date

select curdate(),dayname(curdate());
为了得到一个给定日期对应的星期几的名称，使用dayname()函数

select @d as 'starting date', @first as '1st of month date', dayname(@first) as '1st of month day';
确定一个月的第一天是星期几

14.Finding Dates for Any Weekday of a Given Week

date_add(date_sub(d,interval dayofweek(d) day),interval n day)
对一个日期d，当n取值从1到7得到从星期天到星期六的日期

select d,dayname(d) as day,date_add(d,interval 1-dayofweek(d) day) as sunday,
date_add(d,interval 7-dayofweek(d) day) as saturday from date_val;
使用一个变量n，n=1代表星期天，n=7代表星期六，来查找一个星期的第一天和最后一天。

set @target=date_sub(date_add(curdate(),interval 4-dayofweek(curdate()) day),interval 14 day);
select curdate(),@target,dayname(@target);
计算两个星期前的星期三的日期

15.Performing Leap Year Calculations

select d,year(d)%4=0 as 'rule-of-thumb test',(year(d)%4=0) and ((year(d)%100 != 0) or (year(d)%400=0)) as 'complete test' from date_val;
检测闰年以及完整检测date_val表中的所有日期

set @d='2006-04-13';
select dayofyear(date_format(@d,'%y-12-31'));
计算一年的天数(计算出当年最后一天的日期，然后将其传递给dayofyear())