mysql中文日期转换_MySQL 日期转换，时间转换，时间段查询

时区转换

转换数据库中已存时间的时区

CONVERT_TZ(dt ,from_tz ,to_tz )

eg

SELECT now(), CONVERT_TZ (now(), '+8:00', '-8:00') as los;

//时区转换也可以通过 date_add, date_sub, timestampadd 来实现

获得国家地区时间格式

get_format(date|time|datetime, 'eur'|'usa'|'jis'|'iso'|'internal'

eg

select get_format(date,'usa'); -- %m.%d.%Y

select get_format(time,'usa'); -- %h:%i:%s %p

select get_format(datetime,'usa'); -- %Y-%m-%d %H.%i.%s

//函数使用说明： CONVERT_TZ() 将时间日期值 dt 从 from_tz 给出的时区转到 to_tz 给出的时区，然后返回结果值。

关于可能指定的时区的详细论述，若自变量无效，则这个函数会返回 NULL

查询当前日期 时间

select now() -- 日期+时间

select sysdate() -- 日期+时间

select current_timestamp() -- 日期+时间

select localtime() -- 日期+时间

select localtimestamp() -- 日期+时间

select curdate() -- 日期

select curtime() -- 时间

select current_time() -- 时间

sysdate() 日期时间函数跟 now() 类似，不同之处在于：now() 在执行开始时值就得到了， sysdate() 在函数执行时动态得到值

eg:select now(), sleep(3), now(); select sysdate(), sleep(3), sysdate();

格式转换

select UNIX_TIMESTAMP('2016-12-04 16:30:28') -- 把时间转换成时间戳

select FROM_UNIXTIME('1480581161',"%Y/%m/%d") -- 时间戳转换成时间

select time_to_sec('01:00:05'); -- 时间转换成秒 3605

select sec_to_time(3605); -- 秒转换成时间 '01:00:05'

select to_days('2008-08-08'); -- 日期转换成天数 733627

select from_days(733627); -- 天数转换成日期 '2008-08-08'

select date_format(now(),'%Y/%m/%d') -- 日期转化为字符串

select time_format('22:23:01', '%H.%i.%s'); -- 字符串转化为时间

select str_to_date('12.12.2016 14:09:30', '%m.%d.%Y %H:%i:%s');

-- 字符串转换为日期 2016-12-12 14:09:30

时间日期选取

set @dt = '2016-12-12 11:00:19.123456';

select date(@dt); -- 2016-12-12(日期类型)

select time(@dt); -- 11:00:19.123456(日期类型)

select year(@dt); -- 2016

select quarter(@dt); -- 4 (返回的一年日期，取值范围为1至4季度)

select month(@dt); -- 12

select week(@dt); -- 50

select day(@dt); -- 12

select hour(@dt); -- 11

select minute(@dt); -- 0

select second(@dt); -- 19

select microsecond(@dt); -- 123456(返回微秒,在mysql中我没有找到显示当前微妙的函数,now()只精确到秒,也没有找到可以储存微妙的日期类型,,好玩的是却可以抽取微秒)

select dayofweek(@dt); -- 2 (从周日算起)

select dayofmonth(@dt); -- 12

select dayofyear(@dt); -- 347

select week(@dt); -- 50(有mode参数)

select weekofyear(@dt); -- 50

select dayofweek(@dt); -- 2

select weekday(@dt); -- 0(返回日期的星期索引0=周一，1=周二，6=星期日)

select yearweek(@dt); -- 201650(获取年份和周数)(有mode参数)

select dayname(@dt); -- Monday

select monthname(@dt); -- December

select last_day(@dt); -- 2016-12-31(返回该月份的最后一天)

mode 参数相关 (用法 eg: select week(@dt,3)😉

模式

星期的第一天

范围

星期 1 是第一天

0

Sunday

0-53

一年中多一个星期天

1

Monday

0-53

一年多3天

2

Sunday

1-53

一年中多一个星期天

3

Monday

1-53

一年多3天

4

Sunday

0-53

一年多3天

5

Monday

0-53

一年中多一个星期一

6

Sunday

1-53

一年多3天

7

Monday

1-53

一年中多一个星期一

extract()选取函数

set @dt = '2016-12-12 11:21:19.123456';

select extract(year from @dt); -- 2016

select extract(quarter from @dt); -- 4

select extract(month from @dt); -- 12

select extract(week from @dt); -- 50

select extract(day from @dt); -- 12

select extract(hour from @dt); -- 11

select extract(minute from @dt); -- 21

select extract(second from @dt); -- 19

select extract(microsecond from @dt); -- 123456

select extract(year_month from @dt); -- 201712

select extract(day_hour from @dt); -- 1211

select extract(day_minute from @dt); -- 121121

select extract(day_second from @dt); -- 12112119

select extract(day_microsecond from @dt); -- 12112119123456

select extract(hour_minute from @dt); -- 1121

select extract(hour_second from @dt); -- 112119

select extract(hour_microsecond from @dt); -- 112119123456

select extract(minute_second from @dt); -- 2119

select extract(minute_microsecond from @dt); -- 2119123456

select extract(second_microsecond from @dt); -- 19123456

MySQL Extract() 函数除了没有date(),time() 的功能外，其他功能一应具全。并且还具有选取‘day_microsecond’ 等功能。注意这里不是只选取 day 和 microsecond，而是从日期的 day 部分一直选取到 microsecond 部分。

日期加减时间间隔

set @dt = '2016-12-12 11:21:19.123456';

select date_add(@dt, interval 1 day); -- 2016-12-13 11:21:19.123456

select date_add(@dt, interval 1 hour); -- 2016-12-12 12:21:19.123456

select date_add(@dt, interval 1 minute); -- 2016-12-12 11:22:19.123456

select date_add(@dt, interval 1 second); -- 2016-12-12 11:21:20.123456

select date_add(@dt, interval 1 microsecond); -- 2016-12-12 11:21:19.123457

select date_add(@dt, interval 1 week); -- 2016-12-19 11:21:19.123456

select date_add(@dt, interval 1 month); -- 2017-01-12 11:21:19.123456

select date_add(@dt, interval 1 quarter); -- 2017-03-12 11:21:19.123456

select date_add(@dt, interval 1 year); -- 2017-12-12 11:21:19.123456

select date_add(@dt, interval -1 day); -- 2016-12-11 11:21:19.123456

select date_add(@dt, interval '01:15:30' hour_second); -- 2016-12-12 12:36:49.123456

select date_add(@dt, interval '1 01:15:30' day_second); -- 2016-12-13 12:36:49.123456

select date_sub(@dt, interval '1 1:1:1' day_second); -- 2016-12-11 10:20:18.123456

-- 用法同 date_add

select period_add(@dt,2); -- 202106 (日期加/减去N月,返回到月)

select timestampadd(day, 1, '2016-12-12 12:00:00'); -- 2016-12-13 12:00:00

select timestampadd(day, 3, now()); -- 查询三天后的日期

日期差值计算

set @dt2 = '2010-10-10 10:10:10.123456';

set @dt1 = '2011-12-13 14:15:16.223456';

select datediff(@dt1,@dt2); -- 429(返回天数)

select timediff(@dt1,@dt2); -- 838:59:59.000000(返回日期类型,返回time差值)

select period_diff(@dt1,@dt2); -- 1 (返回月数)

select timestampdiff(unit,datetime_expr1,datetime_expr2)

eg:

select timestampdiff(year,@dt1,@dt2); -- -1

select timestampdiff(day ,@dt1,@dt2); -- -429

select timestampdiff(hour,@dt1,@dt2); -- -10300

日期 时间 拼接

makdedate(year,dayofyear)

eg

select makedate(2001,32); -- '2001-02-01'

maketime(hour,minute,second)

eg

select maketime(12,15,30); -- '12:15:30'

format 格式化标识

%W 星期名字(Sunday……Saturday)

%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。)

%Y 年,数字, 4 位

%y 年, 数字, 2 位

%a 缩写的星期名字(Sun……Sat)

%d 月份中的天数, 数字(00……31)

%e 月份中的天数, 数字(0……31)

%m 月, 数字(01……12)

%c 月, 数字(1……12)

%b 缩写的月份名字(Jan……Dec)

%j 一年中的天数(001……366)

%H 小时(00……23) %k 小时(0……23)

%h 小时(01……12) %I 小时(01……12)

%l 小时(1……12) %i 分钟, 数字(00……59)

%r 时间,12 小时(hh:mm:ss [AP]M)

%T 时间,24 小时(hh:mm:ss)

%S 秒(00……59)

%s 秒(00……59)

%p AM或PM

%w 一个星期中的天数(0=Sunday ……6=Saturday )

%U 星期(0……52), 这里星期天是星期的第一天

%u 星期(0……52), 这里星期一是星期的第一天

时间段查询实例

表tablename，添加时间是insert_time字段，该字段为int(5)类型的，现需要查询今天添加的文章总数并且按照时间从大到小排序，则查询语句如下：

select * from tablename where date_format(from_UNIXTIME(insert_time),'%Y-%m-%d') = date_format(now(),'%Y-%m-%d');

或者：

select * from tablename where to_days(date_format(from_UNIXTIME(insert_time),'%Y-%m-%d')) = to_days(now());

表的insert_time字段的存储类型是DATETIME类型或者TIMESTAMP类型，则查询语句也可按如下写法：

查询今天的信息记录：

select * from tablename where to_days(insert_time) = to_days(now());

查询昨天的信息记录：

select * from tablename where to_days(now()) - to_days(insert_time) <= 1;

查询近7天的信息记录：

select * from tablename where date_sub(curdate(), INTERVAL 7 DAY) <= date(insert_time);

查询近30天的信息记录：

select * from tablename where date_sub(curdate(), INTERVAL 30 DAY) <= date(insert_time);

查询本月的信息记录：

select * from tablename where date_format(insert_time, '%Y%m') = date_format(curdate() , '%Y%m');

查询上一月的信息记录：

select * from tablename where period_diff(date_format(now() , '%Y%m') , date_format(insert_time, '%Y%m')) =1;

转载请注明出处！