MYSQL处理日期时间函数（转）

电玩女神 2023-08-17 15:27 179阅读 0赞

**目录**

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

1.1 获得当前日期 + 时间（date + time）

1.2 获得当前日期 + 时间（date + time）

1.3 获得当前日期（date）

1.4 获得当前时间（time）

1.5 获得当前 UTC 日期时间

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

2.1 选取日期时间的各个部分：

2.2 MySQL Extract() 函数

2.3 MySQL dayof…

2.4 MySQL week…

2.5 MySQL 返回星期和月份名称

2.6 返回月份中的最后一天

三、MySQL 日期时间计算函数

3.1 MySQL 为日期增加一个时间间隔

3.2 MySQL 为日期减去一个时间间隔

3.3 MySQL 另类日期

3.4 MySQL 日期、时间相减

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

4.1 MySQL （时间、秒）转换

4.2 MySQL （日期、天数）转换

4.3 MySQL Str to Date （字符串转换为日期）

4.4 MySQL Date/Time to Str（日期/时间转换为字符串）

4.5 MySQL 获得国家地区时间格式

4.6 MySQL 拼凑日期、时间

五、MySQL 时间戳（Timestamp）函数

5.1 MySQL 获得当前时间戳

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

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

六、MySQL 时区（timezone）转换函数

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

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

### 1.1 获得当前日期 + 时间（date + time） ###

函数：`now()`

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

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

current_timestamp()
    current_timestamp
    localtime()
    localtime
    localtimestamp — (v4.0.6)
    localtimestamp() — (v4.0.6)
    
    mysql> select localtime();
    +———————+
    | localtime()         |
    +———————+
    | 2015-07-07 17:01:38 |
    +———————+
    
    mysql> select localtime;
    +———————+
    | localtime           |
    +———————+
    | 2015-07-07 17:01:41 |
    +———————+
    ————————————————

这些日期时间函数，都等同于 now()。

鉴于 now() 函数简短易记，建议总是使用 now() 来替代上面列出的函数。

### 1.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 function executes。

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

### 1.3 获得当前日期（date） ###

函数：`curdate()`

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

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

current_date()
    current_date

### 1.4 获得当前时间（time） ###

函数：`curtime()`

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

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

current_time()
    current_time

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

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

### 2.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.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
    ————————————————

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

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

### 2.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 天。

### 2.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)。

MySQL weekday()函数和dayofweek() 类似，都是返回“某天”在一周中的位置。不同点在于参考的标准：

*  weekday：(0 = Monday, 1 = Tuesday, …, 6 = Sunday)；
 *  dayofweek：（1 = Sunday, 2 = Monday, …, 7 = Saturday）

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

### 2.5 MySQL 返回星期和月份名称 ###

函数：dayname(), monthname()

set @dt = ‘2008-08-08′;
    
    select dayname(@dt); — Friday
    select monthname(@dt); — August

思考，如何返回中文的名称呢？

### 2.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 日期时间计算函数 ##

### 3.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()。

### 3.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.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 中，这两个日期函数，一般情况下很少用到。

### 3.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 日期转换函数、时间转换函数 ##

### 4.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′

### 4.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′

### 4.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.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) 函数的 一个逆转换。

### 4.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() 函数在实际中用到机会的比较少。*

### 4.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）函数 ##

### 5.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 |
    +———————+———————+

### 5.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′
    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′

### 5.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
    select timestamp(‘2008-08-08 08:00:00′, ’10 01:01:01′); — 2008-08-18 09: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-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
    
    select timediff(’23:40:00′, ‘ 18:30:00′); — 两时间相减
    SELECT substring( timediff(’23:40:00′, ‘ 18:30:00′),1,5) —-“05：10”相减返回小时：分钟
    select datediff(‘2008-08-08′, ‘2008-08-01′); — 7      —–两日期相减
    select TO_DAYS(‘2008-09-08′)-TO_DAYS(‘2008-08-08′)     —–两日期相减
    SELECT substring( ‘2009-06-17 10:00:00′,   1,   10   )   —-从datetime中提取“日期”

时间戳是从1970年1月1日开始到目标时间所经过的秒数.  
可以进行两个datetime时间间隔的运算