美文网首页
MySQL日期和时间的格式化

MySQL日期和时间的格式化

作者: 月饮沙 | 来源:发表于2020-04-24 16:17 被阅读0次

    本文问题

    1. 如何对日期和时间的格式进行修改?
    2. 如何将字符串转换为时间?
    3. 日期的格式有哪些?
    4. 时间的格式有哪些?
    5. 星期的格式有那些?
    6. 除了以上三种以外,MySQL还可以对日期和时间进行哪些部分的格式化?

    更改日期时间格式

    • 格式化日期 DATE_FORMAT(date,format)
    • 格式化时间 TIME_FORMAT(time,format)
      类似DATE_FORMAT,但是format中只能包括时间相关的选项,包括其他选项会导致结果为NULL0
      如果时间的小时部分大于23,%H Hour (00..23)%k Hour (0..23)可以显示具体时间,其他格式会将时间对12取余。
    mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
            -> 'Sunday October 2009'
    mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
            -> '22:23:00'
    mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
        ->                 '%D %y %a %d %m %b %j');
            -> '4th 00 Thu 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> SELECT DATE_FORMAT('2006-06-00', '%d');
            -> '00'
    mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
            -> '100 100 04 04 4'
    
    • 字符串转日期 STR_TO_DATE(str,format)
    # 字符串转日期
    mysql> SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');
    +-------------------------------------+
    | STR_TO_DATE('01,5,2013','%d,%m,%Y') |
    +-------------------------------------+
    | 2013-05-01                          |
    +-------------------------------------+
    1 row in set (0.00 sec)
    # 字符串和日期格式必须匹配,如果不匹配,会返回NULL
    mysql> SELECT STR_TO_DATE('a09:30:17','a%h:%i:%s'),STR_TO_DATE('a09:30:17','%h:%i:%s');
    +--------------------------------------+-------------------------------------+
    | STR_TO_DATE('a09:30:17','a%h:%i:%s') | STR_TO_DATE('a09:30:17','%h:%i:%s') |
    +--------------------------------------+-------------------------------------+
    | 09:30:17                             | NULL                                |
    +--------------------------------------+-------------------------------------+
    1 row in set, 1 warning (0.00 sec)
    
    • 合成日期 MAKEDATE(year,dayofyear)
    • 合成时间 MAKETIME(hour,minute,second)
    mysql> select makedate(2020,155),maketime(11,15,22);
    +--------------------+--------------------+
    | makedate(2020,155) | maketime(11,15,22) |
    +--------------------+--------------------+
    | 2020-06-03         | 11:15:22           |
    +--------------------+--------------------+
    1 row in set (0.00 sec)
    
    • 将日期转换为指定格式 GET_FORMAT({DATE|TIME|DATETIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})

    format详细说明

    日期相关

      • %Y 年份,四位数字
      • %y 年份,两位数字
      • %M 月份,英文全拼 (January..December)
      • %m 月份,两位数值 (00..12)
      • %c 月份,数值 (0..12)
      • %b 月份,英文缩写 (Jan..Dec)
      • 日期 %D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
      • %d 日期,两位数值 (00..31)
      • %e 日期,数值 (0..31)

    时间相关

    • 时间
      • %r 12小时制时间 (hh:mm:ss followed by AM or PM)
      • %T 24小时制时间 (hh:mm:ss)
      • %H Hour (00..23)
      • %k Hour (0..23)
      • %h Hour (01..12)
      • %I Hour (01..12)
      • %l Hour (1..12)
      • %p AM or PM
      • %i 分钟,两位数值 (00..59)
      • %S 秒数 (00..59)
      • %s 秒数 (00..59)
      • %f 微秒 (000000..999999)

    星期相关

    • %W 星期,英文全拼 (Sunday..Saturday)
    • %a 星期,英文缩写 (Sun..Sat)
    • %w Day of the week (0=Sunday..6=Saturday)

    周计数
    %U Week (00..53), where Sunday is the first day of the week; WEEK() mode 0
    %u Week (00..53), where Monday is the first day of the week; WEEK() mode 1
    %V Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
    %v Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x
    %X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
    %x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v

    其他

    • %j Day of year (001..366)
    • %% A literal % character
    • %x x, for any “x” not listed above

    GET_FORMAT详细说明

    • 将日期转换为指定格式 GET_FORMAT({DATE|TIME|DATETIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})
    Function Call Result
    GET_FORMAT(DATE,'USA') '%m.%d.%Y'
    GET_FORMAT(DATE,'JIS') '%Y-%m-%d'
    GET_FORMAT(DATE,'ISO') '%Y-%m-%d'
    GET_FORMAT(DATE,'EUR') '%d.%m.%Y'
    GET_FORMAT(DATE,'INTERNAL') '%Y%m%d'
    GET_FORMAT(DATETIME,'USA') '%Y-%m-%d %H.%i.%s'
    GET_FORMAT(DATETIME,'JIS') '%Y-%m-%d %H:%i:%s'
    GET_FORMAT(DATETIME,'ISO') '%Y-%m-%d %H:%i:%s'
    GET_FORMAT(DATETIME,'EUR') '%Y-%m-%d %H.%i.%s'
    GET_FORMAT(DATETIME,'INTERNAL') '%Y%m%d%H%i%s'
    GET_FORMAT(TIME,'USA') '%h:%i:%s %p'
    GET_FORMAT(TIME,'JIS') '%H:%i:%s'
    GET_FORMAT(TIME,'ISO') '%H:%i:%s'
    GET_FORMAT(TIME,'EUR') '%H.%i.%s'
    GET_FORMAT(TIME,'INTERNAL') '%H%i%s'

    问题答案

    1. 如何对日期和时间的格式进行修改?
      date_format(date,format),time_format(time,format)
      time_format中的格式只能包含时间格式,否则会返回NULL0
      GET_FORMAT({DATE|TIME|DATETIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'}) 更改成指定格式
    2. 如何将字符串转换为时间?
      str_to_date(str,format)
    3. 日期的格式有哪些?
      年:%Y四位,%y两位
      月:%M英文全拼,%m两位数字,%c 数字,%b 英文缩写
      日:%D带英文后缀,%d两位数字,%e数字
    4. 时间的格式有哪些?
      时间:%T24小时制时间,%r12小时制时间
      时:%H,%k 24小时制,%h,%I 12小时制 两位数字,%l 12小时制数字
      分:%i 两位数字
      秒:%s %S 两位数字
      微秒:%f
      上午/下午:%p
    5. 星期的格式有那些?
      %w 数字(0-Sun...6-Sat),%W 星期全拼,%a 星期缩写
    6. 除了以上三种以外,MySQL还可以对日期和时间进行哪些部分的格式化?
      日期是本年的第几天(%j)和第几周(%U,%u,%V,%v)
      用年份+周数来表示日期(%X,%x

    相关文章

      网友评论

          本文标题:MySQL日期和时间的格式化

          本文链接:https://www.haomeiwen.com/subject/cpjfwhtx.html