MySQL获取时间、格式转换各类操作方法详解

MySQL获取时间、格式转换各类操作方法详解

MySQL是一个强大的数据库管理系统，它支持多种数据类型存储，其中包括时间类型。在处理时间相关的业务中，我们需要了解不同的时间类型、获取时间和时间格式转换的方法。

一、SQL时间存储类型

MySQL中常用的时间类型有Date、Datetime、Timestamp和Time四种。

1. Date类型用于存储日期，格式为‘2022-6-17’。在其他语言中（如Python、JAVA等），经常使用函数输出Date为yyyy-mm-dd的形式。如果业务需要精确到天，可以使用该格式存储。

2. Datetime类型用于存储日期和时间，格式为‘2022-6-17 17:00:22’。如果业务需要精确到秒，可以使用该格式存储。

3. Time类型用于存储时间，不包括日期，格式为‘17:11:00’。如果业务只需要每天的时间，可以使用该格式存储。

4. Timestamp类型与Datetime类型类似，既存储时间又存储日期。格式为‘2022-6-17 17:00:22’。与Datetime的区别在于存储方式不同，Timestamp会把客户端插入的时间从当前时区转化为UTC进行存储，并在查询时将其转化为客户端当前时区进行返回。而Datetime则不做任何改变。此外，Timestamp能存储的时间范围与Datetime不同，Timestamp所能存储的时间范围为‘1970-01-01 00:00:01.000000’到‘2038-01-19 03:14:07.999999’，而Datetime所能存储的时间范围为‘1000-01-01 00:00:00.000000’到‘9999-12-31 23:59:59.999999’。还有一个特点是，Timestamp有一个自动初始化和更新的机制，如果插入数据时没有对该值进行赋值，则自动写入当前的时间戳。

5. Varchar/Bigint类型用于存储不固定格式的时间数据，例如有可能只记录到月或者只记录到小时，这种灵活不固定的时间数据只能使用字符串或者Bigint这种类型存储，而需要在后期提取并进行格式转换才能进行计算或逻辑运算，得到自己想要的时间。

二、获取时间

在使用MySQL操作时间数据时，首先需要知道如何获取当前时间和日期。MySQL提供了多种获取当前时间和日期的函数：

1. NOW()函数用于获取当地具体日期和时间，其返回值为一个Datetime类型的值。

2. LOCALTIME()、CURRENT_TIMESTAMP()和LOCALTIMESTAMP()函数均用于获取当地具体日期和时间，与NOW()函数类似。

3. SYSDATE()函数用于获取当地具体日期和时间，与上述函数不同的是，NOW()在执行该函数之前就已经获取到了时间，而SYSDATE()是在执行该函数时获取时间。

4. CURDATE()和CURRENT_DATE()函数用于获取当地具体日期。

5. CURTIME()和CURRENT_TIME()函数用于获取具体时间。

6. UTC_DATE()和UTC_TIME()函数用于获取UTC时间的日期和时间。

以上这些函数都可以获取具体日期和时间，平常使用一个NOW()函数即可满足大部分需求。

除了以上函数，还可以使用一些函数获取特定的时间值，如HOUR(SYSDATE())获取当前系统具体小时，MINUTE(SYSDATE())获取当前系统分钟等等，根据不同的需求选择对应的函数即可。

三、时间格式转换

如果时间数据是以BIGINT或VARCHAR类型存储的，或者需要将字符串转换为时间数据，就需要进行时间格式转换。MySQL提供了多种时间格式转换的函数：

1. CAST()函数可以将数据转换为指定的数据类型。比如我们需要将BIGINT类型的数据转换为DATE或DATETIME类型，就可以使用CAST()函数进行转换，语法为如下：

CAST( AS )

其中可转换的类型包括字符串（VARCHAR）、日期（DATE）、时间（TIME）、日期时间（DATETIME）、浮点型（DECIMAL）、整数（SIGNED）、和无符号整数（UNSIGNED）。

2. CONVERT()函数和CAST()函数功能类似，都可以将数据类型进行转换，但是不同之处在于CONVERT()函数采用的是标准SQL用法，而CAST()函数采用的是MySQL独有的语法。语法如下：

CONVERT(,)

3. STR_TO_DATE()函数用于将时间格式的字符串按照指定的显示格式（format格式）进行转换为不同的时间类型，语法如下：

STR_TO_DATE(,)

其中format格式可以自己定义，比如我们拿到一个存储时间的VARCHAR类型的数据，需要将它转换

扫码添加UTHEME微信为好友

· 分享WordPress相关技术文章，主题上新与优惠动态早知道。
· 微信端最大WordPress社群，限时免费入群。