1.应用
1.如果数据库中时间格式是(2013-01-12 23:23:56—时间精确到秒)
select * from product where timestamp(add_time) = '2013-01-12 23:23:56';
select * from product where timestamp(add_time) between '2013-01-12 03:23:56' and '2013-01-12 23:23:56';
如果时间格式是(2013-01-12)则不能直接使用
select * from product where add_time = '2013-01-12'
因为add_time = '2013-01-12'
表示的是'2013-01-12 00:00:00'
,所以应该使用:
select * from product where Date(add_time) = '2013-01-12'
参考:
https://www.jianshu.com/p/9305e8698b52
API
Name | Description | 例子 |
---|---|---|
ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days) | 增加时间间隔 | SELECT ADDDATE('2008-01-02', 31); # -> '2008-02-02' |
ADDTIME(expr1,expr2) | 添加时间间隔 | SELECT ADDTIME('01:00:00.999999', '02:00:00.999998'); # -> '03:00:01.999997' |
CONVERT_TZ() | Convert from one time zone to another | |
CURDATE() | 获得当前日期 | SELECT CURDATE(); # -> '2008-06-13' |
CURRENT_DATE(), CURRENT_DATE | Synonyms for CURDATE() | |
CURRENT_TIME(), CURRENT_TIME | Synonyms for CURTIME() | |
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP | Synonyms for NOW() | |
CURTIME(); | 获得当前时间 | SELECT CURTIME(); # -> '23:50:26' |
DATE(expr) | 提取日期 | SELECT DATE('2003-12-31 01:02:03'); # -> '2003-12-31' |
DATE_ADD() | Add time values (intervals) to a date value | |
DATE_FORMAT(date,format) | 格式化时间 | |
DATE_SUB() | Subtract a time value (interval) from a date | |
DATEDIFF(expr1,expr2) | 日期相减 | SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30'); # -> 1 |
DAY() | Synonym for DAYOFMONTH() | |
DAYNAME(date) | 返回周几 | SELECT DAYNAME('2007-02-03'); # -> 'Saturday' |
DAYOFMONTH(date) | 月中第几天 | SELECT DAYOFMONTH('2007-02-03'); # -> 3 |
DAYOFWEEK(date) | 周中星期几 | SELECT DAYOFWEEK('2007-02-03'); # -> 7 |
DAYOFYEAR(date) | 年中第几天 | SELECT DAYOFYEAR('2007-02-03'); # -> 34 |
EXTRACT(unit FROM date) | 提取部分时间 | ELECT EXTRACT(YEAR FROM '2019-07-02'); # -> 2019 |
FROM_DAYS(N) | 将天数换换为时间 | SELECT FROM_DAYS(730669); # -> '2000-07-03' |
FROM_UNIXTIME(unix_timestamp[,format]) | 将unix时间戳转换为时间 | SELECT FROM_UNIXTIME(1447430881); # -> '2015-11-13 10:08:01' |
GET_FORMAT({DATE | TIME | DATETIME}, {‘EUR’ |
HOUR(time) | 抽取小时 | SELECT HOUR('10:05:03'); # -> 10 |
LAST_DAY(date) | 返回每月的最后一天 | SELECT LAST_DAY('2003-02-05'); # -> '2003-02-28' |
LOCALTIME(), LOCALTIME | Synonym for NOW() | |
LOCALTIMESTAMP, LOCALTIMESTAMP() | Synonym for NOW() | |
MAKEDATE(year,dayofyear) | 将某年的天数转换为日期 | mysql> SELECT MAKEDATE(2011,365); # -> '2011-12-31' |
MAKETIME(hour,minute,second) | 创建时间 | SELECT MAKETIME(12,15,30); # -> '12:15:30' |
MICROSECOND(expr) | 抽取微秒 | SELECT MICROSECOND('12:00:00.123456'); # -> 123456 |
MINUTE(time) | 抽取分钟 | SELECT MINUTE('2008-02-03 10:05:03'); # -> 5 |
MONTH(date) | 抽取月份 | mysql> SELECT MONTH('2008-02-03'); # -> 2 |
MONTHNAME(date) | 返回月份的名称 | mysql> SELECT MONTHNAME('2008-02-03'); # -> 'February' |
NOW([fsp]) | 返回日期和时间 | SELECT NOW(); # -> '2007-12-15 23:50:26' |
PERIOD_ADD() | Add a period to a year-month | |
PERIOD_DIFF() | Return the number of months between periods | |
QUARTER(date) | 返回季节 | mysql> SELECT QUARTER('2008-04-01'); # -> 2 |
SEC_TO_TIME() | Converts seconds to ‘hh:mm:ss’ format | |
SECOND(time) | 截取秒数 | SELECT SECOND('10:05:03'); # -> 3 |
SEC_TO_TIME(seconds) | 秒数转换为时间 | SELECT SEC_TO_TIME(2378); # -> '00:39:38' |
SUBDATE(date,INTERVAL expr unit), SUBDATE(expr,days) | DATE_SUB() | |
SUBTIME(expr1,expr2) | 时间相减 | mysql> SELECT SUBTIME('01:00:00.999999', '02:00:00.999998'); # -> '-00:59:59.999999' |
SYSDATE([fsp]) | 返回当前时间,NOW()返回的是程序开始的时间 | |
TIME(expr) | 提取时间 | SELECT TIME('2003-12-31 01:02:03'); # -> '01:02:03' |
TIME_FORMAT() | Format as time | |
TIME_TO_SEC() | Return the argument converted to seconds | |
TIMEDIFF(expr1,expr2) | 时间相减 | |
TIMESTAMP(expr), TIMESTAMP(expr1,expr2) | 日期转换为时间 | SELECT TIMESTAMP('2003-12-31'); # -> '2003-12-31 00:00:00' |
TIMESTAMPADD(unit,interval,datetime_expr) | 时间戳相加 | SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02'); # -> '2003-01-02 00:01:00' |
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) | 时间间隔 | SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01'); # -> 3 |
TO_DAYS() | Return the date argument converted to days | |
TO_SECONDS() | Return the date or datetime argument converted to seconds since Year 0 | |
UNIX_TIMESTAMP() | Return a Unix timestamp | |
UTC_DATE() | Return the current UTC date | |
UTC_TIME() | Return the current UTC time | |
UTC_TIMESTAMP() | Return the current UTC date and time | |
WEEK() | Return the week number | |
WEEKDAY() | Return the weekday index | |
WEEKOFYEAR() | Return the calendar week of the date (1-53) | |
YEAR() | Return the year | |
YEARWEEK() | Return the year and week |
参考:
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html