mysql常用日期时间/数值函数

傷城~ 2022-02-18 19:15 378阅读 0赞

author：skate  
time:2013/05/16

**1.日期时间函数**

时间转化秒函数:time\_to\_sec  
mysql> select time\_to\_sec('01:01:01');  
\+-------------------------+  
| time\_to\_sec('01:01:01') |  
\+-------------------------+  
|                    3661 |  
\+-------------------------+  
1 row in set (0.00 sec)

秒转化时间函数:sec\_to\_time  
mysql> select sec\_to\_time(3661);  
\+-------------------+  
| sec\_to\_time(3661) |  
\+-------------------+  
| 01:01:01          |  
\+-------------------+  
1 row in set (0.00 sec)

日期转为天数函数:to\_days  
mysql> select to\_days('0000-00-00');  
\+-----------------------+  
| to\_days('0000-00-00') |  
\+-----------------------+  
|                  NULL |  
\+-----------------------+  
1 row in set, 1 warning (0.01 sec)

mysql> select to\_days('0001-01-01');  
\+-----------------------+  
| to\_days('0001-01-01') |  
\+-----------------------+  
|                   366 |  
\+-----------------------+  
1 row in set (0.00 sec)

天数转化日期函数：from\_days  
mysql> select from\_days(0);  
\+--------------+  
| from\_days(0) |  
\+--------------+  
| 0000-00-00   |  
\+--------------+  
1 row in set (0.00 sec)

mysql> select from\_days(366);  
\+----------------+  
| from\_days(366) |  
\+----------------+  
| 0001-01-01     |  
\+----------------+  
1 row in set (0.00 sec)

字符串转换为日期函数：str\_to\_date  
mysql> select str\_to\_date('2013-01-01 01:21:01','%Y-%m-%d %H:%i:%s');  
\+--------------------------------------------------------+  
| str\_to\_date('2013-01-01 01:21:01','%Y-%m-%d %H:%i:%s') |  
\+--------------------------------------------------------+  
| 2013-01-01 01:21:01                                    |  
\+--------------------------------------------------------+  
1 row in set (0.00 sec)

日期转换为字符串函数：date\_format  
mysql> select date\_format('2013-01-01 01:21:01','%Y%m%d %H%i%s');  
\+----------------------------------------------------+  
| date\_format('2013-01-01 01:21:01','%Y%m%d %H%i%s') |  
\+----------------------------------------------------+  
| 20130101 012101                                    |  
\+----------------------------------------------------+  
1 row in set (0.00 sec)

时间转换为字符串函数：time\_format  
mysql> select time\_format('01:21:01','%H%i%s');  
\+----------------------------------+  
| time\_format('01:21:01','%H%i%s') |  
\+----------------------------------+  
| 012101                           |  
\+----------------------------------+  
1 row in set (0.00 sec)

**说明：**

日期时间格式参数如下：

%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 date(now());  
\+-------------+  
| date(now()) |  
\+-------------+  
| 2013-05-16  |  
\+-------------+  
1 row in set (0.00 sec)

返回表达式的星期索引(0=星期一，1=星期二, ……6= 星期天)。  
mysql> select weekday(now());  
\+----------------+  
| weekday(now()) |  
\+----------------+  
|              3 |  
\+----------------+  
1 row in set (0.00 sec)

返回表达式是一年的第几周  
mysql> select week(now());  
\+-------------+  
| week(now()) |  
\+-------------+  
|          19 |  
\+-------------+  
1 row in set (0.00 sec)

WEEK()允许指定星期是否开始于星期天或星期一。如果第二个参数是0，星期从星期天开始，如果第二个参数是1， 从星期一开始，如下所示：  
mysql> select week(now(),0);  
\+---------------+  
| week(now(),0) |  
\+---------------+  
|            19 |  
\+---------------+  
1 row in set (0.00 sec)

mysql> select week(now(),1);  
\+---------------+  
| week(now(),1) |  
\+---------------+  
|            20 |  
\+---------------+  
1 row in set (0.00 sec)

返回表达式一年中季度  
mysql> select quarter(now());  
\+----------------+  
| quarter(now()) |  
\+----------------+  
|              2 |  
\+----------------+  
1 row in set (0.00 sec)

返回表达式一周的第一天  
mysql> select dayofweek(now());  
\+------------------+  
| dayofweek(now()) |  
\+------------------+  
|                5 |  
\+------------------+  
1 row in set (0.00 sec)

返回表达式一个月的第几天  
mysql> select dayofmonth(now());  
\+-------------------+  
| dayofmonth(now()) |  
\+-------------------+  
|                16 |  
\+-------------------+  
1 row in set (0.00 sec)

返回表达式一年的第几天  
mysql> select dayofyear(now());  
\+------------------+  
| dayofyear(now()) |  
\+------------------+  
|              136 |  
\+------------------+  
1 row in set (0.00 sec)

返回表达式的星期名字  
mysql> select dayname(now());  
\+----------------+  
| dayname(now()) |  
\+----------------+  
| Thursday       |  
\+----------------+  
1 row in set (0.00 sec)

返回表达式月份的名字  
mysql> select monthname(now());  
\+------------------+  
| monthname(now()) |  
\+------------------+  
| May              |  
\+------------------+  
1 row in set (0.00 sec)

mysql>

提取表达式的年份  
mysql> select year(now());  
\+-------------+  
| year(now()) |  
\+-------------+  
|        2013 |  
\+-------------+  
1 row in set (0.00 sec)

提取表达式的月份  
mysql> select month(now());  
\+--------------+  
| month(now()) |  
\+--------------+  
|            5 |  
\+--------------+  
1 row in set (0.01 sec)

提取表达式的天数  
mysql> select day(now());  
\+------------+  
| day(now()) |  
\+------------+  
|         16 |  
\+------------+  
1 row in set (0.00 sec)

提取表达式的小时  
mysql> select hour(now());  
\+-------------+  
| hour(now()) |  
\+-------------+  
|          16 |  
\+-------------+  
1 row in set (0.00 sec)

提取表达式的分钟  
mysql> select minute(now());  
\+---------------+  
| minute(now()) |  
\+---------------+  
|            31 |  
\+---------------+  
1 row in set (0.00 sec)

提取表达式的秒数  
mysql> select second(now());  
\+---------------+  
| second(now()) |  
\+---------------+  
|            34 |  
\+---------------+  
1 row in set (0.00 sec)

将当前日期按照'YYYY-MM-DD' 或YYYYMMDD 格式的值返回，具体格式根据函数用在字符串或是数字语境中而定。  
mysql> select curdate();  
\+------------+  
| curdate()  |  
\+------------+  
| 2013-05-16 |  
\+------------+  
1 row in set (0.00 sec)

mysql> select curdate()+1;  
\+-------------+  
| curdate()+1 |  
\+-------------+  
|    20130517 |  
\+-------------+  
1 row in set (0.00 sec)

将当前时间以'HH:MM:SS'或 HHMMSS的格式返回，具体格式根据函数用在字符串或是数字语境中而定。  
mysql> select curtime();  
\+-----------+  
| curtime() |  
\+-----------+  
| 16:43:10  |  
\+-----------+  
1 row in set (0.00 sec)

mysql> select curtime()+1;  
\+---------------+  
| curtime()+1   |  
\+---------------+  
| 164420.000000 |  
\+---------------+  
1 row in set (0.00 sec)

获取当前日期时间：sysdate(),now()  
mysql> select sysdate(),sleep(2),sysdate();  
\+---------------------+----------+---------------------+  
| sysdate()           | sleep(2) | sysdate()           |  
\+---------------------+----------+---------------------+  
| 2013-05-16 17:16:04 |        0 | 2013-05-16 17:16:06 |  
\+---------------------+----------+---------------------+  
1 row in set (2.00 sec)

mysql> select now(),sleep(2),now();  
\+---------------------+----------+---------------------+  
| now()               | sleep(2) | now()               |  
\+---------------------+----------+---------------------+  
| 2013-05-16 17:16:18 |        0 | 2013-05-16 17:16:18 |  
\+---------------------+----------+---------------------+  
1 row in set (2.00 sec)  
从上面可以看到sysdate和now的区别，now表示语句开始的时间，而sysdate实时的获取时间

将当前日期按照'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式的值返回，具体格式根据函数用在字符串或是数字语境中而定。  
mysql> select current\_timestamp;  
\+---------------------+  
| current\_timestamp   |  
\+---------------------+  
| 2013-05-16 17:19:51 |  
\+---------------------+  
1 row in set (0.00 sec)

mysql> select current\_timestamp+1;  
\+-----------------------+  
| current\_timestamp+1   |  
\+-----------------------+  
| 20130516172008.000000 |  
\+-----------------------+  
1 row in set (0.00 sec)

unix\_timestamp()，unix\_timestamp(date)  
如果没有参数调用，返回一个Unix时间戳记(从'1970-01-01 00:00:00'GMT开始的秒数)。如果UNIX\_TIMESTAMP()用一  
个date参数被调用，它返回从'1970-01-01 00:00:00' GMT开始的秒数值。date可以是一个DATE字符串、一个DATETIME  
字符串、一个TIMESTAMP或以YYMMDD或YYYYMMDD格式的本地时间的一个数字。  
mysql> select unix\_timestamp();  
\+------------------+  
| unix\_timestamp() |  
\+------------------+  
|       1368696216 |  
\+------------------+  
1 row in set (0.00 sec)

mysql> select unix\_timestamp('2013-05-16 01:01:01');  
\+---------------------------------------+  
| unix\_timestamp('2013-05-16 01:01:01') |  
\+---------------------------------------+  
|                            1368637261 |  
\+---------------------------------------+  
1 row in set (0.00 sec)

mysql>

FROM\_UNIXTIME(unix\_timestamp)  
以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式返回unix\_timestamp参数所表示的值，具体格式根据函数用在字符串或是数字语境中而定

mysql> select from\_unixtime(1368637261);  
\+---------------------------+  
| from\_unixtime(1368637261) |  
\+---------------------------+  
| 2013-05-16 01:01:01       |  
\+---------------------------+  
1 row in set (0.00 sec)

mysql> select from\_unixtime(1368637261)+1;  
\+-----------------------------+  
| from\_unixtime(1368637261)+1 |  
\+-----------------------------+  
|       20130516010102.000000 |  
\+-----------------------------+  
1 row in set (0.00 sec)

mysql> select from\_unixtime(1368637261,'%Y-%m-%d %h:%i:%s');  
\+-----------------------------------------------+  
| from\_unixtime(1368637261,'%Y-%m-%d %h:%i:%s') |  
\+-----------------------------------------------+  
| 2013-05-16 01:01:01                           |  
\+-----------------------------------------------+  
1 row in set (0.00 sec)

返回表达式所在月的最后一天  
mysql> select last\_day(now());  
\+-----------------+  
| last\_day(now()) |  
\+-----------------+  
| 2013-05-31      |  
\+-----------------+  
1 row in set (0.00 sec)

日期加减运算  
DATE\_ADD(date,INTERVAL expr type) --加法  
DATE\_SUB(date,INTERVAL expr type) --减法

mysql> select date\_add('2013-05-16 01:01:01',interval 1 second);  
\+---------------------------------------------------+  
| date\_add('2013-05-16 01:01:01',interval 1 second) |  
\+---------------------------------------------------+  
| 2013-05-16 01:01:02                               |  
\+---------------------------------------------------+  
1 row in set (0.00 sec)

mysql> select date\_add('2013-05-16 01:01:01',interval 1 day);  
\+------------------------------------------------+  
| date\_add('2013-05-16 01:01:01',interval 1 day) |  
\+------------------------------------------------+  
| 2013-05-17 01:01:01                            |  
\+------------------------------------------------+  
1 row in set (0.00 sec)

mysql> select date\_add('2013-05-16 01:01:01',interval 1 minute);  
\+---------------------------------------------------+  
| date\_add('2013-05-16 01:01:01',interval 1 minute) |  
\+---------------------------------------------------+  
| 2013-05-16 01:02:01                               |  
\+---------------------------------------------------+  
1 row in set (0.00 sec)

mysql> select date\_add('2013-05-16 01:01:01',interval 1 hour);  
\+-------------------------------------------------+  
| date\_add('2013-05-16 01:01:01',interval 1 hour) |  
\+-------------------------------------------------+  
| 2013-05-16 02:01:01                             |  
\+-------------------------------------------------+  
1 row in set (0.00 sec)

mysql> select date\_add('2013-05-16 01:01:01',interval '1:1' minute\_second);  
\+--------------------------------------------------------------+  
| date\_add('2013-05-16 01:01:01',interval '1:1' minute\_second) |  
\+--------------------------------------------------------------+  
| 2013-05-16 01:02:02                                          |  
\+--------------------------------------------------------------+  
1 row in set (0.00 sec)

mysql> select date\_add('2013-05-16 01:01:01', interval '1 1:1:1' day\_second);  
\+----------------------------------------------------------------+  
| date\_add('2013-05-16 01:01:01', interval '1 1:1:1' day\_second) |  
\+----------------------------------------------------------------+  
| 2013-05-17 02:02:02                                            |  
\+----------------------------------------------------------------+  
1 row in set (0.00 sec)

**type值格式：**  
SECOND 秒 SECONDS  
MINUTE 分钟 MINUTES  
HOUR 时间 HOURS  
DAY 天 DAYS  
MONTH 月 MONTHS  
YEAR 年 YEARS  
MINUTE\_SECOND 分钟和秒 "MINUTES:SECONDS"  
HOUR\_MINUTE 小时和分钟 "HOURS:MINUTES"  
DAY\_HOUR 天和小时 "DAYS HOURS"  
YEAR\_MONTH 年和月 "YEARS-MONTHS"  
HOUR\_SECOND 小时, 分钟， "HOURS:MINUTES:SECONDS"  
DAY\_MINUTE 天, 小时, 分钟 "DAYS HOURS:MINUTES"  
DAY\_SECOND 天, 小时, 分钟, 秒 "DAYS HOURS:MINUTES:SECONDS"

DATEDIFF(expr, expr2)  
返回起始时间 expr和结束时间expr2之间的天数。Expr和expr2为日期或 date-and-time 表达式。计算中只用到这些值的日期部分。

mysql> select datediff('2013-05-16 06:01:01', '2013-05-17 01:01:01');  
\+--------------------------------------------------------+  
| datediff('2013-05-16 06:01:01', '2013-05-17 01:01:01') |  
\+--------------------------------------------------------+  
|                                                     -1 |  
\+--------------------------------------------------------+  
1 row in set (0.00 sec)

**表示日期时间的数据类型：**  
date  
time  
year  
datetime  
timestamp

在使用日期时间数据比较时常用如下

mysql> select now();  
\+---------------------+  
| now()               |  
\+---------------------+  
| 2013-05-14 17:10:26 |  
\+---------------------+  
1 row in set (0.00 sec)

mysql> select current\_timestamp;  
\+---------------------+  
| current\_timestamp   |  
\+---------------------+  
| 2013-05-14 17:10:49 |  
\+---------------------+  
1 row in set (0.00 sec)

mysql> select \* from tab where createtime between str\_to\_date('2013-05-14 00:00:00','%Y-%m-%d %H:%i:%s') and str\_to\_date('2013-05-15 00:00:00','%Y-%m-%d %H:%i:%s')  
    -> ;  
\+------+---------------------+  
| name | createtime          |  
\+------+---------------------+  
| aaaa | 2013-05-14 17:20:19 |  
\+------+---------------------+  
1 row in set (0.00 sec)

mysql>

2.数值函数

ABS(X) ：返回表达式X的绝对值  
mysql> select abs(-2);  
\+---------+  
| abs(-2) |  
\+---------+  
|       2 |  
\+---------+  
1 row in set (0.00 sec)

FLOOR(X) ：返回不大于X的最大整数值  
mysql> select floor(-2.45);  
\+--------------+  
| floor(-2.45) |  
\+--------------+  
|           -3 |  
\+--------------+  
1 row in set (0.00 sec)

MOD(N,M)：模操作，返回N被M除后的余数。  
mysql> select mod(3,2);  
\+----------+  
| mod(3,2) |  
\+----------+  
|        1 |  
\+----------+  
1 row in set (0.00 sec)

RAND()/RAND(N) ：返回一个随机浮点值数a，范围在 0 到1 之间 (即, 其范围为 0 ≤ a ≤ 1.0)。若已指定一个整数参数 N，则它被用作种子值，用来产生重复序列。  
mysql> select rand();  
\+-------------------+  
| rand()            |  
\+-------------------+  
| 0.294932589209576 |  
\+-------------------+  
1 row in set (0.00 sec)

mysql> select rand(2);  
\+-------------------+  
| rand(2)           |  
\+-------------------+  
| 0.655586646549019 |  
\+-------------------+  
1 row in set (0.00 sec)

ROUND(X)/ROUND(X,D) ：返回参数X, 其值接近于最近似的整数。在有两个参数的情况下，返回 X ，其值保留到小数点后D位，而第D位的保留方式为四舍五入。若要接保留X值小数点左边的D位，可将D设为负值。  
mysql> select round(2.4 );  
\+-------------+  
| round(2.4 ) |  
\+-------------+  
|           2 |  
\+-------------+  
1 row in set (0.00 sec)

mysql> select round(2.432,2 );  
\+-----------------+  
| round(2.432,2 ) |  
\+-----------------+  
|            2.43 |  
\+-----------------+  
1 row in set (0.00 sec)

mysql> select round(12.432,-1 );  
\+-------------------+  
| round(12.432,-1 ) |  
\+-------------------+  
|                10 |  
\+-------------------+  
1 row in set (0.00 sec)

\-------续------