mysql时间格式函数:from_unixtime()、unix_timestamp()、date_format()

本文介绍MySQL中datetime与int类型的时间存储方法及不同编程语言间的兼容性问题。详细讲解了from_unixtime(), unix_timestamp()和date_format()三个常用时间格式转换函数的应用场景。

mysql中存储的时间,主要分为datetime类型和int类型。一般来说规范的存法是存int型,特别是像过期时间、最近更新等需要排序、比较大小的时间,更应该存为int型。但最近的项目数据库通信涉及到java、php、c#三种语言,php中的时间int型为10位整数,java中的时间long型为13位整数,c#中不支持unix时间戳。这样往数据库存10位int型就很不方便,于是所有时间都存的datetime类型,取数据时再自行处理成需要的格式(当然跨时区是硬伤)。

mysql中常用的几种时间格式转换函数整理如下:

1,from_unixtime(timestamp, format):timestamp为int型时间,如14290450779;format为转换的格式,包含格式如下:

%M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。)
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)
%k 小时(0……23)
%h 小时(01……12)
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59)
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday )
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一

2,unix_timestamp(date):作用与from_unixtime()刚好相反,前者是把unix时间戳转换为可读的时间,而unix_timestamp()是把可读的时间转换为unix时间戳,这在对datetime存储的时间进行排序时会用到。如unix_timestamp('2009-08-06 10:10:40'),得到1249524739。如果unix_timestamp()不传参数,则调用now()函数自动取当前时间。

3,date_format(date, format):date_format()是将date或datetime类型值转换为任意的时间格式。比如常见的应用场景,某表有一个字段是更新时间,存储的是datetime类型,但前台展示时只需要显示年月日(xxxx-xx-xx),这个时候就可以用date_format(date,'%Y-%m-%d ')处理,而不需要在结果集中用程序循环处理。

<think>嗯,用户想了解MySQLfrom_unixtime函数的使用方法和示例。首先,我需要回忆这个函数的基本功能。记得from_unixtime是把Unix时间戳转换成可读的日期时间格式Unix时间戳通常是从1970年1月1日开始的秒数,但有时候也会遇到毫秒的情况,这时候需要先除以1000转换。 用户提到想了解常用函数,但主要关注from_unixtime,所以应该详细说明它的语法和参数。根据引用资料,这个函数有两个参数,第一个是时间戳,第二个是格式字符串。如果没指定格式,默认返回完整的日期时间,比如'YYYY-MM-DD HH:ii:ss'。 示例部分很重要。需要展示基本用法,比如直接转换秒级时间戳。例如,SELECT FROM_UNIXTIME(1617186153)会得到'2021-03-31 15:22:33'。另外,如果时间戳是毫秒级的,像引用4中提到的,需要除以1000,比如1568018267163/1000,这样处理后再用函数转换。 格式化输出也是用户需要的,比如只取年月日或者自定义格式。比如使用'%Y-%m-%d %H:%i'可以得到更简洁的时间显示。引用2中的例子也展示了如何用格式参数提取特定部分,比如'%Y-%m-%d'。 还要注意时间戳的范围。根据引用3,有效范围是1970年到2038年,超过的话可能会出错。这点需要提醒用户,避免他们遇到问题。 另外,结合其他函数可能会有用,比如UNIX_TIMESTAMP()可以将日期转回时间戳,形成互补。比如SELECT UNIX_TIMESTAMP('2021-03-31 15:22:33')会返回1617186153。 常见错误处理方面,用户可能在转换毫秒时间戳时忘记除以1000,导致结果错误。引用2中的查询示例就正确处理了这个问题,需要强调这一点。 最后,应用场景可以帮助用户理解何时使用这个函数,比如处理日志或分析时间序列数据。总结一下,回答应包括基本语法、参数说明、不同示例(秒级、毫秒级、格式化)、注意事项、相关函数和常见错误,以及应用场景。确保引用正确的资料,如引用2和4处理了毫秒转换,引用3提到了时间范围限制。</think>### MySQL FROM_UNIXTIME()函数详解 FROM_UNIXTIME()MySQL中处理时间戳转换的核心函数,主要用于将Unix时间戳转换为可读的日期时间格式。以下是具体使用方法及示例: #### 一、基本语法 ```sql FROM_UNIXTIME(unix_timestamp [, format]) ``` - `unix_timestamp`:必填参数,表示Unix时间戳(单位:秒) - `format`:可选参数,指定输出格式(与DATE_FORMAT()函数格式相同) #### 二、使用示例 1. **基础转换**(秒级时间戳) ```sql SELECT FROM_UNIXTIME(1617186153); -- 输出:'2021-03-31 15:22:33' ``` 2. **毫秒级时间戳转换**(需先转换为秒) ```sql SELECT FROM_UNIXTIME(1568018267163/1000); -- 输出:'2019-09-09 00:37:47' [^4] ``` 3. **带格式的转换** ```sql SELECT FROM_UNIXTIME(1617186153, '%Y-%m-%d %H:%i'); -- 输出:'2021-03-31 15:22' SELECT FROM_UNIXTIME(update_time/1000, '%Y-%m') FROM tech_innovation.t_count_software_copyright; [^2] ``` #### 三、注意事项 1. **时间戳范围**:有效范围为1970-01-01 00:00:01到2038-01-19 03:14:07(受4字节整数限制)[^3] 2. **类型转换**:若输入非数值型参数会返回NULL 3. **时区影响**:结果根据系统时区设置显示 #### 四、相关函数组合 ```sql -- 与UNIX_TIMESTAMP()形成互补 SELECT UNIX_TIMESTAMP('2021-03-31 15:22:33'); -- 输出:1617186153 [^3] ``` #### 五、常见错误处理 ```sql -- 错误:直接转换毫秒级时间戳 SELECT FROM_UNIXTIME(1568018267163); -- 错误结果 -- 正确:先进行单位转换 SELECT FROM_UNIXTIME(1568018267163/1000); [^4] ``` #### 六、应用场景 1. 日志时间戳转可读格式 2. 时间序列数据分析 3. 报表日期维度转换
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值