MySql 字符串时间转换
MySql中经常遇到字符串格式时间转换成时间类型的情况:
SELECT STR_TO_DATE('Jul 20 2013 7:49:14:610AM','%b %d %Y %h:%i:%s:%f%p') from DUAL; -- 执行后得到结果:
'2013-07-20 07:49:14.610000'
另外还有int值和时间变量相互转化的情况:
SELECT UNIX_TIMESTAMP(2009-4-5 12:50:58) RROM DUAL;
-- 结果为:
1238907058 SELECT FROM_UNIXTIME(1238907058) FROM DUAL;
-- 结果为:
'2009-4-5 12:50:58'
还可以自定义输出格式:
SELECT FROM_UNIXTIME( 1234567890, '%Y-%m-%d %H:%i:%S' ) FROM DUAL;
--结果为:
'2009-02-14 07:31:30'
具体格式符号如下:
%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)
%f 毫秒(000...999)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday )
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一天
%% 一个文字“%”。
以下是官方文档的说明:
11.3.2. The TIME
Type
MySQL retrieves and displays TIME
values in 'HH:MM:SS'
format (or 'HHH:MM:SS'
format for large hours values). TIME
values may range from '-838:59:59'
to '838:59:59'
. The hours part may be so large because the TIME
type can be used not only to represent a time of day (which must be less than 24 hours), but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative).
You can specify TIME
values in a variety of formats:
As a string in
'D HH:MM:SS.fraction'
format. You can also use one of the following “relaxed” syntaxes:'HH:MM:SS.fraction'
,'HH:MM:SS'
,'HH:MM'
,'D HH:MM:SS'
,'D HH:MM'
,'D HH'
, or'SS'
. HereD
represents days and can have a value from 0 to 34. Note that MySQL does not store the fraction part.As a string with no delimiters in
'HHMMSS'
format, provided that it makes sense as a time. For example,'101112'
is understood as'10:11:12'
, but'109712'
is illegal (it has a nonsensical minute part) and becomes'00:00:00'
.As a number in
HHMMSS
format, provided that it makes sense as a time. For example,101112
is understood as'10:11:12'
. The following alternative formats are also understood:SS
,MMSS
,HHMMSS
,HHMMSS.fraction
. Note that MySQL does not store the fraction part.As the result of a function that returns a value that is acceptable in a
TIME
context, such asCURRENT_TIME
.
For TIME
values specified as strings that include a time part delimiter, it is not necessary to specify two digits for hours, minutes, or seconds values that are less than 10
. '8:3:2'
is the same as '08:03:02'
.
Be careful about assigning abbreviated values to a TIME
column. Without colons, MySQL interprets values using the assumption that the two rightmost digits represent seconds. (MySQL interprets TIME
values as elapsed time rather than as time of day.) For example, you might think of '1112'
and 1112
as meaning '11:12:00'
(12 minutes after 11 o'clock), but MySQL interprets them as '00:11:12'
(11 minutes, 12 seconds). Similarly, '12'
and 12
are interpreted as '00:00:12'
. TIME
values with colons, by contrast, are always treated as time of the day. That is, '11:12'
mean '11:12:00'
, not '00:11:12'
.
By default, values that lie outside the TIME
range but are otherwise legal are clipped to the closest endpoint of the range. For
example, '-850:00:00'
and '850:00:00'
are converted to '-838:59:59'
and '838:59:59'
. Illegal TIME
values are converted to '00:00:00'
. Note that because '00:00:00'
is itself a legal TIME
value, there is no way to tell, from a value of '00:00:00'
stored in a table, whether the original value was specified as '00:00:00'
or whether it was illegal.
最新文章
- java中的权限修饰符的理解
- Glide加载图片到自定义的圆形ImageView中不显示
- Testlink与Redmine关联
- 控制变量法-初中物理-Nobel Lecture, December 12, 1929-php执行SET GLOBAL connect_timeout=2效果
- MySQL实战积累
- Hadoop2.7.2安装笔记
- 巧用C#做中间语言 实现Java调用.net DLL
- Oracle SQL操作计划基线总结(SQL Plan Baseline)
- 30万奖金!还带你奔赴加拿大相约KDD!?阿里聚安全算法挑战赛带你飞起!
- linux内核Makefile整体分析
- 01-Jenkins-Master节点安装
- iOS的非常全的三方库,插件,大牛博客
- C++ Primer 笔记——union
- Linux内核剖析(三)构建源码树
- Luogu1514 NOIP2010 引水入城 BFS、贪心
- 在listView中的模糊查询和删除
- nodejs+mysql入门实例(表的查询)
- TagHelpers 使用
- c++刷题(39/100)笔试题3
- 【转载】COM 组件设计与应用(九)——IDispatch 接口 for VC6.0