date_format¶
date_format(timestamp, fmt)¶
Converts a date into a string in the format specified by fmt.
Example:
SELECT date_format('2009-10-04 22:23:00', '%M %D %W');
date_format
--------------------
October 4th Sunday
(1 row)
SELECT date_format('2021-02-20 08:30:45', '%Y-%m-%d %H:%i:%S');
date_format
---------------------
2021-02-20 08:30:45
(1 row)
SELECT date_format('2021-02-20 18:10:15', '%r-%T');
date_format
----------------------
06:10:15 PM-18:10:15
(1 row)
Table 1 describes the patterns of date parameter values. They can be used for the date_format, time_format, str_to_date, str_to_time, and from_unixtime functions.
Format | Description | Value |
|---|---|---|
%a | Abbreviated week name | Sun...Sat |
%b | Abbreviated month name | Jan...Dec |
%c | Month | 0...12 |
%D | Date with a suffix | 0th, 1st, 2nd, 3rd, ... |
%d | Day in a month (two digits) | 00...31 |
%e | Day in a month | 0...31 |
%f | Microsecond | 000000...999999 |
%H | Hour, in 24-hour format | 00...23 |
%h | Hour, in 12-hour format | 01...12 |
%I | Hour, in 12-hour format, same as %h | 01...12 |
%i | Minute | 00...59 |
%j | Day in a year | 001...366 |
%k | Hour, in 24-hour format, same as %H | 0...23 |
%l | Hour, in 12-hour format, same as %h | 1...12 |
%M | Month name | January...December |
%m | Month (two digits) | 00...12 |
%p | Morning and afternoon | AM PM |
%r | Time, in 12-hour format | hh::mm::ss AM/PM |
%S | Second | 00...59 |
%s | Second, same as %S | 00...59 |
%T | Time, in 24-hour format | hh::mm::ss |
%U | Week (Sunday is the first day of a week.) | 00...53 |
%u | Week (Monday is the first day of a week.) | 00...53 |
%V | Week (Sunday is the first day of a week). It is used together with %X. | 01...53 |
%v | Week (Monday is the first day of a week). It is used together with %x. | 01...53 |
%W | Week name | Sunday...Saturday |
%w | Day of a week. The value is 0 for Sunday. | 0...6 |
%X | Year (four digits). It is used together with %V. Sunday is the first day of a week. |
|
%x | Year (four digits). It is used together with %v. Monday is the first day of a week. |
|
%Y | Year (four digits) |
|
%y | Year (two digits) |
|
%% | Character '%' | Character '%' |
%x | 'x': any character apart from the preceding ones | Character 'x' |
Important
In the preceding table, %U, %u, %V, %v, %X, and %x are not supported currently.