Detailed explanation of various operation methods of MySQL obtaining time and format conversion

This article brings you relevant knowledge about Video Tutorial, which mainly introduces the relevant content about mysql acquisition time and various operation methods of format conversion. Let’s take a look at it together. I hope Helpful to everyone.

Recommended learning: mysql video tutorial

1. SQL time storage type

First of all, we must play with the processing To operate time, you must first understand what this data type can do and what it is used for. There are three data types commonly used to store dates in MySQL: Date, Datetime, and Timestamp.

1.date

Calendar date, for example: ‘2022-6-17’. The format is: %Y-%m-%d. In other languages, such as Python, JAVA, etc., the function output Date is in the form of yyyy-mm-dd. This format is used for business operations that are extremely accurate.

2.datetime

Specific time and date For example: '2022-6-17 17:00:22' format is: %Y-%m -%d %H:%M:%s. This time format can be used when business requirements require accuracy to seconds.

3.time

The specific time does not include the date, for example: '17:11:00' format is: %H:%M:% s. This time format can be used when only daily time is required in business requirements.

4.timestamp

Same as datetime storage type, it also stores both time and date. The format format is: %Y-%m-%d %H:%M:%s.

The difference between PS.datetime and timestamp

• The storage method is different. For TIMESTAMP, it converts the time inserted by the client from the current time zone to UTC (Universal Standard Time) for storage. When querying, it is converted into the current time zone of the client and returned. For DATETIME, no changes are made, and the input and output are basically the same.

• The stored time range is also different. The time range that timestamp can store is: '1970-01- 01 00:00:01.000000' to '2038-01-19 03:14:07.999999'. The time range that datetime can store is: '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'.

• timestamp has a mechanism of automatic initialization and update, which means that if the value is not assigned when inserting data, the format corresponding to the current [timestamp] will be automatically written. When updating other fields, it will automatically update to the current time

Comparison summary

• timestamp and datetime except the storage range It is different from the storage method, there is not much difference. For cross-time zone business, TIMESTAMP is more suitable.

• timestamp has automatic initialization and update. When you update a record, the column value will automatically Update, this is the biggest difference from datatime

##5.varchar/bigint

Sometimes the storage format is not fixed, and the storage time may occur It can be accurate to day or hour or only month. This kind of flexible and unfixed time can only be carried out using string or BIGINT format.

# This requires extraction and post-processing, converting it into time format for calculation or performing logical operations to get the time you want.

2. Get the time

1.now()

Get the local specific date and time:

select now() as time

## 2.localtime()

Get the local specific date and time, the same as now():

select LOCALTIME() as time

3.current_timestamp()

Get the local specific date and time, the same as now():

select current_timestamp() as time

4.localtimestamp()

Get the local specific date and time, the same as now () Same as:

select LOCALTIMESTAMP() as time

以上这4种函数功能都与now()功能一样获取当地具体日期和时间，平常使用一个now()就好了好记。

5.sysdate()

获取当地具体日期和时间，与now（）上述几个函数不一样的是，now（）在执行该函数之前就已得到了。

select sysdate() as time

以上函数均为获取具体日期和时间。

---

 6.curdate()

获取当地具体日期:

select curdate() as time

7.current_time()

获取当地具体日期，和curdate()函数功能一样：

select current_date() as time

 以上函数均为获取具体日期。

---

8. curtime()

获取具体的时间：

select curtime() as time

9.current_time()

获取具体的时间：

select current_time() as

 以上均为获得具体时间的函数。

---

10. utc_date()

获取UTC时间的日期，因为我们是东八时区要快8个小时，本地时间=UTC时间+8小时。

select utc_date() as time

由于博主现在是晚上九点所以还是6月17日，如果是早上八点之前就是6月16号了。

11.utc_time

获取UTC时间的时间。

select utc_time() as time

12.utc_timestamp()

获取UTC时间的具体日期和时间，在做跨国业务时非常有用。

select utc_timestamp() as time

 以上为获取UTC时间函数。

---

 13.HOUR(SYSDATE())

获取系统具体小时：

select HOUR(SYSDATE()) as time

 14.MINUTE(SYSDATE())

获取当前系统分钟：

select MINUTE(SYSDATE()) as time

 其他获取year,month,day,second,microsecond都可以通过这种方法获得，这里不再演示。

三、转换时间

如果是用BIGINT或者是字符串varchar存储的时间数据就需要将该列数据转换为时间数据，或者输入一个字符串想要转化为时间格式都需要转换函数，这里详细介绍各种方法解决这种问题：

1.cast() 

基础语法格式：

cast( <数据> as <数据类型> )

可转换的类型有字符串varchar、日期date、时间time、日期时间datetime、浮点型decimal、整数signed、无符号整数unsigned。 

例如我们拿到展示的sql表格：

 该列类型为BIGINT：

 下面直接用cast转换为时间类型：

select cast(time as date) as timefrom value_test

 可见如果有与其他format不对应，只记录到月或者记录到小时时，将不能识别转为时间类型。也可以切换成time或是datetime：

select cast(time as datetime) as timefrom value_test

select cast(time as time) as timefrom value_test

只要是有6个字符的都会被识别为%H:%M:s。

我们可以修改表再看：

 2.convert()

基础语法格式：

convert(<数据>,<数据类型>)

select CONVERT(time ,date) as timefrom value_test

 和上述cast的功能一样，但是cast是强制转换。

所以说如果涉及到记录有多个不同维度的时间数据存储的时候，一般是不用数据库时间类型去做存储的。看cast的例子就可以看出。

3.str_to_date()

str_to_date()函数可以将时间格式的字符串按照所指定的显示格式（format）转换为不同的时间类型。

基础语法格式：

str_to_date(<字符串>,<format格式>')

select str_to_date(time,'%Y%m%d') as timefrom value_test

 这个函数自由性要比cast和convert的自由性高很多，可以自由定义format，但是不会仅显示单个年或日，后面会根据字符的长度补零填充：

select str_to_date(time,'%Y') as timefrom value_test

 这里我们可以更改表格的时间观察是否不满足或者超过标准的时间格式能够被识别：

select str_to_date(time,'%Y%m%d') as timefrom value_test

select str_to_date(time,'%Y%m%d%H%i%S') as timefrom value_test

 可见兼容能力是很强的。

如果是时间都是统一格式记录的直接使用cast或者convert快速转换为时间格式就好了，若是记录的有多个维度的时间应该使用str_to_date函数来转换。

四、时间转换

时间转换一般是把时间类似数据转换为其他类型数据，以上例子cast（）函数和convert（）函数都可以做到。改变一下位置就好了，由于上述已经提到这里就做两个简单的例子展示：

1.cast()

select cast(create_time as signed) as timefrom value_test

2.convert()

select convert(create_time ,signed) as timefrom value_test

 3.date_format()

其实最主要的还是使用data_format()，date_format()函数可以以不同的格式显示日期/时间数据，可以实现日期转换成字符串。也就是将时间数据读取之后按照format形式转换为字符串输出，当然转换为了字符串我们又可以转为其他的格式。

语法格式：

date_format(<时间类型数据>,<format格式>)

其中format的格式参数可选的有：

格式	描述
%a	星期名缩写
%b	月名缩写
%c	代表几月的数值
%D	带时序后缀的数值-天
%d	天数，数值(00-31)
%e	天数，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
--	--------------------------
%i	分钟，数值(00-59)
%j	转换为天数 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)%T	时间, 24-小时 (hh:mm:ss)
--	-----------------------------
%U	从年初首周开始计算 (00-53)  星期日是一周的第一天
%u	从年初首周开始计算 (00-53)  星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
--	----------------------------
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	当前周的天数，（0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
--	--------------------------
%Y	年，4 位
%y	年，2 位

自己大家可自己随意组合使用：

select date_format(create_time, '%x%v') as timefrom value_test

但是记住转换输出的都为字符串，转换为其他类型都需要再次转换.

推荐学习：mysql视频教程

The above is the detailed content of Detailed explanation of various operation methods of MySQL obtaining time and format conversion. For more information, please follow other related articles on the PHP Chinese website!