mysql 插入语句时间格式 mysql时间加

转载

IT剑客之家 2024-07-04 17:12:00

文章标签 mysql 插入语句时间格式 mysql 数据库日期时间字符串 文章分类 MySQL 数据库

本节介绍可用于操作时间值的函数。用于描述每个日期和时间类型的值范围以及可以指定值的有效格式。

表12.11 日期和时间函数

名字	描述
ADDDATE()	将时间值（间隔）添加到日期值
ADDTIME()	添加时间
CONVERT_TZ()	从一个时区转换为另一个时区
CURDATE()	返回当前日期
CURRENT_DATE（）， CURRENT_DATE	CURDATE（）的同义词
CURRENT_TIME（）， CURRENT_TIME	CURTIME（）的同义词
CURRENT_TIMESTAMP（）， CURRENT_TIMESTAMP	NOW（）的同义词
CURTIME()	返回当前时间
DATE()	提取日期或日期时间表达式的日期部分
DATE_ADD()	将时间值（间隔）添加到日期值
DATE_FORMAT()	设置指定日期的格式
DATE_SUB()	从日期中减去时间值（间隔）
DATEDIFF()	减去两个日期
DAY()	DAYOFMONTH（）的同义词
DAYNAME()	返回工作日的名称
DAYOFMONTH()	返回月份中的某天（0-31）
DAYOFWEEK()	返回参数的工作日索引
DAYOFYEAR()	返回一年中的某一天（1-366）
EXTRACT()	提取日期的一部分
FROM_DAYS()	将日期数字转换为日期
FROM_UNIXTIME()	将 Unix 时间戳格式化为日期
GET_FORMAT()	返回日期格式字符串
HOUR()	提取小时
LAST_DAY	返回参数的月份的最后一天
LOCALTIME（）， LOCALTIME	NOW（）的同义词
LOCALTIMESTAMP， LOCALTIMESTAMP（）	NOW（）的同义词
MAKEDATE()	从一年中的哪一天创建日期
MAKETIME()	从小时、分钟、秒创建时间
MICROSECOND()	从参数返回微秒
MINUTE()	从参数返回分钟数
MONTH()	从过去的日期返回月份
MONTHNAME()	返回月份名称
NOW()	返回当前日期和时间
PERIOD_ADD()	向年月添加期间
PERIOD_DIFF()	返回周期之间的月数
QUARTER()	从日期参数返回季度
SEC_TO_TIME()	将秒转换为“hh：mm：ss”格式
SECOND()	返回第二个（0-59）
STR_TO_DATE()	将字符串转换为日期
SUBDATE()	使用三个参数调用时 DATE_SUB（）的同义词
SUBTIME()	减去时间
SYSDATE()	返回函数执行的时间
TIME()	提取表达式传递的时间部分
TIME_FORMAT()	格式化为时间
TIME_TO_SEC()	返回转换为秒的参数
TIMEDIFF()	减去时间
TIMESTAMP()	使用单个参数，此函数返回日期或日期时间表达;有两个参数，参数的总和
TIMESTAMPADD()	向日期时间表达式添加间隔
TIMESTAMPDIFF()	使用单位返回两个日期时间表达式的差值指定
TO_DAYS()	返回转换为天的日期参数
TO_SECONDS()	返回自第 0 年
UNIX_TIMESTAMP()	返回 Unix 时间戳
UTC_DATE()	返回当前 UTC 日期
UTC_TIME()	返回当前 UTC 时间
UTC_TIMESTAMP()	返回当前 UTC 日期和时间
WEEK()	返回周数
WEEKDAY()	返回工作日索引
WEEKOFYEAR()	返回日期的日历周（1-53）
YEAR()	返回年份
YEARWEEK()	返回年份和周

下面是一个使用日期函数的示例。以下查询选择具有date_col值的所有行从过去 30 天内：

mysql> SELECT something FROM tbl_name     -> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;

该查询还会选择日期为将来的行。
需要日期值的函数通常接受日期时间值并忽略时间部分。需要时间值的函数通常接受日期时间值并忽略日期部分。
计算返回当前日期或时间的函数在查询执行开始时，每个查询仅一次。这意味着在单个查询中始终对函数（如 NOW（））的多个引用产生相同的结果。（出于我们的目的，单个查询也包括对存储程序（存储例程、触发器或事件）以及该程序调用的所有子程序。这个原则也适用于 CURDATE（）、CURTIME（）、UTC_DATE（）、UTC_TIME（）、UTC_TIMESTAMP（）以及任何他们的同义词。
CURRENT_TIMESTAMP（）、CURRENT_TIME（）、CURRENT_DATE（）和 FROM_UNIXTIME（）函数返回当前会话时区中的值，可用作 time_zone系统变量的会话值。此外，UNIX_TIMESTAMP（）假设其参数是会话时区中的日期时间值。

某些日期函数可以与“零”日期一起使用，或者不完整的日期，例如，而其他人不能。通常提取部分日期的函数使用不完整的日期，因此可以在可能时返回 0 否则需要非零值。例如：'2001-11-00':

mysql> SELECT DAYOFMONTH('2001-11-00'), MONTH('2005-00-00');
        -> 0, 0

其他函数需要完成日期，并返回不完整的日期。这些包括执行日期算术或映射部分日期的函数到名字。例如：NULL

mysql> SELECT DATE_ADD('2006-05-00',INTERVAL 1 DAY);
        -> NULL
mysql> SELECT DAYNAME('2006-05-00');
        -> NULL

当传递 DATE（）函数值作为它们的函数时，有几个函数是严格的参数并拒绝日期部分为零的不完整日期：CONVERT_TZ（）、DATE_ADD（）、DATE_SUB（）、DAYOFYEAR（）、TIMESTAMPDIFF（）、TO_DAYS（）、TO_SECONDS（）、WEEK（）、WEEKDAY（）、WEEKOFYEAR（）、YEARWEEK（）。

支持TIME、DATETIME和TIMESTAMP值的小数秒，精度高达微秒。采用临时参数的函数接受具有小数秒的值。时间函数的返回值包括适当的小数秒。

ADDDATE（日期，INTERVAL expr单位），ADDDATE（日期，天）当以第二个参数，ADDDATE（）是一个 DATE_ADD（）的同义词。这相关函数 SUBDATE（）是一个 DATE_SUB（）的同义词

mysql> SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);
        -> '2008-02-02'
mysql> SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);
        -> '2008-02-02'

当以 days 形式调用时第二个参数，MySQL将其视为整数要添加到 EXPR

mysql> SELECT ADDDATE('2008-01-02', 31);
        -> '2008-02-02'

如果日期或天数为NULL，此函数将返回NULL。

ADDTIME(expr1,expr2)

ADDTIME（）将expr2与expr1相加并返回结果。expr1是时间或日期时间表达式，expr2是时间表达式。如果表达式1或表达式2为NULL，则返回NULL。

从MySQL 8.0.28开始，此函数和SUBTIME（）函数的返回类型确定如下：

如果第一个参数是动态参数（例如在准备好的语句中），则返回类型为TIME。
否则，函数的解析类型是从第一个参数的解析类型派生的。

mysql> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
        -> '2008-01-02 01:01:01.000001'
mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
        -> '03:00:01.999997'

CONVERT_TZ(dt,from_tz,to_tz)

CONVERT_TZ（）将日期时间值dt从from_TZ给定的时区转换为to_TZ给定的时区，并返回结果值。如果任何参数无效，或者其中任何参数为NULL，则此函数将返回NULL。
在32位平台上，此函数支持的值范围与TIMESTAMP类型相同。在64位平台上，从MySQL 8.0.28开始，支持的最大值为“3001-01-18 23:59:59.999999”UTC。
无论平台或MySQL版本如何，如果从from_tz转换为UTC时该值超出支持的范围，则不会发生转换。

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
        -> '2004-01-01 13:00:00'
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
        -> '2004-01-01 22:00:00'

CURDATE()

以“YYYY-MM-DD”或YYYYMMDD格式的值返回当前日期，具体取决于函数是在字符串上下文中使用还是在数字上下文中使用。

mysql> SELECT CURDATE();
        -> '2008-06-13'
mysql> SELECT CURDATE() + 0;
        -> 20080613

• CURRENT_DATE， CURRENT_DATE（） CURRENT_DATE 和 CURRENT_DATE（） 是 CURDATE（） 的同义词。
• CURRENT_TIME， CURRENT_TIME（[FSP]） CURRENT_TIME 和 CURRENT_TIME（） 是 CURTIME（） 的同义词。
• CURRENT_TIMESTAMP， CURRENT_TIMESTAMP（[FSP]） CURRENT_TIMESTAMP 和 CURRENT_TIMESTAMP（） 是 NOW（） 的同义词。
• CURTIME([fsp])

以“hh:mm:ss”或hhmmss格式的值返回当前时间，具体取决于函数是在字符串上下文中使用还是在数字上下文中使用。该值以会话时区表示。
如果给定fsp参数以指定从0到6的小数秒精度，则返回值包括该数字的小数秒部分。

mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 19:25:37  |
+-----------+

mysql> SELECT CURTIME() + 0;
+---------------+
| CURTIME() + 0 |
+---------------+
|        192537 |
+---------------+

mysql> SELECT CURTIME(3);
+--------------+
| CURTIME(3)   |
+--------------+
| 19:25:37.840 |
+--------------+

DATE(expr)

提取日期或日期时间表达式expr的日期部分。如果expr为NULL，则返回NULL。

mysql> SELECT DATE('2003-12-31 01:02:03');
        -> '2003-12-31'

DATEDIFF(expr1,expr2)

DATEDIFF（）返回expr1−expr2，用从一个日期到另一个日期的天数表示。expr1和expr2是日期或日期和时间表达式。计算中仅使用值的日期部分。

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
        -> 1
mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
        -> -31

如果expr1或expr2为NULL，此函数将返回NULL。

DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)

这些函数执行日期运算。date参数指定开始日期或日期时间值。expr是一个表达式，指定要从开始日期中添加或减去的间隔值。expr被计算为字符串；它可能以a开头，表示负区间。unit是一个关键字，指示表达式应使用的单位。
有关时间间隔语法的更多信息，包括单位说明符的完整列表、每个单位值的expr参数的预期形式以及时间算术中操作数解释的规则，请参阅时间间隔。
返回值取决于以下参数：

如果日期为NULL，函数将返回NULL。
DATE如果日期参数是DATE值，并且您的计算只涉及YEAR、MONTH和DAY部分（即没有时间部分）。
（MySQL 8.0.28及更高版本：）TIME如果日期参数是TIME值，并且计算只涉及HOURS、MINUTES和SECONDS部分（即没有日期部分）。
如果第一个参数是DATETIME（或TIMESTAMP）值，或者如果第一个自变量是DATE并且单位值使用HOURS、MINUTES或SECONDS，或者第一个自变量的类型是TIME并且单位数值使用YEAR、MONTH或DAY，则为DATETIME。
（MySQL 8.0.28及更高版本：）如果第一个参数是动态参数（例如，prepared语句），则如果第二个参数是仅包含YEAR、MONTH或DAY值组合的间隔，则其解析类型为DATE；否则，其类型为DATETIME。
否则为字符串（键入VARCHAR）。

为了确保结果是DATETIME，可以使用CAST（）将第一个参数转换为DATETIME。

mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);
        -> '2018-05-02'
mysql> SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);
        -> '2017-05-01'
mysql> SELECT DATE_ADD('2020-12-31 23:59:59',
    ->                 INTERVAL 1 SECOND);
        -> '2021-01-01 00:00:00'
mysql> SELECT DATE_ADD('2018-12-31 23:59:59',
    ->                 INTERVAL 1 DAY);
        -> '2019-01-01 23:59:59'
mysql> SELECT DATE_ADD('2100-12-31 23:59:59',
    ->                 INTERVAL '1:1' MINUTE_SECOND);
        -> '2101-01-01 00:01:00'
mysql> SELECT DATE_SUB('2025-01-01 00:00:00',
    ->                 INTERVAL '1 1:1:1' DAY_SECOND);
        -> '2024-12-30 22:58:59'
mysql> SELECT DATE_ADD('1900-01-01 00:00:00',
    ->                 INTERVAL '-1 10' DAY_HOUR);
        -> '1899-12-30 14:00:00'
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
        -> '1997-12-02'
mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
    ->            INTERVAL '1.999999' SECOND_MICROSECOND);
        -> '1993-01-01 00:00:01.000001'

当将MONTH间隔添加到DATE或DATETIME值，并且生成的日期包含给定月份中不存在的日期时，该日期将调整为该月的最后一天，如下所示：

mysql> SELECT DATE_ADD('2024-03-30', INTERVAL 1 MONTH) AS d1, 
     >        DATE_ADD('2024-03-31', INTERVAL 1 MONTH) AS d2;
+------------+------------+
| d1         | d2         |
+------------+------------+
| 2024-04-30 | 2024-04-30 |
+------------+------------+
1 row in set (0.00 sec)

DATE_FORMAT(date,format)

根据格式字符串设置日期值的格式。如果任一参数为NULL，则函数将返回NULL。
下表中显示的说明符可以用于格式字符串。格式说明符字符之前必须有%字符。说明符也适用于其他函数：STR_to_DATE（）、TIME_FORMAT（）、UNIX_TIMESTAMP（）。

字符	说明
`%a`	Abbreviated weekday name (`Sun`..`Sat`)
`%b`	Abbreviated month name (`Jan`..`Dec`)
`%c`	Month, numeric (`0`..`12`)
`%D`	Day of the month with English suffix (`0th`, `1st`, `2nd`, `3rd`, …)
`%d`	Day of the month, numeric (`00`..`31`)
`%e`	Day of the month, numeric (`0`..`31`)
`%f`	Microseconds (`000000`..`999999`)
`%H`	Hour (`00`..`23`)
`%h`	Hour (`01`..`12`)
`%I`	Hour (`01`..`12`)
`%i`	Minutes, numeric (`00`..`59`)
`%j`	Day of year (`001`..`366`)
`%k`	Hour (`0`..`23`)
`%l`	Hour (`1`..`12`)
`%M`	Month name (`January`..`December`)
`%m`	Month, numeric (`00`..`12`)
`%p`	`AM` or `PM`
`%r`	Time, 12-hour (`hh:mm:ss` followed by `AM` or `PM`)
`%S`	Seconds (`00`..`59`)
`%s`	Seconds (`00`..`59`)
`%T`	Time, 24-hour (`hh:mm:ss`)
`%U`	Week (`00`..`53`), where Sunday is the first day of the week; Week() mode 0
`%u`	Week (`00`..`53`), where Monday is the first day of the week; Week() mode 1
`%V`	Week (`01`..`53`), where Sunday is the first day of the week; Week() mode 2; used with `%X`
`%v`	Week (`01`..`53`), where Monday is the first day of the week; Week() mode 3; used with `%x`
`%W`	Weekday name (`Sunday`..`Saturday`)
`%w`	Day of the week (`0`=Sunday..`6`=Saturday)
`%X`	Year for the week where Sunday is the first day of the week, numeric, four digits; used with `%V`
`%x`	Year for the week, where Monday is the first day of the week, numeric, four digits; used with `%v`
`%Y`	Year, numeric, four digits
`%y`	Year, numeric (two digits)
`%%`	A literal `%` character
`%x`	`x`, for any “`x`” not listed above

由于MySQL允许存储诸如“2014-00-00”之类的不完整日期，因此月份和日期说明符的范围以零开头。
用于日期和月份名称及缩写的语言由lc_time_names系统变量的值控制
对于%U、%U、%V和%V说明符，有关模式值的信息，请参阅WEEK（）函数的描述。该模式会影响星期编号的方式。
DATE_FORMAT（）返回一个字符串，该字符串具有character_set_connection和collating_connection给定的字符集和排序规则，因此它可以返回包含非ASCII字符的月份和工作日名称：

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
        -> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
    ->                 '%D %y %a %d %m %b %j');
        -> '4th 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
    ->                 '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
        -> '00'

DAYNAME(date)

返回日期的工作日名称。用于名称的语言由lc_time_names系统变量的值控制。如果日期为NULL，则返回NULL。

mysql> SELECT DAYNAME('2007-02-03');
        -> 'Saturday'

DAYOFMONTH(date)

对于日期，返回一个月中的哪一天，范围在1到31之间；对于日期为零的日期，如“0000-00-00”或“2008-00-00”，返回0。如果日期为NULL，则返回NULL。

mysql> SELECT DAYOFMONTH('2007-02-03');
        -> 3

DAYOFWEEK(date)

返回日期的工作日索引（1=周日，2=周一，…，7=周六）。这些索引值对应于ODBC标准。如果日期为NULL，则返回NULL。

mysql> SELECT DAYOFWEEK('2007-02-03');
        -> 7

DAYOFYEAR(date)

返回一年中日期的日期，范围为1到366。如果日期为NULL，则返回NULL。

mysql> SELECT DAYOFYEAR('2007-02-03');
        -> 34

EXTRACT(unit FROM date)

EXTRACTT（）函数使用与DATE_ADD（）或DATE_SUB（）相同类型的单位说明符，但从日期中提取部分，而不是执行日期运算。有关单位参数的信息，请参见时间间隔。如果日期为NULL，则返回NULL。

mysql> SELECT EXTRACT(YEAR FROM '2019-07-02');
        -> 2019
mysql> SELECT EXTRACT(YEAR_MONTH FROM '2019-07-02 01:02:03');
        -> 201907
mysql> SELECT EXTRACT(DAY_MINUTE FROM '2019-07-02 01:02:03');
        -> 20102
mysql> SELECT EXTRACT(MICROSECOND
    ->                FROM '2003-01-02 10:30:00.000123');
        -> 123

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。