mysql两个时间的差值(MySQL计算时间差的函数)-生活-

mysql两个时间的差值(MySQL计算时间差的函数)

牵着乌龟去散步 生活 3 0

大家好,mysql两个时间的差值相信很多的网友都不是很明白,包括MySQL计算时间差的函数也是一样,不过没有关系,接下来就来为大家分享关于mysql两个时间的差值和MySQL计算时间差的函数的一些知识点,大家可以关注收藏,免得下次来找不到哦,下面我们开始吧!

本文目录

  1. mysql时间类型的用法及股票知识的学习
  2. mysql集群主从延迟时间怎么计算
  3. mysql 中自定义日期减日期函数

一、mysql时间类型的用法及股票知识的学习

mysql时间类型的用法,给你转一篇日期函数说明,你看看就知道了,股票知识,太高深了,不在研究范围之内

1.1获得当前日期+时间(date+ time)函数:now()

除了 now()函数能获得当前的日期时间外,MySQL中还有下面的函数:

这些日期时间函数,都等同于 now()。鉴于 now()函数简短易记,建议总是使用 now()来替代上面列出的函数。

1.2获得当前日期+时间(date+ time)函数:sysdate()

sysdate()日期时间函数跟 now()类似,不同之处在于:now()在执行开始时值就得到了, sysdate()在函数执行时动态得到值。看下面的例子就明白了:

mysql> select now(), sleep(3), now();

+---------------------+----------+---------------------+

+---------------------+----------+---------------------+

| 2008-08-08 22:28:21| 0| 2008-08-08 22:28:21|

+---------------------+----------+---------------------+mysql> select sysdate(), sleep(3), sysdate();

+---------------------+----------+---------------------+

| sysdate()| sleep(3)| sysdate()|

+---------------------+----------+---------------------+

| 2008-08-08 22:28:41| 0| 2008-08-08 22:28:44|

+---------------------+----------+---------------------+

可以看到,虽然中途 sleep 3秒,但 now()函数两次的时间值是相同的; sysdate()函数两次得到的时间值相差 3秒。MySQL Manual中是这样描述 sysdate()的:Return the time at which the function executes。

sysdate()日期时间函数,一般情况下很少用到。

2.获得当前日期(date)函数:curdate()

其中,下面的两个日期函数等同于 curdate():

3.获得当前时间(time)函数:curtime()

其中,下面的两个时间函数等同于 curtime():

4.获得当前 UTC日期时间函数:utc_date(), utc_time(), utc_timestamp()

mysql> select utc_timestamp(), utc_date(), utc_time(), now()

+---------------------+------------+------------+---------------------+

| utc_timestamp()| utc_date()| utc_time()| now()|

+---------------------+------------+------------+---------------------+

| 2008-08-08 14:47:11| 2008-08-08| 14:47:11| 2008-08-08 22:47:11|

+---------------------+------------+------------+---------------------+

因为我国位于东八时区,所以本地时间= UTC时间+ 8小时。UTC时间在业务涉及多个国家和地区的时候,非常有用。

二、MySQL日期时间 Extract(选取)函数。

1.选取日期时间的各个部分:日期、时间、年、季度、月、日、小时、分钟、秒、微秒

set@dt='2008-09-10 07:15:30.123456';

select date(@dt);-- 2008-09-10

select time(@dt);-- 07:15:30.123456

select microsecond(@dt);-- 123456

2. MySQL Extract()函数,可以上面实现类似的功能:

set@dt='2008-09-10 07:15:30.123456';

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

MySQL Extract()函数唯一不好的地方在于:你需要多敲几次键盘。

3. MySQL dayof…函数:dayofweek(), dayofmonth(), dayofyear()

分别返回日期参数,在一周、一月、一年中的位置。

日期‘2008-08-08′是一周中的第 6天(1= Sunday, 2= Monday,…, 7= Saturday);一月中的第 8天;一年中的第 221天。

4. MySQL week…函数:week(), weekofyear(), dayofweek(), weekday(), yearweek()

select yearweek(@dt);-- 200831

MySQL week()函数,可以有两个参数,具体可看手册。 weekofyear()和 week()一样,都是计算“某天”是位于一年中的第几周。 weekofyear(@dt)等价于 week(@dt,3)。

MySQL weekday()函数和 dayofweek()类似,都是返回“某天”在一周中的位置。不同点在于参考的标准, weekday:(0= Monday, 1= Tuesday,…, 6= Sunday); dayofweek:(1= Sunday, 2= Monday,…, 7= Saturday)

MySQL yearweek()函数,返回 year(2008)+ week位置(31)。

5. MySQL返回星期和月份名称函数:dayname(), monthname()

select monthname(@dt);-- August

6. MySQL last_day()函数:返回月份中的最后一天。

select last_day('2008-02-01');-- 2008-02-29

select last_day('2008-08-08');-- 2008-08-31

MySQL last_day()函数非常有用,比如我想得到当前月份中有多少天,可以这样来计算:

mysql> select now(), day(last_day(now())) as days;

+---------------------+------+

+---------------------+------+

+---------------------+------+三、MySQL日期时间计算函数

1. MySQL为日期增加一个时间间隔:date_add()

select date_add(@dt, interval 1 day);-- add 1 day

select date_add(@dt, interval 1 hour);-- add 1 hour

select date_add(@dt, interval 1 minute);--...

select date_add(@dt, interval 1 second);

select date_add(@dt, interval 1 microsecond);

select date_add(@dt, interval 1 week);

select date_add(@dt, interval 1 month);

select date_add(@dt, interval 1 quarter);

select date_add(@dt, interval 1 year);select date_add(@dt, interval-1 day);-- sub 1 day

MySQL adddate(), addtime()函数,可以用 date_add()来替代。下面是 date_add()实现 addtime()功能示例:

mysql> set@dt='2008-08-09 12:12:33';

mysql> select date_add(@dt, interval'01:15:30' hour_second);

+------------------------------------------------+

| date_add(@dt, interval'01:15:30' hour_second)|

+------------------------------------------------+

+------------------------------------------------+mysql> select date_add(@dt, interval'1 01:15:30' day_second);

+-------------------------------------------------+

| date_add(@dt, interval'1 01:15:30' day_second)|

+-------------------------------------------------+

+-------------------------------------------------+

date_add()函数,分别为@dt增加了“1小时 15分 30秒”和“1天 1小时 15分 30秒”。建议:总是使用 date_add()日期时间函数来替代 adddate(), addtime()。

2. MySQL为日期减去一个时间间隔:date_sub()

mysql> select date_sub('1998-01-01 00:00:00', interval'1 1:1:1' day_second);

+----------------------------------------------------------------+

| date_sub('1998-01-01 00:00:00', interval'1 1:1:1' day_second)|

+----------------------------------------------------------------+

+----------------------------------------------------------------+

MySQL date_sub()日期时间函数和 date_add()用法一致,不再赘述。另外,MySQL中还有两个函数 subdate(), subtime(),建议,用 date_sub()来替代。

3. MySQL另类日期函数:period_add(P,N), period_diff(P1,P2)

函数参数“P”的格式为“YYYYMM”或者“YYMM”,第二个参数“N”表示增加或减去 N month(月)。

MySQL period_add(P,N):日期加/减去N月。

mysql> select period_add(200808,2), period_add(20080808,-2)

+----------------------+-------------------------+

| period_add(200808,2)| period_add(20080808,-2)|

+----------------------+-------------------------+

+----------------------+-------------------------+

MySQL period_diff(P1,P2):日期 P1-P2,返回 N个月。

mysql> select period_diff(200808, 200801);

+-----------------------------+

| period_diff(200808, 200801)|

+-----------------------------+

+-----------------------------+

在 MySQL中,这两个日期函数,一般情况下很少用到。

4. MySQL日期、时间相减函数:datediff(date1,date2), timediff(time1,time2)

MySQL datediff(date1,date2):两个日期相减 date1- date2,返回天数。

select datediff('2008-08-08','2008-08-01');-- 7

select datediff('2008-08-01','2008-08-08');---7

MySQL timediff(time1,time2):两个日期相减 time1- time2,返回 time差值。

select timediff('2008-08-08 08:08:08','2008-08-08 00:00:00');-- 08:08:08

select timediff('08:08:08','00:00:00');-- 08:08:08

注意:timediff(time1,time2)函数的两个参数类型必须相同。

四、MySQL日期转换函数、时间转换函数

1. MySQL(时间、秒)转换函数:time_to_sec(time), sec_to_time(seconds)

select time_to_sec('01:00:05');-- 3605

select sec_to_time(3605);--'01:00:05'

2. MySQL(日期、天数)转换函数:to_days(date), from_days(days)

select to_days('0000-00-00');-- 0

select to_days('2008-08-08');-- 733627select from_days(0);--'0000-00-00'

select from_days(733627);--'2008-08-08'

3. MySQL Str to Date(字符串转换为日期)函数:str_to_date(str, format)

select str_to_date('08/09/2008','%m/%d/%Y');-- 2008-08-09

select str_to_date('08/09/08','%m/%d/%y');-- 2008-08-09

select str_to_date('08.09.2008','%m.%d.%Y');-- 2008-08-09

select str_to_date('08:09:30','%h:%i:%s');-- 08:09:30

select str_to_date('08.09.2008 08:09:30','%m.%d.%Y%h:%i:%s');-- 2008-08-09 08:09:30

可以看到,str_to_date(str,format)转换函数,可以把一些杂乱无章的字符串转换为日期格式。另外,它也可以转换为时间。“format”可以参看 MySQL手册。

4. MySQL Date/Time to Str(日期/时间转换为字符串)函数:date_format(date,format), time_format(time,format)

mysql> select date_format('2008-08-08 22:23:00','%W%M%Y');

+------------------------------------------------+

| date_format('2008-08-08 22:23:00','%W%M%Y')|

+------------------------------------------------+

+------------------------------------------------+mysql> select date_format('2008-08-08 22:23:01','%Y%m%d%H%i%s');

+----------------------------------------------------+

| date_format('2008-08-08 22:23:01','%Y%m%d%H%i%s')|

+----------------------------------------------------+

+----------------------------------------------------+mysql> select time_format('22:23:01','%H.%i.%s');

+-------------------------------------+

| time_format('22:23:01','%H.%i.%s')|

+-------------------------------------+

+-------------------------------------+

MySQL日期、时间转换函数:date_format(date,format), time_format(time,format)能够把一个日期/时间转换成各种各样的字符串格式。它是 str_to_date(str,format)函数的一个逆转换。

5. MySQL获得国家地区时间格式函数:get_format()

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

MySQL get_format()用法的全部示例:

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

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

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

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

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

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

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

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

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

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

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

select get_format(time,'jis');--'%H:%i:%s'

select get_format(time,'iso');--'%H:%i:%s'

mysql两个时间的差值(MySQL计算时间差的函数)-第1张图片-

select get_format(time,'eur');--'%H.%i.%s'

select get_format(time,'internal');--'%H%i%s'

MySQL get_format()函数在实际中用到机会的比较少。

6. MySQL拼凑日期、时间函数:makdedate(year,dayofyear), maketime(hour,minute,second)

select makedate(2001,31);--'2001-01-31'

select makedate(2001,32);--'2001-02-01'select maketime(12,15,30);--'12:15:30'五、MySQL时间戳(Timestamp)函数

1. MySQL获得当前时间戳函数:current_timestamp, current_timestamp()

mysql> select current_timestamp, current_timestamp();

+---------------------+---------------------+

| current_timestamp| current_timestamp()|

+---------------------+---------------------+

| 2008-08-09 23:22:24| 2008-08-09 23:22:24|

+---------------------+---------------------+

2. MySQL(Unix时间戳、日期)转换函数:

from_unixtime(unix_timestamp),

from_unixtime(unix_timestamp,format)

select unix_timestamp();-- 1218290027

select unix_timestamp('2008-08-08');-- 1218124800

select unix_timestamp('2008-08-08 12:30:00');-- 1218169800select from_unixtime(1218290027);--'2008-08-09 21:53:47'

select from_unixtime(1218124800);--'2008-08-08 00:00:00'

select from_unixtime(1218169800);--'2008-08-08 12:30:00'select from_unixtime(1218169800,'%Y%D%M%h:%i:%s%x');--'2008 8th August 12:30:00 2008'

3. MySQL时间戳(timestamp)转换、增、减函数:

timestamp(date)-- date to timestamp

timestampadd(unit,interval,datetime_expr)--

timestampdiff(unit,datetime_expr1,datetime_expr2)--

select timestamp('2008-08-08');-- 2008-08-08 00:00:00

select timestamp('2008-08-08 08:00:00','01:01:01');-- 2008-08-08 09:01:01

select timestamp('2008-08-08 08:00:00','10 01:01:01');-- 2008-08-18 09:01:01select timestampadd(day, 1,'2008-08-08 08:00:00');-- 2008-08-09 08:00:00

select date_add('2008-08-08 08:00:00', interval 1 day);-- 2008-08-09 08:00:00

MySQL timestampadd()函数类似于 date_add()。

select timestampdiff(year,'2002-05-01','2001-01-01');---1

select timestampdiff(day,'2002-05-01','2001-01-01');---485

select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00');---12

select datediff('2008-08-08 12:00:00','2008-08-01 00:00:00');-- 7

MySQL timestampdiff()函数就比 datediff()功能强多了,datediff()只能计算两个日期(date)之间相差的天数。

六、MySQL时区(timezone)转换函数convert_tz(dt,from_tz,to_tz)select convert_tz('2008-08-08 12:00:00','+08:00','+00:00');-- 2008-08-08 04:00:00

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

select date_add('2008-08-08 12:00:00', interval-8 hour);-- 2008-08-08 04:00:00

select date_sub('2008-08-08 12:00:00', interval 8 hour);-- 2008-08-08 04:00:00

select timestampadd(hour,-8,'2008-08-08 12:00:00');-- 2008-08-08 04:00:00

为了您的安全,请只打开来源可靠的网址

来自:

二、mysql集群主从延迟时间怎么计算

1、主从复制延迟的监测,我以前的做法是通过比较show slave statusG中的两个变量的差值(Read_Master_Log_Pos,Exec_Master_Log_Pos),将差值设置为一个自己认为合理的范围,Seconds_Behind_Master没有适用过,今天做一次解析:

2、 Seconds_Behind_Master是通过比较 SQL THREAD接受 events事件的时间戳(timestamp)与IO THREAD执行事件 events时间戳的差值--秒数来确定slave落后于master多少。如果主从机器的时间不同,该时间的计算也是不会受影响的(如果时间发生异常,则这个秒数的就不怎么可靠啦)

3、如果slave SQL thread或者 slave I/O thread或者没有连接到master,那么该变量的值为NULL.

4、 0:表示master slave复制没有延迟(大部分情况下是这个样子)。

5、正值:表示slave落后于master的秒数。

6、在 *** 很快的情况下,I/O thread能够很快的从master上获取binlog到slave的 relay-log。这种情况下, seconds_behind_master的值能真正代表slave落后于master的秒数。在 *** 很差的情况下,I/O thread同步很慢,slave收到的二进制日志信息,SQL THREAD能够很快的执行。这个时候 seconds_behind_master是0,这种情况下 slave落后于master很多。

7、为了排除 *** 的干扰,我们可以参考percona的工具 pt-heartbeat.

8、该工具可以计算出MySQL复制或者是PostgreSQL,它可以更新master或者监控复制。它还可以从my.cnf读取配置。它借助timestmp的比较实现的,首先需要保证主从服务器时间必须要保持一致,通过与相同的一个NTP server同步时钟。它需要在主库上创建一个heartbeat的表,里面的时间戳ts就是当前的时间戳 now(),该结构也会被复制到从库上。表建好以后,会在主库上以后台进程的模式去执行一行更新操作的命令,定期去向表中的插入数据,这个周期默认为1秒,同时从库也会在后台执行

三、mysql 中自定义日期减日期函数

1. MySQL为日期增加一个时间间隔:date_add()

select date_add(@dt, interval 1 day);-加1天

select date_add(@dt, interval 1 hour);-加1小时

select date_add(@dt, interval 1 minute);-加1分钟

select date_add(@dt, interval 1 second);-加1秒

select date_add(@dt, interval 1 microsecond);-加1毫秒

select date_add(@dt, interval 1 week);-加1周

select date_add(@dt, interval 1 month);-加1月

select date_add(@dt, interval 1 quarter);-加1季

select date_add(@dt, interval 1 year);-加1年

MySQL adddate(), addtime()函数,可以用date_add()来替代。

下面是date_add()实现addtime()功能示例:

mysql> set@dt='2009-09-09 12:12:33';

mysql> select date_add(@dt, interval'01:15:30' hour_second);-加上1小时15分30秒

date_add(@dt, interval'01:15:30' hour_second)

mysql> select date_add(@dt, interval'1 01:15:30' day_second);-加1天1小时15分30秒

date_add(@dt, interval'1 01:15:30' day_second)

date_add()函数,分别为@dt增加了“1小时15分30秒”和“1天1小时15分30秒”

2. MySQL为日期减去一个时间间隔:date_sub()

mysql> select date_sub('1998-01-01 00:00:00', interval'1 1:1:1' day_second);

date_sub('1998-01-01 00:00:00', interval'1 1:1:1' day_second)

MySQL date_sub()日期时间函数和date_add()用法一致。

另外,MySQL中还有两个函数subdate(),subtime(),建议,用date_sub()来替代。

3. MySQL另类日期函数:period_add(P,N), period_diff(P1,P2)

函数参数“P”的格式为YYYYMM或者YYMM,第二个参数N表示增加或减去N month(月)。

MySQL period_add(P,N):日期加/减去N月。

mysql> select period_add(200808,2), period_add(20080808,-2)

| period_add(200808,2)| period_add(20080808,-2)|

MySQL period_diff(P1,P2):日期P1-P2,返回N个月。

mysql> select period_diff(200808, 200801);

MySQL中,这两个日期函数,一般情况下很少用到。

4. MySQL日期、时间相减函数:datediff(date1,date2), timediff(time1,time2)

MySQL datediff(date1,date2):两个日期相减date1 date2,返回天数。

select datediff('2008-08-08','2008-08-01');- 7

select datediff('2008-08-01','2008-08-08');-7

MySQL timediff(time1,time2):两个日期相减time1 time2,返回time差值。

select timediff('2008-08-08 08:08:08','2008-08-08 00:00:00');- 08:08:08

select timediff('08:08:08','00:00:00');- 08:08:08

注意:timediff(time1,time2)函数的两个参数类型必须相同。

关于本次mysql两个时间的差值和MySQL计算时间差的函数的问题分享到这里就结束了,如果解决了您的问题,我们非常高兴。

标签: 差值 时间差 mysql 函数 两个

抱歉,评论功能暂时关闭!