【MYSQL】日期、 时间操作函数

日期和时间函数

函数	用法
CURDATE() ,CURRENT_DATE()	返回当前日期，只包含年、月、日
CURTIME(), CURRENT_TIME()	返回当前时间，只包含时、分、秒
NOW() / SYSDATE()/ CURRENT_TIMESTAMP() /LOCALTIME()/LOCALTIMESTAMP()	返回当前系统日期和时间
UTC_DATE()	返回UTC(世界标准时间)
UTC_TIME()	返回UTC(世界标准时间)时间

#4.1  获取当前日期、时间
SELECT CURDATE(),CURRENT_DATE(),CURTIME(),NOW(),SYSDATE(),
UTC_DATE(),UTC_TIME()
FROM DUAL;
/*
+------------+----------------+-----------+---------------------+---------------------+------------+------------+
| CURDATE()  | CURRENT_DATE() | CURTIME() | NOW()               | SYSDATE()           | UTC_DATE() | UTC_TIME() |
+------------+----------------+-----------+---------------------+---------------------+------------+------------+
| 2022-02-12 | 2022-02-12     | 18:59:18  | 2022-02-12 18:59:18 | 2022-02-12 18:59:18 | 2022-02-12 | 10:59:18   |
+------------+----------------+-----------+---------------------+---------------------+------------+------------+
*/
SELECT CURDATE(),CURDATE() + 0,CURTIME() + 0,NOW() + 0
FROM DUAL;
/*
+------------+---------------+---------------+----------------+
| CURDATE()  | CURDATE() + 0 | CURTIME() + 0 | NOW() + 0      |
+------------+---------------+---------------+----------------+
| 2022-02-12 |      20220212 |        190103 | 20220212190103 |
+------------+---------------+---------------+----------------+
*/

日期与时间戳的转换

函数	用法
UNIX_TIMESTAMP()	以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() ->1634348884
UNIX_TIMESTAMP(date)	将时间date以UNIX时间戳的形式返回。
FROM_UNIXTIME(timestamp)	将UNIX时间戳的时间转换为普通格式的时间

 

#4.2 日期与时间戳的转换
#UNIX_TIMESTAMP(): 以UNIX时间戳(毫秒数)的形式返回当前时间
SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP('2021-10-01 12:12:32'),
FROM_UNIXTIME(1635173853),FROM_UNIXTIME(1633061552)
FROM DUAL;
/*
+------------------+---------------------------------------+---------------------------+---------------------------+
| UNIX_TIMESTAMP() | UNIX_TIMESTAMP('2021-10-01 12:12:32') | FROM_UNIXTIME(1635173853) | FROM_UNIXTIME(1633061552) |
+------------------+---------------------------------------+---------------------------+---------------------------+
|       1644663805 |                            1633061552 | 2021-10-25 22:57:33       | 2021-10-01 12:12:32       |
+------------------+---------------------------------------+---------------------------+---------------------------+
*/

获取月份、星期、星期数、天数等函数

函数	用法
YEAR(date)/MONTH(date) / DAY(date)	返回具体的日期值
HOUR(time)/ MINUTE(time) /SECOND(time)	返回具体的时间值
MONTHNAME(date)	返回月份:January，…
DAYNAME(date)	返回星期几: MONDAY，TUESDAY…SUNDAY
WEEKDAY(date)	返回周几，注意，周1是0，周2是1,。。。周日是6
QUARTER(date)	返回日期对应的季度，范围为1～4
WEEK(date),WEEKOFYEAR(date)	返回一年中的第几周
DAYOFYEAR(date)	返回日期是一年中的第几天
DAYOFMONTH(date)	返回日期位于所在月份的第几天
DAYOFWEEK(date)	返回周几，注意:周日是1，周一是2，。。。周六是7

 

#4.3 获取月份、星期、星期数、天数等函数
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;
/*输出
+-----------------+------------------+----------------+-----------------+---------------+-------------------+
| YEAR(CURDATE()) | MONTH(CURDATE()) | DAY(CURDATE()) | HOUR(CURTIME()) | MINUTE(NOW()) | SECOND(SYSDATE()) |
+-----------------+------------------+----------------+-----------------+---------------+-------------------+
|            2022 |                2 |             13 |               2 |            50 |                55 |
+-----------------+------------------+----------------+-----------------+---------------+-------------------+
*/
#MONTHNAME(date) 返回月份：January，..
#DAYNAME(date) 返回星期几：MONDAY，TUESDAY.....SUNDAY
SELECT MONTHNAME('2021-10-26'),DAYNAME('2021-10-26'),WEEKDAY('2021-10-26'),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;
/*
+-------------------------+-----------------------+-----------------------+--------------------+-----------------+------------------+-------------------+------------------+
| MONTHNAME('2021-10-26') | DAYNAME('2021-10-26') | WEEKDAY('2021-10-26') | QUARTER(CURDATE()) | WEEK(CURDATE()) | DAYOFYEAR(NOW()) | DAYOFMONTH(NOW()) | DAYOFWEEK(NOW()) |
+-------------------------+-----------------------+-----------------------+--------------------+-----------------+------------------+-------------------+------------------+
| October                 | Tuesday               |                     1 |                  1 |               7 |               44 |                13 |                1 |
+-------------------------+-----------------------+-----------------------+--------------------+-----------------+------------------+-------------------+------------------+
*/