文档章节

MySQL 日期转换,时间转换,时间段查询

Grace_
 Grace_
发布于 2019/05/05 09:33
字数 1812
阅读 204
收藏 2
 

时区转换

  1. 转换数据库中已存时间的时区
CONVERT_TZ(dt ,from_tz ,to_tz )
eg 
SELECT now(), CONVERT_TZ (now(), '+8:00', '-8:00')  as los;
//时区转换也可以通过 date_add, date_sub, timestampadd 来实现
 
  1. 获得国家地区时间格式
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;
 

转载请注明出处!

 

本文转载自:https://blog.csdn.net/weixin_42614447/article/details/88797808 

Grace_
粉丝 1
博文 72
码字总数 21384
作品 0
济南
后端工程师
私信 提问
mysql根据时间戳查询指定日期内数据

MySql查询时间段的方法未必人人都会,下面为您介绍两种MySql查询时间段的方法,供您参考,希望对您能有所启迪。MySql的时间字段有date、time、datetime、timestamp等,往往我们在存储数据的时...

疯子张
2014/02/09
8.3K
0
MySQL:UNIX时间戳与日期的相互转换

在PHP+MySQL编程中,UNIX时间戳和格式化日期是我们常打交道的两个时间表示形式,Unix时间戳存储、处理方便,但是不直观,格式化日期直观,但是处理起来不如Unix时间戳那么自如,所以有的时候...

BENNEE
2011/03/31
312
0
mysql获取7天前数据,日期比较

select from news where ndate>unixtimestamp( subdate( now() , interval 7 day ))order by nclick desc N天内记录 WHERE TODAYS(NOW()) - TODAYS(时间字段) <= N 今天的记录 where date(时......

今幕明
2014/12/04
7.4K
0
php+mysql中13位的时间戳

function getMillisecond() { list($t1, $t2) = explode(' ', microtime()); // return $t2 . '.' . ceil( ($t1 1000) ); return $t2 . ceil( ($t1 1000) ); } echo getMillisecond(); 上面方......

技术小胖子
2017/11/07
0
0
mysql日期转换与计算函数

MySQL 获得当前日期时间 函数 获得当前日期+时间(date + time)函数:now() mysql> select now(); +---------------------+| now() |+---------------------+| 2008-08-08 22:20:46 |+-----......

孟飞阳
2016/10/09
40
0

没有更多内容

加载失败,请刷新页面

加载更多

IDEA 拉取、上传、更新 项目到 Gitee+GitHub_超详细超简单版

注:本人使用的idea是最新版(2019.1.2),要是其他的版本的不要惊慌〜,基本上都一样,没有什么太大的差别的 首先我要说一下,拉取项目分两个,一个,你就没有项目,拉取仓库的整个项目,而...

杨木发
今天
54
0
pyqt5环境搭建(Ubuntu19.10+pycharm+python3)

1.安装pyqt5 sudo apt-get install python3-pyqt5 sudo apt-get install qttools5-dev-tools sudo apt-get install qt5-default 2.安装pycharm 下载pycharm社区版安装包并解压 在桌面新建pyc......

小芯片
今天
54
0
Vue造轮子-tab组件(中)

1. 如果给一个标签一个class,标签本身又有class,vue是默认会合并的。只有两个属性是这样一个是class,一个是style。这样就比较好改样式。 <g-tabs-head class="red"></g-tabs> 2. 组件的...

ories
昨天
59
0
Windows 版本 Anaconda 配置加速源安装软件

C:\Users\lenovo\.condarc 首先安装Anaconda最新版本。 其次添加安装目录到环境变量。文本为 C:\ProgramData\Anaconda3\Library\bin 运行 conda 命令在 Windows 用户下生成文件 .conda...

白豆腐徐长卿
昨天
232
0
如何从Bash函数返回字符串值

我想从Bash函数返回一个字符串。 我将用Java编写示例以显示我想做的事情: public String getSomeString() { return "tadaa";}String variable = getSomeString(); 下面的示例在bash中...

javail
昨天
71
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部