mysql 毫秒格式化_MySQL毫秒值和日期的指定格式的相互转换

一、毫秒值转换为指定的日期格式

使用MYSQL自带的函数FROM_UNIXTIME(unix_timestamp,format)。

举例：

select FROM_UNIXTIME(1364176514656/1000,'%Y-%m-%d %h:%i:%s')as date

;

结果为：

2013-03-25 09:55:15

其中unix_timestamp为字段值/1000.

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), 这里星期一是星期的第一天

%% 一个文字“%”。

二、日期格式转换为毫秒值

同理，有时我们也想把datetime类型的数据转换为int类型保存到数据库。那么这时候就需要用到函数

select UNIX_TIMESTAMP('2011-05-31

23:59:59'); //这个只是秒值，若需要毫秒值则再乘以1000

结果

1306771200

注意 UNIX_TIMESTAMP该函数只返回1970。。。。到现在的秒数。如果算上一个函数一样是毫秒数，则还需要乘以1000

三、根据日期格式转换为指定的日期格式

DATE_FORMAT(date,

format)函数可根据format字符串格式化日期或日期和时间值date，返回结果串。也可用DATE_FORMAT( )

来格式化DATE 或DATETIME 值，以便得到所希望的格式。根据format字符串格式化date值:

下面是函数的参数说明: %S, %s 两位数字形式的秒( 00,01, . . .,

59) %i 两位数字形式的分( 00,01, . . .,

59) %H 两位数字形式的小时，24 小时(00,01, . . .,

23) %h, %I 两位数字形式的小时，12 小时(01,02, . . .,

12) %k 数字形式的小时，24 小时(0,1, . . .,

23) %l 数字形式的小时，12 小时(1, 2, . . .,

12) %T 24 小时的时间形式(hh : mm : s

s) %r 12 小时的时间形式(hh:mm:ss AM 或hh:mm:ss

PM) %p AM 或P M %W 一周中每一天的名称(

Sunday, Monday, . . ., Saturday) %a 一周中每一天名称的缩写(

Sun, Mon, . . ., Sat) %d 两位数字表示月中的天数( 00, 01, . .

., 31) %e 数字形式表示月中的天数( 1, 2， . . .,

31) %D 英文后缀表示月中的天数( 1st, 2nd, 3rd, . .

.) %w 以数字形式表示周中的天数( 0 = Sunday, 1=Monday, . . .,

6=Saturday) %j 以三位数字表示年中的天数( 001, 002, . . .,

366) % U 周(0, 1, 52)，其中Sunday

为周中的第一天 %u 周(0, 1, 52)，其中Monday

为周中的第一天 %M 月名(January, February, . . .,

December) %b 缩写的月名( January, February, . . .,

December) %m 两位数字表示的月份( 01, 02, . . .,

12) %c 数字表示的月份( 1, 2, . . .,

12) %Y 四位数字表示的年份 %y

两位数字表示的年份 %% 直接值“%”

示例: select date_format(日期字段,’%Y-%m-%d’) as ‘日期’

from test mysql> SELECT DATE_FORMAT('1997-10-04

22:23:00', '%W %M %Y'); -> 'Saturday October

1997' mysql> SELECT DATE_FORMAT('1997-10-04

22:23:00', '%H:%i:%s'); ->

'22:23:00' mysql> SELECT

DATE_FORMAT('1997-10-04 22:23:00', '%D %y %a %d %m

%b %j'); -> '4th 97 Sat 04 10 Oct

277' mysql> SELECT DATE_FORMAT('1997-10-04

22:23:00', '%H %k %I %r %T %S

%w'); -> '22 22 10 10:23:00 PM 22:23:00 00

6' mysql> SELECT DATE_FORMAT('1999-01-01', '%X

%V'); -> '1998 52' 在 MySQL

3.23 中，在格式修饰符前需要字符 `%`。在更早的 MySQL 版本中，`%` 是可选的。

月份与天修饰符的范围从零开始的原因是，在 MySQL 3.23 中，它允许存储不完善的日期值(例如

'2009-00-00')。

四、其他时间函数

DAYOFWEEK(date) 返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。 mysql> select DAYOFWEEK('1998-02-03'); -> 3 WEEKDAY(date) 返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。 mysql> select WEEKDAY('1997-10-04 22:23:00'); -> 5 mysql> select WEEKDAY('1997-11-05'); -> 2 DAYOFMONTH(date) 返回date的月份中日期，在1到31范围内。 mysql> select DAYOFMONTH('1998-02-03'); -> 3 DAYOFYEAR(date) 返回date在一年中的日数, 在1到366范围内。 mysql> select DAYOFYEAR('1998-02-03'); -> 34 MONTH(date) 返回date的月份，范围1到12。 mysql> select MONTH('1998-02-03'); -> 2 DAYNAME(date) 返回date的星期名字。 mysql> select DAYNAME("1998-02-05"); -> 'Thursday' MONTHNAME(date) 返回date的月份名字。 mysql> select MONTHNAME("1998-02-05"); -> 'February' QUARTER(date) 返回date一年中的季度，范围1到4。 mysql> select QUARTER('98-04-01'); -> 2 WEEK(date) WEEK(date,first) 对于星期天是一周的第一天的地方，有一个单个参数，返回date的周数，范围在0到52。2个参数形式WEEK()允许 你指定星期是否开始于星期天或星期一。如果第二个参数是0，星期从星期天开始，如果第二个参数是1， 从星期一开始。 mysql> select WEEK('1998-02-20'); -> 7 mysql> select WEEK('1998-02-20',0); -> 7 mysql> select WEEK('1998-02-20',1); -> 8 YEAR(date) 返回date的年份，范围在1000到9999。 mysql> select YEAR('98-02-03'); -> 1998 HOUR(time) 返回time的小时，范围是0到23。 mysql> select HOUR('10:05:03'); -> 10 MINUTE(time) 返回time的分钟，范围是0到59。 mysql> select MINUTE('98-02-03 10:05:03'); -> 5 SECOND(time) 回来time的秒数，范围是0到59。 mysql> select SECOND('10:05:03'); -> 3 PERIOD_ADD(P,N) 增加N个月到阶段P(以格式YYMM或YYYYMM)。以格式YYYYMM返回值。注意阶段参数P不是日期值。 mysql> select PERIOD_ADD(9801,2); -> 199803 PERIOD_DIFF(P1,P2) 返回在时期P1和P2之间月数，P1和P2应该以格式YYMM或YYYYMM。注意，时期参数P1和P2不是日期值。 mysql> select PERIOD_DIFF(9802,199703); -> 11 DATE_ADD(date,INTERVAL expr type) DATE_SUB(date,INTERVAL expr type) ADDDATE(date,INTERVAL expr type) SUBDATE(date,INTERVAL expr type) 这些功能执行日期运算。对于MySQL 3.22，他们是新的。ADDDATE()和SUBDATE()是DATE_ADD()和DATE_SUB()的同义词。 在MySQL 3.23中，你可以使用+和-而不是DATE_ADD()和DATE_SUB()。(见例子)date是一个指定开始日期的 DATETIME或DATE值，expr是指定加到开始日期或从开始日期减去的间隔值一个表达式，expr是一个字符串；它可以以 一个“-”开始表示负间隔。type是一个关键词，指明表达式应该如何被解释。EXTRACT(type FROM date)函数从日期 中返回“type”间隔。下表显示了type和expr参数怎样被关联： type值 含义 期望的expr格式 SECOND 秒 SECONDS MINUTE 分钟 MINUTES HOUR 时间 HOURS DAY 天