欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

Mysql的时间函数

程序员文章站 2024-01-28 11:58:58
...

为什么80%的码农都做不了架构师？>>>   Mysql的时间函数

mysql的时间函数，放着查看 
1.本周内的第几天，从周日开始
mysql> select dayofweek('2015-05-25');
+-------------------------+
| dayofweek('2015-05-25') |
+-------------------------+
|                       2 |
+-------------------------+
1 row in set (0.00 sec)

2.本月内的第几天
mysql> select dayofmonth('2015-05-25');
+--------------------------+
| dayofmonth('2015-05-25') |
+--------------------------+
|                       25 |
+--------------------------+
1 row in set (0.00 sec)

3.本年内的第几天
mysql> select dayofyear('2015-05-25');
+-------------------------+
| dayofyear('2015-05-25') |
+-------------------------+
|                     145 |
+-------------------------+
1 row in set (0.00 sec)

4.获取周几
mysql> select dayname('2015-05-26');
+-----------------------+
| dayname('2015-05-26') |
+-----------------------+
| Tuesday               |
+-----------------------+
1 row in set (0.04 sec)

5.获取月份
mysql> select month('2015-05-26'),monthname('2015-05-26');
+---------------------+-------------------------+
| month('2015-05-26') | monthname('2015-05-26') |
+---------------------+-------------------------+
|                   5 | May                     |
+---------------------+-------------------------+
1 row in set (0.00 sec)

6.获取第几周
mysql> select week('2015-05-26');
+--------------------+
| week('2015-05-26') |
+--------------------+
|                 21 |
+--------------------+
1 row in set (0.00 sec)

7.获取第几季度
mysql> select quarter('2015-06-26');
+-----------------------+
| quarter('2015-06-26') |
+-----------------------+
|                     2 |
+-----------------------+
1 row in set (0.00 sec)

8.查看年份
mysql> select year('2015-09-26');
+--------------------+
| year('2015-09-26') |
+--------------------+
|               2015 |
+--------------------+
1 row in set (0.00 sec)

9.其他
mysql> select year('2015-05-26'),month('2015-05-26'),day('2015-05-26');
+--------------------+---------------------+-------------------+
| year('2015-05-26') | month('2015-05-26') | day('2015-05-26') |
+--------------------+---------------------+-------------------+
|               2015 |                   5 |                26 |
+--------------------+---------------------+-------------------+
1 row in set (0.01 sec)

mysql> select hour('10:05:03'),minute('10:05:03'),second('10:05:03');
+------------------+--------------------+--------------------+
| hour('10:05:03') | minute('10:05:03') | second('10:05:03') |
+------------------+--------------------+--------------------+
|               10 |                  5 |                  3 |
+------------------+--------------------+--------------------+
1 row in set (0.00 sec)

10.查看当前时间
mysql> select current_time(),sysdate(),current_timestamp(),now();
+----------------+---------------------+---------------------+---------------------+
| current_time() | sysdate()           | current_timestamp() | now()               |
+----------------+---------------------+---------------------+---------------------+
| 00:29:05       | 2015-05-26 00:29:05 | 2015-05-26 00:29:05 | 2015-05-26 00:29:05 |
+----------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

其他类似内置函数，now是跟session有关的，sysdate无关事务
current_timestamp(),
current_timestamp,
localtime(),
localtime,
localtimestamp,
localtimestamp()

11.unix时间戳转换
mysql> select unix_timestamp('2015-05-26 15:38:22'),unix_timestamp(now());
+---------------------------------------+-----------------------+
| unix_timestamp('2015-05-26 15:38:22') | unix_timestamp(now()) |
+---------------------------------------+-----------------------+
|                            1432679902 |            1432625945 |
+---------------------------------------+-----------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(1432679902),from_unixtime(1432625945);
+---------------------------+---------------------------+
| from_unixtime(1432679902) | from_unixtime(1432625945) |
+---------------------------+---------------------------+
| 2015-05-26 15:38:22       | 2015-05-26 00:39:05       |
+---------------------------+---------------------------+
1 row in set (0.00 sec)

12.format格式
%M 月名字(January……December)
%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), 这里星期一是星期的第一天
%% 字符%

mysql>  select now(),DATE_FORMAT(now(),'%M %D %Y %b %a %d %e %j %i');
+---------------------+-------------------------------------------------+
| now()               | DATE_FORMAT(now(),'%M %D %Y %b %a %d %e %j %i') |
+---------------------+-------------------------------------------------+
| 2015-05-26 01:38:17 | May 26th 2015 May Tue 26 26 146 38              |
+---------------------+-------------------------------------------------+
1 row in set (0.00 sec)

13.时间运算
mysql> select now(),date_add(now(),interval 11 second);
+---------------------+------------------------------------+
| now()               | date_add(now(),interval 11 second) |
+---------------------+------------------------------------+
| 2015-05-27 00:22:20 | 2015-05-27 00:22:31                |
+---------------------+------------------------------------+
1 row in set (0.00 sec)

mysql> select now(),date_add(now(),interval 1 hour);
+---------------------+---------------------------------+
| now()               | date_add(now(),interval 1 hour) |
+---------------------+---------------------------------+
| 2015-05-27 00:23:26 | 2015-05-27 01:23:26             |
+---------------------+---------------------------------+
1 row in set (0.00 sec)

mysql> select now(),date_add(now(),interval 1 day);
+---------------------+--------------------------------+
| now()               | date_add(now(),interval 1 day) |
+---------------------+--------------------------------+
| 2015-05-27 00:22:28 | 2015-05-28 00:22:28            |
+---------------------+--------------------------------+
1 row in set (0.00 sec)

mysql> select now(),date_add(now(),interval 1 month);
+---------------------+----------------------------------+
| now()               | date_add(now(),interval 1 month) |
+---------------------+----------------------------------+
| 2015-05-27 00:23:57 | 2015-06-27 00:23:57              |
+---------------------+----------------------------------+
1 row in set (0.00 sec)

mysql> select now(),date_add(now(),interval -1 year);
+---------------------+----------------------------------+
| now()               | date_add(now(),interval -1 year) |
+---------------------+----------------------------------+
| 2015-05-27 00:24:34 | 2014-05-27 00:24:34              |
+---------------------+----------------------------------+
1 row in set (0.06 sec)

转载于:https://my.oschina.net/Kenyon/blog/482412

上一篇： vue将一个data/DataSource对象变量赋值给另一个，结果两个对象变量同时变化的解决办法【伸手党福利】

下一篇： Spring调用第三方Api实例

推荐阅读