mysql 时间和日期函数学习
时间：2022-03-14 13:47
mysql 时间和日期函数学习
为了方便使用了比较智能的mysql客户端工具mycli，关于这个工具的介绍可以在另一篇文章（）里了解！
1，now()函数，获得当前日期和时间
mysql root@localhost:(none)> SELECT NOW() +---------------------+ | NOW() | |---------------------| |2015-08-25 10:47:54 | +---------------------+
2，sysdate()函数，获得当前日期和时间
mysql root@localhost:(none)> SELECT SYSDATE() +---------------------+ | SYSDATE() | |---------------------| |2015-08-25 10:49:43 | +---------------------+
now()函数，和sysdate()函数作用类似，区别如下：now() 在执行开始时值就得到了， sysdate() 在函数执行时动态得到值
mysql root@localhost:(none)> SELECT NOW(),sleep(2),NOW() +---------------------+------------+---------------------+ | NOW() | sleep(2) | NOW() | |---------------------+------------+---------------------| | 2015-08-25 10:51:18 | 0 | 2015-08-25 10:51:18 | +---------------------+------------+---------------------+ mysql root@localhost:(none)> SELECT SYSDATE(),sleep(2),SYSDATE() +---------------------+------------+---------------------+ | SYSDATE() | sleep(2) | SYSDATE() | |---------------------+------------+---------------------| | 2015-08-25 10:52:01 | 0 | 2015-08-25 10:52:03 | +---------------------+------------+---------------------+
3，current_time(),curtime()函数，获得当前时间的函数
mysql root@localhost:(none)> SELECT CURRENT_TIME() +------------------+ | CURRENT_TIME() | |------------------| | 10:57:47 | +------------------+ mysql root@localhost:(none)> SELECT CURTIME() +-------------+ | CURTIME() | |-------------| | 10:57:55 | +-------------+
4，current_date(),curdate()函数，获得当前日期的函数
mysql root@localhost:(none)> SELECT CURRENT_DATE() +------------------+ | CURRENT_DATE() | |------------------| | 2015-08-25 | +------------------+ mysql root@localhost:(none)> SELECT CURDATE() +-------------+ | CURDATE() | |-------------| | 2015-08-25 | +-------------+
5,current_timestamp()函数，获得当前时间戳的函数
mysql root@localhost:(none)> SELECT CURRENT_TIMESTAMP() +-----------------------+ | CURRENT_TIMESTAMP() | |-----------------------| | 2015-08-25 11:00:21 | +-----------------------+
6，date_format(date,format)函数，日期格式转换函数，将日期转化为字符串形式
mysql root@localhost:(none)> SELECT curdate(),DATE_FORMAT(curdate(),‘%Y%m%d‘); +-------------+-----------------------------------+ | curdate() | DATE_FORMAT(curdate(),‘%Y%m%d‘) | |-------------+-----------------------------------| | 2015-08-25 | 20150825 | +-------------+-----------------------------------+
7，time_format(time,format)函数，时间转换函数，将时间转化为字符串形式
mysql root@localhost:(none)> SELECT curtime(),time_format(curtime(),‘%H%i%s‘) +-------------+-----------------------------------+ | curtime() | time_format(curtime(),‘%H%i%s‘)| |-------------+----------------------------------- | | 11:15:17 | 111517 | +-------------+-----------------------------------+
上面函数的逆函数str_to_date()函数
mysql root@localhost:(none)> SELECT str_to_date(‘20150311‘,‘%Y%m%d‘) +------------------------------------+ | str_to_date(‘20150311‘,‘%Y%m%d‘) | |------------------------------------| | 2015-03-11 | +------------------------------------+ mysql root@localhost:(none)> SELECT str_to_date(‘121409‘,‘%H%i%s‘) +----------------------------------+ | str_to_date(‘121409‘,‘%H%i%s‘) | |----------------------------------| | 12:14:09 | +----------------------------------+ mysql root@localhost:(none)> SELECT str_to_date(‘20150903120000‘,‘%Y%m%d%H%i%s‘) +------------------------------------------------+ | str_to_date(‘20150903120000‘,‘%Y%m%d%H%i%s‘) | |------------------------------------------------| | 2015-09-03 12:00:00 | +------------------------------------------------+
8，unix_timestamp()函数，将时间转化为时间戳的函数
mysql root@localhost:(none)> SELECT unix_timestamp() +--------------------+ | unix_timestamp() | |--------------------| | 1440474909 | +--------------------+ mysql root@localhost:(none)> SELECT unix_timestamp(‘2015-09-03‘) +--------------------------------+ | unix_timestamp(‘2015-09-03‘) | |--------------------------------| | 1441209600 | +--------------------------------+ mysql root@localhost:(none)> SELECT unix_timestamp(‘2015-09-03 01:00:00‘) +-----------------------------------------+ | unix_timestamp(‘2015-09-03 01:00:00‘) | |-----------------------------------------| | 1441213200 | +-----------------------------------------+ 9，from_unixtime(unix_timestamp)函数，将时间戳转换为时间字符串 mysql root@localhost:(none)> SELECT from_unixtime(1441209600) +-----------------------------+ | from_unixtime(1441209600) | |-----------------------------| | 2015-09-03 00:00:00 | +-----------------------------+ mysql root@localhost:(none)> SELECT from_unixtime(1441209600,‘%Y%m%d %H:%i:%s‘) +-----------------------------------------------+ | from_unixtime(1441209600,‘%Y%m%d %H:%i:%s‘) | |-----------------------------------------------| | 20150903 00:00:00 | +-----------------------------------------------+ 10,date_add()函数，日期计算函数 mysql root@localhost:(none)> SELECT date_add(now(),interval 1 year) +-----------------------------------+ | date_add(now(),interval 1 year) | |-----------------------------------| | 2016-08-25 13:53:16 | +-----------------------------------+ mysql root@localhost:(none)> SELECT date_add(now(),interval 1 month) +------------------------------------+ | date_add(now(),interval 1 month) | |------------------------------------| | 2015-09-25 13:54:38 | +------------------------------------+ mysql root@localhost:(none)> SELECT date_add(now(),interval 1 week) mysql root@localhost:(none)> SELECT date_add(now(),interval 1 day) mysql root@localhost:(none)> SELECT date_add(now(),interval 1 hour) mysql root@localhost:(none)> SELECT date_add(now(),interval 1 minute) mysql root@localhost:(none)> SELECT date_add(now(),interval 1 second) 同理 -1 指的是一年/月/日/天/时/分/秒以前 mysql root@localhost:(none)> SELECT date_add(now(),interval -1 year) +------------------------------------+ | date_add(now(),interval -1 year) | |------------------------------------| | 2014-08-25 13:57:46 | +------------------------------------+ 11,datediff(date1,date2),日期相减函数，返回的是天数 mysql root@localhost:(none)> SELECT datediff(‘2015-09-03‘,‘2015--8-25‘) +---------------------------------------+ | datediff(‘2015-09-03‘,‘2015--8-25‘) | |---------------------------------------| | 9 | +---------------------------------------+ mysql root@localhost:(none)> SELECT datediff(‘2015--8-25‘,‘2015-09-03‘) +---------------------------------------+ | datediff(‘2015--8-25‘,‘2015-09-03‘) | |---------------------------------------| | -9 | +---------------------------------------+ 12,timediff(time1,time2)，时间相减函数，返回的是时间差值 mysql root@localhost:(none)> SELECT timediff(‘12:37:33‘,‘12:35:32‘) +-----------------------------------+ | timediff(‘12:37:33‘,‘12:35:32‘) | |-----------------------------------| | 0:02:01 | +-----------------------------------+ mysql root@localhost:(none)> SELECT timediff(‘12:35:32‘,‘12:37:33‘) +-----------------------------------+ | timediff(‘12:35:32‘,‘12:37:33‘) | |-----------------------------------| | -1 day, 23:57:59 | +-----------------------------------+ 13，timestamp()时间戳转换，增，减函数 mysql root@localhost:(none)> select timestamp(‘2013-08-25‘); +---------------------------+ | timestamp(‘2013-08-25‘) | |---------------------------| | 2013-08-25 00:00:00 | +---------------------------+ mysql root@localhost:(none)> select timestamp(‘2013-08-25‘,‘01:00:00‘); +--------------------------------------+ | timestamp(‘2013-08-25‘,‘01:00:00‘) | |--------------------------------------| | 2013-08-25 01:00:00 | +--------------------------------------+ 14，timestampadd()，函数类似于 date_add()，强大于date_add() mysql root@localhost:(none)> select timestampdiff(year,‘2013-01-01‘,‘2015-01-01‘) +-------------------------------------------------+ | timestampdiff(year,‘2013-01-01‘,‘2015-01-01‘) | |-------------------------------------------------| | 2 | +-------------------------------------------------+ mysql root@localhost:(none)> select timestampdiff(month,‘2013-01-01‘,‘2015-03-01‘) +--------------------------------------------------+ | timestampdiff(month,‘2013-01-01‘,‘2015-03-01‘) | |--------------------------------------------------| | 26 | +--------------------------------------------------+ mysql root@localhost:(none)> select timestampdiff(day,‘2013-01-01‘,‘2015-03-01‘) +------------------------------------------------+ | timestampdiff(day,‘2013-01-01‘,‘2015-03-01‘) | |------------------------------------------------| | 789 | +------------------------------------------------+ mysql root@localhost:(none)> select timestampdiff(hour,‘2013-01-01‘,‘2015-03-01‘) +-------------------------------------------------+ | timestampdiff(hour,‘2013-01-01‘,‘2015-03-01‘) | |-------------------------------------------------| | 18936 | +-------------------------------------------------+ mysql root@localhost:(none)> select timestampdiff(second,‘2013-01-01‘,‘2015-03-01‘) +---------------------------------------------------+ | timestampdiff(second,‘2013-01-01‘,‘2015-03-01‘) | |---------------------------------------------------| | 68169600 | +---------------------------------------------------+
本文出自 “” 博客，请务必保留此出处
