MySQL函数-日期和时间函数

青旅半醒 2023-06-11 10:28 8阅读 0赞

时间函数对应mysql版本5.7,低版本未测

**1、获取当前日期的函数，返回格式"YYYY-MM-DD"**

mysql> select curdate(),current_date();
    +------------+----------------+
    | curdate()  | current_date() |
    +------------+----------------+
    | 2019-10-30 | 2019-10-30     |
    +------------+----------------+
    1 row in set (0.05 sec)

**2、获取当前日期的函数，返回格式"YYYY-MM-DD HH:MM:SS"**

mysql> select now(),sysdate(),current_timestamp(),localtime();
    +---------------------+---------------------+---------------------+---------------------+
    | now()               | sysdate()           | current_timestamp() | localtime()         |
    +---------------------+---------------------+---------------------+---------------------+
    | 2019-10-30 14:03:46 | 2019-10-30 14:03:46 | 2019-10-30 14:03:46 | 2019-10-30 14:03:46 |
    +---------------------+---------------------+---------------------+---------------------+
    1 row in set (0.11 sec)

**3、UNIX时间戳函数**

mysql> select unix_timestamp(),unix_timestamp(now()),now();
    +------------------+-----------------------+---------------------+
    | unix_timestamp() | unix_timestamp(now()) | now()               |
    +------------------+-----------------------+---------------------+
    |       1572418048 |            1572418048 | 2019-10-30 14:47:28 |
    +------------------+-----------------------+---------------------+
    1 row in set (0.11 sec)

**from\_unixtime与unix\_timestamp为互反函数**

mysql> select from_unixtime(1572418048);
    +---------------------------+
    | from_unixtime(1572418048) |
    +---------------------------+
    | 2019-10-30 14:47:28       |
    +---------------------------+
    1 row in set (0.10 sec)

**4、返回UTC日期和时间的函数**

mysql> select utc_date(),utc_time();
    +------------+------------+
    | utc_date() | utc_time() |
    +------------+------------+
    | 2019-10-30 | 06:56:44   |
    +------------+------------+
    1 row in set (0.09 sec)

**5、获取月份的函数**

MONTH(date)函数返回对应的月份，MONTHNAME(date)返回月份对应的英文

mysql> select month('2019-10-30'),monthname('2019-10-30');
    +---------------------+-------------------------+
    | month('2019-10-30') | monthname('2019-10-30') |
    +---------------------+-------------------------+
    |                  10 | October                 |
    +---------------------+-------------------------+
    1 row in set (0.09 sec)

**6、获取星期的函数**

dayname(d)函数返回d对应的工作日的英文名称

dayofweek(d)返回日期所在周的索引

mysql> select dayname('2019-10-30'),dayofweek('2019-10-30');
    +-----------------------+-------------------------+
    | dayname('2019-10-30') | dayofweek('2019-10-30') |
    +-----------------------+-------------------------+
    | Wednesday             |                       4 |
    +-----------------------+-------------------------+
    1 row in set (0.10 sec)

**7、获取星期数的函数**

week(d,default\_week\_format)默认值是0，指定一周的第一天为周日，1则指定一周的第一天为周一

weekofyear(d)某天位于一年中的第几周，相当于week(d,3)

mysql> select week('2019-10-30'),weekofyear('2019-10-30');
    +--------------------+--------------------------+
    | week('2019-10-30') | weekofyear('2019-10-30') |
    +--------------------+--------------------------+
    |                 43 |                       44 |
    +--------------------+--------------------------+
    1 row in set (0.13 sec)
    
    mysql> select week('2019-10-30',1),weekofyear('2019-10-30');
    +----------------------+--------------------------+
    | week('2019-10-30',1) | weekofyear('2019-10-30') |
    +----------------------+--------------------------+
    |                   44 |                       44 |
    +----------------------+--------------------------+
    1 row in set (0.09 sec)

**8、获取天数的函数**

dayofyear(d) 一年中的第几天

dayofmonth(d)一个月中的第几天

mysql> select dayofmonth('2019-10-30'),dayofyear('2019-10-30');
    +--------------------------+-------------------------+
    | dayofmonth('2019-10-30') | dayofyear('2019-10-30') |
    +--------------------------+-------------------------+
    |                       30 |                     303 |
    +--------------------------+-------------------------+
    1 row in set (0.09 sec)

**9、获取年份、季度、小时、分钟和秒钟的函数**

mysql> select year('2019-10-30 14:47:28'),quarter('2019-10-30 14:47:28'),minute('2019-10-30 14:47:28'),second('2019-10-30 14:47:28');
    +-----------------------------+--------------------------------+-------------------------------+-------------------------------+
    | year('2019-10-30 14:47:28') | quarter('2019-10-30 14:47:28') | minute('2019-10-30 14:47:28') | second('2019-10-30 14:47:28') |
    +-----------------------------+--------------------------------+-------------------------------+-------------------------------+
    |                        2019 |                              4 |                            47 |                            28 |
    +-----------------------------+--------------------------------+-------------------------------+-------------------------------+
    1 row in set (0.11 sec)

**10、时间和秒钟转换的函数**

time\_to\_sec(time)

sec\_to\_time(second)

mysql> select time_to_sec('14:47:28'),sec_to_time(53248);
    +-------------------------+--------------------+
    | time_to_sec('14:47:28') | sec_to_time(53248) |
    +-------------------------+--------------------+
    |                   53248 | 14:47:28           |
    +-------------------------+--------------------+
    1 row in set (0.10 sec)

**11、计算日期和时间的函数**

date\_add(date,interval expr type)

adddate(date,interval expr type)

date\_sub(date,interval expr type)

subdate(date,interval expr type)

addtime(date,expr)

subtime(date,expr)

datediff() 两个日期的间隔天数

<table style="width:500px;">  计算日期和时间的格式  
 <thead> 
  <tr> 
   <th>type值</th> 
   <th>预期的expr格式</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>MICROSECOND</td> 
   <td>MICROSECONDS</td> 
  </tr> 
  <tr> 
   <td>SECOND</td> 
   <td>SECONDS</td> 
  </tr> 
  <tr> 
   <td>MINUTE</td> 
   <td>MINUTES</td> 
  </tr> 
  <tr> 
   <td> <p>HOUR</p> </td> 
   <td>HOURS</td> 
  </tr> 
  <tr> 
   <td>DAY</td> 
   <td>DAYS</td> 
  </tr> 
  <tr> 
   <td>WEEK</td> 
   <td>WEEKS</td> 
  </tr> 
  <tr> 
   <td>MONTH</td> 
   <td>MONTHS</td> 
  </tr> 
  <tr> 
   <td>QUARTER</td> 
   <td>QUARTERS</td> 
  </tr> 
  <tr> 
   <td>YEAR</td> 
   <td>YEARS</td> 
  </tr> 
  <tr> 
   <td> <p>SECOND_MICROSECOND</p> </td> 
   <td>'SECONDS.MICROSECONDS'</td> 
  </tr> 
  <tr> 
   <td>MINUTE_MICROSECOND</td> 
   <td>'MINUTES.MICROSECONDS'</td> 
  </tr> 
  <tr> 
   <td>MINUTE_SECOND</td> 
   <td>'MINUTES:SECONDS'</td> 
  </tr> 
  <tr> 
   <td>HOUR_MICROSECOND</td> 
   <td>'HOUR.MICROSECONDS'</td> 
  </tr> 
  <tr> 
   <td>HOUR_SECOND</td> 
   <td>'HOURS:MINUTES:SECONDS'</td> 
  </tr> 
  <tr> 
   <td>HOUR_MINUTE</td> 
   <td>'HOURS:MINUTES'</td> 
  </tr> 
  <tr> 
   <td>DAY_MICROSECOND</td> 
   <td>'DAYS:MICROSECONDS'</td> 
  </tr> 
  <tr> 
   <td>DAY_SECOND</td> 
   <td>'DAYS HOURS:MINUTES:SECONDS'</td> 
  </tr> 
  <tr> 
   <td>DAY_MINUTE</td> 
   <td>'DAYS HOURS:MINUTES'</td> 
  </tr> 
  <tr> 
   <td>DAY_HOUR</td> 
   <td>'DAYS HOURS'</td> 
  </tr> 
  <tr> 
   <td>YEAR_MONTH</td> 
   <td>'YEARS-MONTHS'</td> 
  </tr> 
 </tbody> 
</table>

举例：使用三个type值 day,second,year\_month 其他类比

mysql> select date_sub('2019-10-30',interval 10 day),date_sub('2019-10-30 14:47:28',interval 10 second),date_add('2019-10-30 14:47:28',interval '1_1' year_month);
    +----------------------------------------+----------------------------------------------------+-----------------------------------------------------------+
    | date_sub('2019-10-30',interval 10 day) | date_sub('2019-10-30 14:47:28',interval 10 second) | date_add('2019-10-30 14:47:28',interval '1_1' year_month) |
    +----------------------------------------+----------------------------------------------------+-----------------------------------------------------------+
    | 2019-10-20                             | 2019-10-30 14:47:18                                | 2020-11-30 14:47:28                                       |
    +----------------------------------------+----------------------------------------------------+-----------------------------------------------------------+

addtime

mysql> select addtime('2019-10-30 14:47:28','1:1:1'),addtime('14:47:28','1:1:1');
    +----------------------------------------+-----------------------------+
    | addtime('2019-10-30 14:47:28','1:1:1') | addtime('14:47:28','1:1:1') |
    +----------------------------------------+-----------------------------+
    | 2019-10-30 15:48:29                    | 15:48:29                    |
    +----------------------------------------+-----------------------------+
    1 row in set (0.09 sec)

datediff

mysql> select datediff('2019-10-30 14:47:28','2019-09-23 10:27:18');
    +-------------------------------------------------------+
    | datediff('2019-10-30 14:47:28','2019-09-23 10:27:18') |
    +-------------------------------------------------------+
    |                                                    37 |
    +-------------------------------------------------------+
    1 row in set (0.10 sec)

**12、将日期时间格式化的函数**

date\_format(date,format)

<table style="width:500px;">  DATE_FORMAT时间日期格式  
 <thead> 
  <tr> 
   <th>说明符</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>%a</td> 
   <td>工作日的缩写名称（Sun...Sat）</td> 
  </tr> 
  <tr> 
   <td>%b</td> 
   <td>月份的缩写名称（Jan...Dec）</td> 
  </tr> 
  <tr> 
   <td>%c</td> 
   <td>月份、数字形式</td> 
  </tr> 
  <tr> 
   <td>%D</td> 
   <td>带有英语后缀的该月日期</td> 
  </tr> 
  <tr> 
   <td>%d</td> 
   <td>该月日期，数字形式</td> 
  </tr> 
  <tr> 
   <td>%e</td> 
   <td>该月日期，数字形式</td> 
  </tr> 
  <tr> 
   <td>%f</td> 
   <td>微妙</td> 
  </tr> 
  <tr> 
   <td>%H</td> 
   <td>以2位数表示24小时</td> 
  </tr> 
  <tr> 
   <td>%h</td> 
   <td>以2位数表示12小时</td> 
  </tr> 
  <tr> 
   <td>%i</td> 
   <td>分钟，数字形式</td> 
  </tr> 
  <tr> 
   <td>%j</td> 
   <td>一年中的天数</td> 
  </tr> 
  <tr> 
   <td>%k</td> 
   <td>以24小时表示时间</td> 
  </tr> 
  <tr> 
   <td>%l</td> 
   <td>以12小时表示时间</td> 
  </tr> 
  <tr> 
   <td>%M</td> 
   <td>月份名称，英文</td> 
  </tr> 
  <tr> 
   <td>%m</td> 
   <td>月份名称，数字形式</td> 
  </tr> 
  <tr> 
   <td>%p</td> 
   <td>上午AM 或者下午PM</td> 
  </tr> 
  <tr> 
   <td>%r</td> 
   <td>时间12小时制</td> 
  </tr> 
  <tr> 
   <td>%S,%s</td> 
   <td>以数字形式表示秒</td> 
  </tr> 
  <tr> 
   <td>%W</td> 
   <td>工作日名称，中文</td> 
  </tr> 
  <tr> 
   <td>%w</td> 
   <td>一周的工作日，0表示周日</td> 
  </tr> 
  <tr> 
   <td>%Y</td> 
   <td>四位数表示年份</td> 
  </tr> 
  <tr> 
   <td>%y</td> 
   <td>两位数表示年份</td> 
  </tr> 
  <tr> 
   <td>%%</td> 
   <td>‘%’文字字符</td> 
  </tr> 
 </tbody> 
</table>

mysql> select date_format('2019-10-30 14:47:28','%a %b %c %d %M %m %Y %y');
    +--------------------------------------------------------------+
    | date_format('2019-10-30 14:47:28','%a %b %c %d %M %m %Y %y') |
    +--------------------------------------------------------------+
    | Wed Oct 10 30 October 10 2019 19                             |
    +--------------------------------------------------------------+
    1 row in set (0.08 sec)