一文带你玩转MySQL获取时间和格式转换各类操作方法详解
时间在数据库中经常作为时间索引,在数据入库和出库以及更新的时候都需要变化。在一些指标计算或者是提取某段时间的数据时,都会根据数据库中的时间索引数据进行操作。因此很大一部分我们操作数据都得先从时间数据下手,但是想要真正提取到我们想要的时间作为索引,还需要我们掌握许多功能函数方便我们操作,这是一个比较复杂的运用过程。因此特地写下这篇文章,记录一些十分好用常用的处理数据库SQL时间数据的函数,以及实例运用这些函数完成一些复杂查询任务。
一、SQL时间存储类型首先要玩好处理时间的操作,要先明白此数据类型能够干什么事,有什么用。MySQL中经常用来存储日期的数据类型有三种:Date、Datetime、Timestamp。
1.date日历日期,例如: 2022-6-17 .format形式为:%Y-%m-%d。在其他语言中,像Python、JAVA等利用函数输出Date都为yyyy-mm-dd的形式,业务精确到天就用这个格式。
2.datetime具体时间日期 例如: 2022-6-17 17:00:22 format格式为:%Y-%m-%d %H:%M:%s.当业务需求中需要精确到秒时,可以用这个时间格式。
3.time具体时间不包括日期,例如: 17:11:00 format格式为:%H:%M:%s。当业务需求中只需要每天的时间,可以用这个时间格式。
4.timestamp和datetime存储类型一样,也是既存储时间又存储日期。format格式为:%Y-%m-%d %H:%M:%s.
PS.datetime与timestamp的区别
存储方式不同,对于TIMESTAMP,它把客户端插入的时间从当前时区转化为UTC(世界标准时间)进行存储。查询时,将其又转化为客户端当前时区进行返回。而对于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有一个机制是自动初始化与更新,意思就是如果插入数据的时候没有对该值进行赋值,则自动写入当前的时间戳对应的格式。在更新其他字段的时候该自动会自动更新到当前的时间对比总结
timestamp和datetime除了存储范围和存储方式不一样,没有太大区别。如果对于跨时区的业务,TIMESTAMP更为合适 timestamp有自动初始化和更新,当你update某条记录的时候,该列值会自动更新,这是和datatime最大的区别 5.varchar/bigint有时候存储入库格式不是固定的,可能出现入库时间精确到日或者是小时又可能只有月,这种灵活不固定的时间就只能使用字符串或者是BIGINT这种类型格式来进行。
这种就需要提取出来后期处理,转换为时间格式进行计算或者进行逻辑运算得到自己想要的时间。
二、获取时间 1.now()获取当地具体日期和时间:
select now() as time 2.localtime()获取当地具体日期和时间,与now()一样:
select LOCALTIME() as time 3.current_timestamp()获取当地具体日期和时间,与now()一样:
select current_timestamp() as time 4.localtimestamp()获取当地具体日期和时间,与now()一样:
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 time以上均为获得具体时间的函数。
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的格式参数可选的有:
自己大家可自己随意组合使用:
select date_format(create_time, %x%v ) as timefrom value_test
但是记住转换输出的都为字符串,转换为其他类型都需要再次转换。
参阅:
mysql 日期类型转换
到此这篇关于MySQL获取时间和格式转换各类操作方法的文章就介绍到这了,更多相关MySQL获取时间和格式转换内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持!
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 一文带你玩转MySQL获取时间和格式转换各类操作方法详解
相关文章
- MySql 数字转换函数使用指南(mysql转数字函数)
- MySQL: 转换为崭新的时刻(mysql转换为时间)
- MySQL 显示记录的简单方法(mysql显示记录)
- MySQL 集群技术:精通与实践(mysql集群方面的书籍)
- MySQL 高级查询技巧掌握,让你不再为复杂查询烦恼!(mysql高级查询)
- MySQL主从同步配置及查看流程详解(mysql查看主从)
- MySQL删除数据的语句详解(mysql删除数据的语句)
- MySQL 日期格式转换简明指南(mysql日期转换)
- MySQL的日志路径及使用方法详解(mysql 日志路径)
- 如何压缩MySQL文件?详解MySQL压缩文件的方法与步骤。(mysql压缩文件)
- 连接MySQL如何有效关闭数据库连接(mysql 关闭数据库)
- 从零开始:使用MySQL创建数据库(用mysql创建数据库)
- MySQL中使用交集操作来实现数据筛选和比较的方法详解(mysql中交集操作)
- MySQL中repeat函数的使用详解(mysql中repeat)
- MySQL中如何使用pi函数进行数学计算(mysql中pi的用法)
- MySQL中LEN函数的用法详解(mysql中len用法)
- MySQL中如何进行IP地址转换(mysql中ip转换)
- MySQL两表数据比对的详细操作方法(mysql 两表数据比对)
- MySQL中的条件判断语句详解(mysql中判断条件语句)
- MySQL函数商的使用和实现方法(mysql中函数商)
- MySQL实现三表左外连接的方法详解(mysql三表左外连接)
- 深入了解MySQL的三大存储引擎读法(mysql三大引擎怎么读)
- MySQL中百分号()的用法详解(mysql _ %的用法)
- 解析MySQL XA异常,保障数据一致性(mysql xa 异常)
- 深入解析MySQL的双层循环,优化查询效率(mysql 两层循环)
- 下载旧版本 MySQL数据库(mysql下载以前版本)