TIME_FORMAT

描述

TIME_FORMAT 函数用于将时间值按照指定的格式字符串（format）转换为字符串。支持对 TIME 和 DATETIME 类型进行格式化，输出结果为符合格式要求的字符串。

该函数与 mysql 中的 time_format 函数行为一致。

语法

TIME_FORMAT(<time_or_datetime_expr>, <format>)

参数

参数	说明
`<time_or_datetime_expr>`	合法的时间值，支持为 TIME 或者 DATETIME 类型。
`<format>`	规定时间的输出格式，为 `varchar` 类型。

支持的 format 格式：

格式符	描述
%f	微秒 (000000-999999)
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值 (00-59)
%k	小时 (0-23)
%l	小时 (1-12)
%p	AM 或 PM (由 hh % 24 的值决定,)
%r	时间，12-小时（hh:mm:ss, 后跟 AM 或 PM）
%S	秒 (00-59)
%s	秒 (00-59)
%T	时间，24-小时 (hh:mm:ss)
%%	用于表示 %

返回值

格式化后的时间字符串。当输入的时间值为负时，仅会在结果开头添加 -。

特殊情况：

如果任一参数为 NULL，则返回 NULL。
如果 format 字符串包含日期格式符：
- %Y: 返回 0000。
- %y, %m, %d: 返回 00。
- %c, %e: 返回 0。
- 其他日期格式符（%D, %j, %M, %u, %U, %v, %V, %w, %W, %x, %X）: 返回 NULL。
如果时间值中的小时部分大于 23，%H 和 %k 格式符会产生大于 23 的值。其他小时格式符则产生小时值对 12 取模的结果。

举例

SELECT * FROM test_time_format;

+------+-------------------+
| id   | tm                |
+------+-------------------+
|    1 | 00:00:00          |
|    2 | 00:00:00.123456   |
|    3 | 12:34:56          |
|    4 | 12:34:56.789012   |
|    5 | 23:59:59          |
|    6 | 23:59:59.999999   |
|    7 | 08:00:00          |
|    8 | 15:00:00          |
|    9 | 100:00:00         |
|   10 | 123:45:56         |
|   11 | 838:59:59.999999  |
|   12 | -00:00:01         |
|   13 | -12:34:56.000001  |
|   14 | -838:59:59.999999 |
+------+-------------------+

SELECT
    id,
    tm,
    TIME_FORMAT(tm, '%H') AS '%H',
    TIME_FORMAT(tm, '%k') AS '%k',
    TIME_FORMAT(tm, '%h') AS '%h',
    TIME_FORMAT(tm, '%I') AS '%I',
    TIME_FORMAT(tm, '%l') AS '%l',
    TIME_FORMAT(tm, '%i') AS '%i',
    TIME_FORMAT(tm, '%s') AS '%s',
    TIME_FORMAT(tm, '%S') AS '%S',
    TIME_FORMAT(tm, '%f') AS '%f',
    TIME_FORMAT(tm, '%p') AS '%p',
    TIME_FORMAT(tm, '%r') AS '%r',
    TIME_FORMAT(tm, '%T') AS '%T',
    TIME_FORMAT(tm, '%H:%i:%s.%f') AS '%H:%i:%s.%f',
    TIME_FORMAT(tm, '%k %H %l %I %h') AS '%k %H %l %I %h',
    TIME_FORMAT(tm, '%T %r %h:%I') AS '%T %r %h:%I',
    TIME_FORMAT(tm, '%l %k %I %H %h %p') AS '%l %k %I %H %h %p',
    TIME_FORMAT(tm, '%f %s %i %T %r') AS '%f %s %i %T %r'
FROM test_time_format
ORDER BY id;

+------+-------------------+------+------+------+------+------+------+------+------+---------+------+--------------+-----------+------------------+-----------------+-----------------------------+--------------------+------------------------------------+
| id   | tm                | %H   | %k   | %h   | %I   | %l   | %i   | %s   | %S   | %f      | %p   | %r           | %T        | %H:%i:%s.%f      | %k %H %l %I %h  | %T %r %h:%I                 | %l %k %I %H %h %p  | %f %s %i %T %r                     |
+------+-------------------+------+------+------+------+------+------+------+------+---------+------+--------------+-----------+------------------+-----------------+-----------------------------+--------------------+------------------------------------+
|    1 | 00:00:00          | 00   | 0    | 12   | 12   | 12   | 00   | 00   | 00   | 000000  | AM   | 12:00:00 AM  | 00:00:00  | 00:00:00.000000  | 0 00 12 12 12   | 00:00:00 12:00:00 AM 12:12  | 12 0 12 00 12 AM   | 000000 00 00 00:00:00 12:00:00 AM  |
|    2 | 00:00:00.123456   | 00   | 0    | 12   | 12   | 12   | 00   | 00   | 00   | 123456  | AM   | 12:00:00 AM  | 00:00:00  | 00:00:00.123456  | 0 00 12 12 12   | 00:00:00 12:00:00 AM 12:12  | 12 0 12 00 12 AM   | 123456 00 00 00:00:00 12:00:00 AM  |
|    3 | 12:34:56          | 12   | 12   | 12   | 12   | 12   | 34   | 56   | 56   | 000000  | PM   | 12:34:56 PM  | 12:34:56  | 12:34:56.000000  | 12 12 12 12 12  | 12:34:56 12:34:56 PM 12:12  | 12 12 12 12 12 PM  | 000000 56 34 12:34:56 12:34:56 PM  |
|    4 | 12:34:56.789012   | 12   | 12   | 12   | 12   | 12   | 34   | 56   | 56   | 789012  | PM   | 12:34:56 PM  | 12:34:56  | 12:34:56.789012  | 12 12 12 12 12  | 12:34:56 12:34:56 PM 12:12  | 12 12 12 12 12 PM  | 789012 56 34 12:34:56 12:34:56 PM  |
|    5 | 23:59:59          | 23   | 23   | 11   | 11   | 11   | 59   | 59   | 59   | 000000  | PM   | 11:59:59 PM  | 23:59:59  | 23:59:59.000000  | 23 23 11 11 11  | 23:59:59 11:59:59 PM 11:11  | 11 23 11 23 11 PM  | 000000 59 59 23:59:59 11:59:59 PM  |
|    6 | 23:59:59.999999   | 23   | 23   | 11   | 11   | 11   | 59   | 59   | 59   | 999999  | PM   | 11:59:59 PM  | 23:59:59  | 23:59:59.999999  | 23 23 11 11 11  | 23:59:59 11:59:59 PM 11:11  | 11 23 11 23 11 PM  | 999999 59 59 23:59:59 11:59:59 PM  |
|    7 | 08:00:00          | 08   | 8    | 08   | 08   | 8    | 00   | 00   | 00   | 000000  | AM   | 08:00:00 AM  | 08:00:00  | 08:00:00.000000  | 8 08 8 08 08    | 08:00:00 08:00:00 AM 08:08  | 8 8 08 08 08 AM    | 000000 00 00 08:00:00 08:00:00 AM  |
|    8 | 15:00:00          | 15   | 15   | 03   | 03   | 3    | 00   | 00   | 00   | 000000  | PM   | 03:00:00 PM  | 15:00:00  | 15:00:00.000000  | 15 15 3 03 03   | 15:00:00 03:00:00 PM 03:03  | 3 15 03 15 03 PM   | 000000 00 00 15:00:00 03:00:00 PM  |
|    9 | 100:00:00         | 100  | 100  | 04   | 04   | 4    | 00   | 00   | 00   | 000000  | AM   | 04:00:00 AM  | 100:00:00 | 100:00:00.000000 | 100 100 4 04 04 | 100:00:00 04:00:00 AM 04:04 | 4 100 04 100 04 AM | 000000 00 00 100:00:00 04:00:00 AM |
|   10 | 123:45:56         | 123  | 123  | 03   | 03   | 3    | 45   | 56   | 56   | 000000  | AM   | 03:45:56 AM  | 123:45:56 | 123:45:56.000000 | 123 123 3 03 03 | 123:45:56 03:45:56 AM 03:03 | 3 123 03 123 03 AM | 000000 56 45 123:45:56 03:45:56 AM |
|   11 | 838:59:59.999999  | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL    | NULL | NULL         | NULL      | NULL             | NULL            | NULL                        | NULL               | NULL                               |
|   12 | -00:00:01         | -00  | -0   | -12  | -12  | -12  | -00  | -01  | -01  | -000000 | -AM  | -12:00:01 AM | -00:00:01 | -00:00:01.000000 | -0 00 12 12 12  | -00:00:01 12:00:01 AM 12:12 | -12 0 12 00 12 AM  | -000000 01 00 00:00:01 12:00:01 AM |
|   13 | -12:34:56.000001  | -12  | -12  | -12  | -12  | -12  | -34  | -56  | -56  | -000001 | -PM  | -12:34:56 PM | -12:34:56 | -12:34:56.000001 | -12 12 12 12 12 | -12:34:56 12:34:56 PM 12:12 | -12 12 12 12 12 PM | -000001 56 34 12:34:56 12:34:56 PM |
|   14 | -838:59:59.999999 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL    | NULL | NULL         | NULL      | NULL             | NULL            | NULL                        | NULL               | NULL                               |
+------+-------------------+------+------+------+------+------+------+------+------+---------+------+--------------+-----------+------------------+-----------------+-----------------------------+--------------------+------------------------------------+

注: 对于参数 838:59:59.999999 和 -838:59:59.999999, 由于超过了 TIME 的范围[-838:59:59, 838:59:59] 无法转换为 TIME 类型，故返回结果为 NULL

```sql
-- 占位符为 %Y, %y, %m, %d, %c, %e时，返回对应位数的 0
SELECT fmt, TIME_FORMAT('12:13:14.123456', fmt) AS res FROM test_format;

+------+------+
| fmt  | res  |
+------+------+
| %Y   | 0000 |
| %y   | 00   |
| %m   | 00   |
| %d   | 00   |
| %c   | 0    |
| %e   | 0    |
+------+------+

-- 其他占位符(`%D`, `%j`, `%M`, `%u`, `%U`, `%v`, `%V`, `%w`, `%W`, `%x`, `%X`)返回 NULL
SELECT TIME_FORMAT('11:22:33', '%h:%i:%s, %j');

+-----------------------------------------+
| TIME_FORMAT('11:22:33', '%h:%i:%s, %j') |
+-----------------------------------------+
| NULL                                    |
+-----------------------------------------+

-- 任一参数为 NULL 则返回 NULL
SELECT TIME_FORMAT('12:34:56', NULL);

+-------------------------------+
| TIME_FORMAT('12:34:56', NULL) |
+-------------------------------+
| NULL                          |
+-------------------------------+

描述​

语法​

参数​

返回值​

举例​

描述

语法

参数

返回值

举例