mysql日期与时间操作：
----选择合适的提起或者时间变量类型-----
--创建表、初始化数据
DROP TABLE IF EXISTS date_val;
CREATE TABLE date_val
(
d DATE
);
INSERT INTO date_val (d) VALUES('1864-02-28');
INSERT INTO date_val (d) VALUES('1900-01-15');
INSERT INTO date_val (d) VALUES('1987-03-05');
INSERT INTO date_val (d) VALUES('1999-12-31');
INSERT INTO date_val (d) VALUES('2000-06-04');
DROP TABLE IF EXISTS datetime_val;
CREATE TABLE datetime_val
(
dt DATETIME
);
INSERT INTO datetime_val (dt) VALUES('1970-01-01 00:00:00');
INSERT INTO datetime_val (dt) VALUES('1987-03-05 12:30:15');
INSERT INTO datetime_val (dt) VALUES('1999-12-31 09:00:00');
INSERT INTO datetime_val (dt) VALUES('2000-06-04 15:45:30');
DROP TABLE IF EXISTS time_val;
CREATE TABLE time_val
(
t1 TIME,
t2 TIME
);
INSERT INTO time_val (t1,t2) VALUES('15:00:00','15:00:00');
INSERT INTO time_val (t1,t2) VALUES('05:01:30','02:30:20');
INSERT INTO time_val (t1,t2) VALUES('12:30:20','17:30:45');
DROP TABLE IF EXISTS time_val;
CREATE TABLE time_val
(
t1 TIME,
t2 TIME
);
INSERT INTO time_val (t1,t2) VALUES('15:00:00','15:00:00');
INSERT INTO time_val (t1,t2) VALUES('05:01:30','02:30:20');
INSERT INTO time_val (t1,t2) VALUES('12:30:20','17:30:45');
DROP TABLE IF EXISTS timestamp_val;
CREATE TABLE timestamp_val
(
ts TIMESTAMP
);
INSERT INTO timestamp_val (ts) VALUES('1970-01-01 09:00:00');
INSERT INTO timestamp_val (ts) VALUES('1987-03-05 12:30:15');
INSERT INTO timestamp_val (ts) VALUES('1999-12-31 09:00:00');
INSERT INTO timestamp_val (ts) VALUES('2000-06-04 15:45:30');
--查看数据
select * from date_val;
select * from datetime_val;
select * from time_val;
select * from timestamp_val;
----------------------修改MySQL日期格式-------------------------
select str_to_date('May 13, 2007', '%M %d, %Y');
insert into date_val (d) values(str_to_date('May 13, 2007', '%M %d, %Y'));
select * from date_val;
select d, date_format(d, '%M %d, %Y') from date_val;
select d, date_format(d, '%M %d, %Y') as date from date_val;
--格式化串定义
格式化字符 含义
%Y 年份，数字形式，4位数
%y 年份，数字形式，2位数
%M 完整度月份名称（Hanuary-December）
%b 月份名称的前三个字母（Jan-Dec）
%m 月份，数字形式（01..12）
%c 月份，数字形式(1..12)
%d 该月日期，数字形式（01..31）
%e 该月日期，数字形式（1..31）
%W 工作日名称（Sunday..Saturday）
%r 时间，12小时制，以AM或PM结尾
%T 时间，24小时制
%H 小时，数字形式，2位数（00..23）
%i 分钟，数字形式，2位数（00..59）
%s 秒，数字形式，2位数（00..59）
%% '%'文字字符
select dt, date_format(dt, '%c/%e/%y %r') as format1, date_format(dt, '%M %e, %Y %T') as format2 from datetime_val;
select dt, time_format(dt, '%r') as '12-hour time', time_format(dt, '%T') as '24-hour time' from datetime_val;
delimiter $$
create function time_ampm(t time)
returns varchar(13) # mm:dd:ss (a.m.|p.m.)格式
begin
declare ampm char(4);
if time_to_sec(t) < 12*60*60 then
set ampm = 'a.m.';
else
set ampm = 'p.m.';
end if;
return concat(left(time_format(4, '%r'), 9), ampm);
end;
$$
delimiter ;
select t1, time_ampm(t1) from time_val;
----------------------设置客户端时区-------------------------
--客户端位于不同时区需要注意，如果位于同一时区则不需要关心
drop table if exists t;
create table t (ts timestamp);
insert into t (ts) values('2006-06-01 12:30:00');
select ts from t;
select @@global.time_zone, @@session.time_zone;
select ts from t;
set session time_zone = '+04:00';
select @@global.time_zone, @@session.time_zone;
select ts from t;
----------------------获取当前日期或时间-------------------------
select curdate(), curtime(), now();
select current_date(), current_time(), current_timestamp();
select utc_date(), utc_time(), utc_timestamp();
----------------------使用timestamp跟踪行修改时间-------------------------
--timestamp初始化为当前日期，并随行值改变而改变
drop table if exists t;
create table t(ts timestamp);
show create table tG
drop table if exists tsdemo1;
create table tsdemo1(ts timestamp, val int);
insert into tsdemo1(val) values(5);
insert into tsdemo1(ts, val) values(null, 10);
select * from tsdemo1;
update tsdemo1 set val = 6 where val = 5;
select * from tsdemp1;
update tsdemo1 set val = val + 1;
--timestamp初始化为当前日期，行值改变不改变
drop table if exists tsdemo2;
create table tsdemo2(t_create timestamp default current_timestamp, val int);
select * from tsdemo2;
insert into tsdemo2(val) values(5);
insert into tsdemo2(t_create, val) values(null, 10);
select * from tsdemo2;
update tsdemo2 set val = val + 1;
select * from tsdemo2;
----------------------从日期或者时间值中分解出各部分值-------------------------
--使用成分分解函数来分解日期和时间值
select dt, date(dt), time(dt) from datetime_val;
--日期相关函数
函数 返回值
year() 月份数值
month() 月份名称（1..12）
monthname() 月份中的天数值（January..December）
dayofmonth() 一周中的天数（1..31）
dayname() 一周中的天数（Sunday..Saturday）
dayofweek() 一周中的天数（1..7对应Sunday..Saturday）
weekday() 一周中的天数（0..6对应Monday..Sunday）
dayofyear() 一年中的天数值（1.366）
hour() 时间中的小数值（0..23）
minute() 时间中的分数值（0..59）
second() 时间中的秒数（0..59）
select dt, year(dt), dayofmonth(dt), hour(dt), second(dt) from datetime_val;
select d, dayofyear(d) from date_val;
select d, dayname(d), left(dayname(d), 3) from date_val;
select d, dayname(d), dayofweek(d), weekday(d) from date_val; /*dayofweek 1~7星期天到星期六 weekday0~6星期一到星期天*/
select dt, extract(day from dt), extract(hour from dt) from datetime_val; /*year,month,day,hour,minute,second*/
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') as year, date_format(dt, '%d') as day, time_format(dt, '%H') as hour, time_format(dt, '%s') as second from datetime_val;
select dt, date_format(dt, '%Y-%m-%d') as 'date part', time_format(dt, '%T') as 'time part' from datetime_val;
select ts, date_format(ts, '%M %e, %Y') as 'descriptive date', time_format(ts, '%H:%i') as 'hours/minutes' from timestamp_val;
--使用字符串函数分解时间或者日期值
select dt, left(dt, 4) as year, mid(dt, 9, 2) as day, right(dt, 2) as second from datetime_val;
select dt, left(dt, 10) as date, right(dt, 8) as time from datetime_val;
select ts, left(ts, 10) as date, right(ts, 8) as time from timestamp_val;
----------------------合成日期或者时间-------------------------
select maketime(10,30,58), maketime(-5,0,11);
select makedate(2007,60);
select d, date_format(d, '%Y-%m-01') from date_val;
select t1, time_format(t1, '%H:%i:00') from time_val;
select d, concat(year(d), '-', month(d), '-01') from date_val;
select d, concat(year(d), '-', lpad(month(d), 2, '0'), '-01') from date_val;
select d, concat(year(d), '-', lpad(month(d), 4, '0'), '-01') from date_val;
select t1, concat(lpad(hour(t1), 2, '0'), ':', lpad(minute(t1), 2, '0'), ':00') as recombined from time_val;
set @d = '2006-02-28';
set @t = '13:10:05';
select @d, @t, concat(@d, ' ', @t);
----------------------在时间数据类型和基本单位间进行转换-------------------------
--在时间和秒之间进行转换
select t1, time_to_sec(t1) as 'TIME to seconds', sec_to_time(time_to_sec(t1)) as 'TIME to seconds to TIME' from time_val;
select t1, time_to_sec(t1) as 'seconds', time_to_sec(t1)/60 as 'minutes', time_to_sec(t1)/(60*60) as hours, time_to_sec(t1)/(24*60*60) as 'days' from time_val;
select t1, time_to_sec(t1) as 'seconds',
floor(time_to_sec(t1)/60) as 'minutes',
floor(time_to_sec(t1)/(60*60)) as hours,
floor(time_to_sec(t1)/(24*60*60)) as 'days'
from time_val;
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;
select ts, time_to_sec(ts) as 'time part in seconds',
sec_to_time(time_to_sec(ts)) as 'time part as TIME'
from timestamp_val;
--在日期值和天数之间进行转换
select d, to_days(d) as 'date to days', from_days(to_days(d)) as 'DATE to days to DATE' from date_val;
select dt, to_days(dt) as 'date part in days', from_days(to_days(dt)) as 'date part as DATE' from datetime_val;
select ts, to_days(ts) as 'date part in days', from_days(to_days(ts)) as 'date part as DATE' from timestamp_val;
--在datetime或者timestamp类型值和秒数之间进行转换
select dt, unix_timestamp(dt) as seconds, from_unixtime(unix_timestamp(dt)) as timestamp from datetime_val;
select curdate(), unix_timestamp(curdate()), from_unixtime(unix_timestamp(curdate()))G
----------------------计算两个日期和时间之间的间隔-------------------------
--使用时间差函数
set @d1 = '2010-01-01', @d2 = '2009-12-01';
select datediff(@d1, @d2) as 'd1 - d2', datediff(@d2, @d1) as 'd2 - d1';
set @t1 = '12:00:00', @t2 = '16:30:00';
select timediff(@t1, @t2) as 't1 - t2', timediff(@t2, @t1) as 't2 - t1';
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;
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;
--利用基本时间单位计算时间间隔
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;
--使用基本单位计算两个Date类型值，或者两个date-and-time类型值的时间间隔
select to_days('1884-01-01') - to_days('1883-06-05') as days;
select (to_days('1884-01-01') - to_days('1883-06-05')) / 7 as weeks;
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;
set @dt1 = '1800-02-14 07:30:00';
set @dt2 = @dt1 + interval 7 day;
set @interval = ((to_days(@dt2) - to_days(@dt1)) * 24 * 60 * 60)
+ time_to_sec(@dt2) - time_to_sec(@dt1);
select @interval as seconds, sec_to_time(@interval) as time;
----------------------增加日期或时间值-------------------------
--使用时间加法函数或者操作符进行时间值求和运算
set @t1 = '12:00:00', @t2 = '15:30:00';
select addtime(@t1, @t2);
set @dt = '1984-03-01 12:00:00', @t = '12:00:00';
select addtime(@dt, @t);
set @d = '1984-03-01', @t = '15:30:00';
select timestamp(@d, @t);
set @dt = '1984-03-01 12:00:00', @t = '12:00:00';
select timestamp(@dt, @t);
--求出即日第三天的日期值
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);
select now(), date_add(now(), interval '14:30' hour_minute);
select now(), date_add(now(), interval '3 4' day_hour);
select curdate(), curdate() + interval 1 year;
select now(), now() - interval '1 12' day_hour;
--使用基本时间单位进行时间加法运算
select t1, sec_to_time(time_to_sec(t1) + 7200) as 't1 plus 2 hours' from time_val;
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)) as 't1 + t2 (as TIME)'
from time_val;
select t1, t2, mod(time_to_sec(t1) + time_to_sec(t2), 86400) as 't1 + t2 (in seconds)',
sec_to_time(mod(time_to_sec(t1) + time_to_sec(t2), 86400)) as 't1 + t2 (as TIME)'
from time_val;
set @d = '2006-01-01';
select @d as date, from_days(to_days(@d) + 7) as 'date + 1 week',
from_days(to_days(@d) - 7) as 'date - 1 week';
set @dt = '2006-01-01 12:30:45';
select @dt as datetime,
from_days(to_days(@dt) + 7) as 'datetime + 1 week',
from_days(to_days(@dt) - 7) as 'datetime - 1 week';
set @dt = '2006-01-01 09:00:00';
select @dt as datetime,
from_unixtime(unix_timestamp(@dt) + 3600) as 'datetime + 1 hour',
from_unixtime(unix_timestamp(@dt) - 3600) as 'datetime - 1 hour';
----------------------计算年龄-------------------------
--创建表、初始化数据
drop table if exists sibling;
create table sibling
(
name char(20),
birth date
);
insert into sibling (name,birth) values('Gretchen','1942-04-14');
insert into sibling (name,birth) values('Wilbur','1946-11-28');
insert into sibling (name,birth) values('Franz','1953-03-05');
select * from sibling;
select name, birth, curdate() as today, timestampdiff(year, birth, curdate()) as 'age in years' from sibling;
select name, birth, '1953-03-05' as 'Franz'' birth', timestampdiff(year, birth, '1953-03-05') as 'age in years' from sibling where name != 'Franz';
select name, birth, curdate() as today, timestampdiff(month, birth, curdate()) as 'age in months' from sibling;
select dayofyear('1995-03-01'), dayofyear('1996-02-29');
select right('1995-03-01', 5), right('1996-02-29', 5);
select if('02-29' < '03-01', '02-29', '03-01') as earliest;
set @birth = '1965-03-01';
set @target = '1975-01-01';
select @birth, @target, year(@target)- year(@birth) as 'difference',
if(right(@target, 5) < right(@birth, 5), 1, 0) as 'adjustment',
year(@target) - year(@birth) - if(right(@target, 5) < right(@birth, 5), 1, 0) as 'age';
select name, birth, curdate() as today, year(curdate()) - year(birth) - if(right(curdate(), 5) < right(birth, 5), 1, 0) as 'age in years' from sibling;
select name, birth, '1953-03-05' as 'Franz'' birthday', year('1953-03-05') - year(birth) - if(right('1953-03-05', 5) < right(birth, 5), 1, 0) as 'age in years' from sibling where name != 'Franz';
select name, birth, curdate() as today, (year(curdate()) - year(birth)) * 12 + (month(curdate()) - month(birth)) - if(dayofmonth(curdate()) < dayofmonth(birth), 1, 0) as 'age in months' from sibling;
--将一个日期和时间值切换到另一个时区
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 BrisbaneG
select @dt as Chicago, convert_tz(@dt, '-06:00', '+01:00') as Berlin,
convert_tz(@dt, '-06:00', '+00:00') as London,
convert_tz(@dt, '-06:00', '-07:00') as Edmonton,
convert_tz(@dt, '-06:00', '+10:00') as BrisbaneG
----------------------找出每月的第一天和最后一天-------------------------
select d, date_sub(d, interval dayofmonth(d)-1 day) as '1st of month' from date_val;
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) as '1st of following month'
from date_val;
select d, last_day(d) as 'last of month' from date_val;
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;
select d, dayofmonth(last_day(d)) as 'days in month' from date_val;
----------------------通过字串替换来计算日期-------------------------
select d, date_format(d, '%Y-%m-01') as method1,
concat(year(d), '-', lpad(month(d), 2, '0'), '-01') as method2
from date_val;
select d, date_format(d, '%Y-01-01') as method1,
concat(year(d), '-01-01') as method2
from date_val;
select d, date_format(d, '%Y-12-15') as method1,
concat(year(d), '-12-15') as method2
from date_val;
select curdate(), date_add(date_format(curdate(), '%Y-12-25'), interval 2 year) as method1,
date_format(date_add(curdate(), interval 2 year), '%Y-12-25') as method2;
----------------------计算某个日期为星期几-------------------------
select curdate(), dayname(curdate());
--一个月的第一天是星期几
set @d = curdate();
set @first = date_sub(@d, interval dayofmonth(@d)-1 day);
select @d as 'starting date',
@first as '1st of month date',
dayname(@first) as '1st of month day';
----------------------查出给定某周到某天的日期-------------------------
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;
--两个星期前星期三的日期
set @target = date_sub(date_add(curdate(), interval 4-dayofweek(curdate()) day), interval 14 day);
select curdate(), @target, dayname(@target);
set @target = date_add(date_sub(curdate(), interval 14 day), interval 4-dayofweek(curdate()) day);
select curdate(), @target, dayname(@target);
----------------------执行闰年计算-------------------------
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;
set @d = '2006-04-13';
select dayofyear(date_format(@d, '%Y-12-31'));
set @d = '2008-04-13';
select dayofyear(date_format(@d, '%Y-12-31'));
----------------------接近但不是iso格式的日期格式-------------------------
select d, concat(year(d), '-', month(d), '-01') from date_val;
select d, concat(year(d), '-', lpad(month(d), 2, '0'), '-01') from date_val;
select concat(year(d), '-', month(d), '-01') as 'non-iso',
date_add(concat(year(d), '-', month(d), '-01'), interval 0 day) as 'iso 1',
concat(year(d), '-', month(d), '-01') + interval 0 day as 'iso2',
from_days(to_days(concat(year(d), '-', month(d), '-01'))) as 'iso 3',
str_to_date(concat(year(d), '-', month(d), '-01'), '%Y-%m-%d') as 'iso 4'
from date_val;
----------------------将日期或时间当成数值-------------------------
select t1, t1+0 as 't1 as number',
floor(t1) as 't1 as number',
floor(t1/10000) as 'hour part'
from time_val;
select d, d+0 from date_val;
select dt, dt+0, floor(dt+0) from datetime_val;
select '1999-01-01' + 0, '1999-01-01 12:30:45' + 0, '12:30:45'+0;
show warnings;
----------------------强制MySQL将字符串当作时间值-------------------------
select '12:30:45'+0, sec_to_time(time_to_sec('12:30:45'))+0;
select '1999-01-01'+0, from_days(to_days('1999-01-01'))+0;
select date_add('1999-01-01 12:30:45', interval 0 day)+0 as 'numberic datetime';