mysql 时间函数

曾经终败给现在 2022-04-08 10:19 276阅读 0赞

转发自[：脚本之家][Link 1]

本篇文章主要介绍了mysql 中对日期的一系列操作，具有一定的参考价值，感兴趣的小伙伴们可以参考一下。

--------------------

**目录**

一、MySQL 获得当前日期时间 函数

1. 获得当前日期+时间（date + time）函数：now()

2. 获得当前日期+时间（date + time）函数：sysdate()

3. 获得当前日期（date）函数：curdate()

4. 获得当前时间（time）函数：curtime()

5. 获得当前 UTC 日期时间函数：utc\_date(), utc\_time(), utc\_timestamp()

6. format字符串格式化

二、MySQL 日期时间 Extract（选取） 函数。

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

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

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

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

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

6. MySQL last\_day() 函数：返回月份中的最后一天。

三、MySQL 日期时间计算函数

1. MySQL 为日期增加一个时间间隔：date\_add()

2. MySQL 为日期减去一个时间间隔：date\_sub()

3. MySQL 另类日期函数：period\_add(P,N), period\_diff(P1,P2)

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

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

1. MySQL （时间、秒）转换函数：time\_to\_sec(time), sec\_to\_time(seconds)

2. MySQL （日期、天数）转换函数：to\_days(date), from\_days(days)

3. MySQL Str to Date （字符串转换为日期）函数：str\_to\_date(str, format)

4. MySQL Date/Time to Str（日期/时间转换为字符串）函数：date\_format(date,format), time\_format(time,format)

5. MySQL 获得国家地区时间格式函数：get\_format()

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

五、MySQL 时间戳（Timestamp）函数

1. MySQL 获得当前时间戳函数：current\_timestamp, current\_timestamp()

2. MySQL （Unix 时间戳、日期）转换函数：

3. MySQL 时间戳（timestamp）转换、增、减函数：

六、MySQL 时区（timezone）转换函数convert\_tz(dt,from\_tz,to\_tz)

--------------------

## **一、MySQL 获得当前日期时间 函数** ##

### **1. 获得当前日期+时间（date + time）函数：now()** ###

mysql> select now();
    
    +---------------------+
    | now() |
    +---------------------+
    | 2008-08-08 22:20:46 |
    +---------------------+

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

1.  current\_timestamp()
2.  current\_timestamp
3.  localtime()
4.  localtime
5.  localtimestamp -- (v4.0.6)
6.  localtimestamp() -- (v4.0.6)

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

### **2. 获得当前日期+时间（date + time）函数：sysdate()** ###

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

mysql> select now(), sleep(3), now();
    
    +---------------------+----------+---------------------+
    | 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 functionexecutes。

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

### **3. 获得当前日期（date）函数：curdate()** ###

mysql> select curdate();
    
    +------------+
    | curdate() |
    +------------+
    | 2008-08-08 |
    +------------+

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

current\_date()，current\_date

### **4. 获得当前时间（time）函数：curtime()** ###

mysql> select curtime();
    
    +-----------+
    | curtime() |
    +-----------+
    | 22:41:30 |
    +-----------+

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

current\_time()，current\_time

### **5. 获得当前 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 时间在业务涉及多个国家和地区的时候，非常有用。

### 6. format字符串格式化 ###

MySQL 获取当前日期及日期格式

获取系统日期： NOW()

格式化日期： DATE\_FORMAT(date, format)

注： date：时间字段，format：日期格式

返回系统日期,输出 2009-12-25 14:38:59

select now();

下面格式化后输出 09-12-25

select date_format(now(),'%y-%m-%d');

根据format字符串格式化date值:

*  %S, %s 两位数字形式的秒（ 00,01, ..., 59）
 *  %I, %i 两位数字形式的分（ 00,01, ..., 59）
 *  %H 两位数字形式的小时，24 小时（00,01, ..., 23）
 *  %h 两位数字形式的小时，12 小时（01,02, ..., 12）
 *  %k 数字形式的小时，24 小时（0,1, ..., 23）
 *  %l 数字形式的小时，12 小时（1, 2, ..., 12）
 *  %T 24 小时的时间形式（hh:mm:ss）
 *  %r 12 小时的时间形式（hh:mm:ss AM 或hh:mm:ss PM）
 *  %p AM或PM
 *  %W 一周中每一天的名称（Sunday, Monday, ..., Saturday）
 *  %a 一周中每一天名称的缩写（Sun, Mon, ..., Sat）
 *  %d 两位数字表示月中的天数（00, 01,..., 31）
 *  %e 数字形式表示月中的天数（1, 2， ..., 31）
 *  %D 英文后缀表示月中的天数（1st, 2nd, 3rd,...）
 *  %w 以数字形式表示周中的天数（ 0 = Sunday, 1=Monday, ..., 6=Saturday）
 *  %j 以三位数字表示年中的天数（ 001, 002, ..., 366）
 *  %U 周（0, 1, 52），其中Sunday 为周中的第一天
 *  %u 周（0, 1, 52），其中Monday 为周中的第一天
 *  %M 月名（January, February, ..., December）
 *  %b 缩写的月名（ January, February,...., December）
 *  %m 两位数字表示的月份（01, 02, ..., 12）
 *  %c 数字表示的月份（1, 2, ...., 12）
 *  %Y 四位数字表示的年份
 *  %y 两位数字表示的年份
 *  %% 直接值“%”

## **二、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 year(@dt); -- 2008
    select quarter(@dt); -- 3
    select month(@dt); -- 9
    select week(@dt); -- 36
    select day(@dt); -- 10
    select hour(@dt); -- 7
    select minute(@dt); -- 15
    select second(@dt); -- 30
    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); -- 123456
    select 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

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

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

### **3. MySQL dayof… 函数：dayofweek(), dayofmonth(), dayofyear()** ###

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

set @dt = '2008-08-08';
    
    select dayofweek(@dt); -- 6
    select dayofmonth(@dt); -- 8
    select dayofyear(@dt); -- 221

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

### **4. MySQL week… 函数：week(), weekofyear(), dayofweek(), weekday(), yearweek()** ###

set @dt = '2008-08-08';
    
    select week(@dt); -- 31
    select week(@dt,3); -- 32
    select weekofyear(@dt); -- 32
    
    select dayofweek(@dt); -- 6
    select weekday(@dt); -- 4
    
    select yearweek(@dt); -- 200831

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

MySQLweekday() 函数和 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()** ###

set @dt = '2008-08-08';
    
    select dayname(@dt); -- Friday
    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;
    
    +---------------------+------+
    | now() | days |
    +---------------------+------+
    | 2008-08-09 11:45:45 | 31 |
    +---------------------+------+

## **三、MySQL 日期时间计算函数** ##

### **1. MySQL 为日期增加一个时间间隔：date\_add()** ###

set @dt = now();
    
    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) |
    +------------------------------------------------+
    | 2008-08-09 13:28:03 |
    +------------------------------------------------+
    
    mysql> select date_add(@dt, interval '1 01:15:30' day_second);
    +-------------------------------------------------+
    | date_add(@dt, interval '1 01:15:30' day_second) |
    +-------------------------------------------------+
    | 2008-08-10 13:28:03 |
    +-------------------------------------------------+

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) |
    +----------------------------------------------------------------+
    | 1997-12-30 22:58:59 |
    +----------------------------------------------------------------+

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) |
    +----------------------+-------------------------+
    | 200810 | 20080806 |
    +----------------------+-------------------------+
    MySQL period_diff(P1,P2)：日期 P1-P2，返回 N 个月。
    
    mysql> select period_diff(200808, 200801);
    
    +-----------------------------+
    | period_diff(200808, 200801) |
    +-----------------------------+
    | 7 |
    +-----------------------------+

在 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'); -- 733627
    select 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') |
    +------------------------------------------------+
    | Friday August 2008 |
    +------------------------------------------------+
    
    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') |
    +----------------------------------------------------+
    | 20080808222301 |
    +----------------------------------------------------+
    
    mysql> select time_format('22:23:01', '%H.%i.%s');
    +-------------------------------------+
    | time_format('22:23:01', '%H.%i.%s') |
    +-------------------------------------+
    | 22.23.01 |
    +-------------------------------------+

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

### **5. MySQL 获得国家地区时间格式函数：get\_format()** ###

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'
    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 时间戳、日期）转换函数：** ###

unix_timestamp(),
    unix_timestamp(date),
    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'); -- 1218169800
    select from_unixtime(1218290027); -- '2008-08-09 21:53:47'
    select from_unixtime(1218124800); -- '2008-08-08 00:00:00'
    selectfrom_unixtime(1218169800); -- '2008-08-08 12:30:00'
    selectfrom_unixtime(1218169800, '%Y %D %M %h:%i:%s %x'); -- '2008 8th August12:30:00 2008'

### **3. MySQL 时间戳（timestamp）转换、增、减函数：** ###

timestamp(date) -- date to timestamp
    timestamp(dt,time) -- dt + time
    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
    selecttimestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-1809:01:01
    select 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-0804: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

[Link 1]: https://www.jb51.net/article/100897.htm