date_format
date_format(timestamp, fmt)
The date_format function converts a date into a string in the format specified by fmt.
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
postgres=#SELECT date_format('2009-10-04 22:23:00', '%M %D %W'); date_format -------------------- October 4th Sunday (1 row) postgres=#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) postgres=#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 format types that can be used to format date parameters for output. These format types are applicable to the functions date_format, time_format, str_to_date, str_to_time, and from_unixtime.
Format |
Description |
Value |
---|---|---|
%a |
Abbreviated week name |
Sun...Sat |
%b |
Abbreviated month name |
Jan...Dec |
%c |
Month |
0...12 |
%D |
Day of the month with English suffix |
0th, 1st, 2nd, 3rd, ... |
%d |
Day of a month, two digits |
00...31 |
%e |
Day of a month |
0...31 |
%f |
Microseconds |
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 |
Minutes |
00...59 |
%j |
Day of 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 |
Full month name |
January...December |
%m |
Month, two digits |
00...12 |
%p |
AM or PM |
AM PM |
%r |
Time, in 12-hour format |
hh::mm::ss AM/PM |
%S |
Seconds |
00...59 |
%s |
Seconds, same as %S |
00...59 |
%T |
Time, in 24-hour format |
hh::mm::ss |
%U |
Week (00–53), Sunday is the first day of the week |
00...53 |
%u |
Week (00–53), Monday is the first day of the week |
00...53 |
%V |
Week (01–53), Sunday is the first day of the week, used with %X |
01...53 |
%v |
Week (01–53), Monday is the first day of the week, used with %x |
01...53 |
%W |
Full weekday name |
Sunday...Saturday |
%w |
Day of the week, where Sunday is 0 |
0...6 |
%X |
Year where Sunday is the first day of the week, 4 digits, used with %V |
- |
%x |
Year where Monday is the first day of the week, 4 digits, used with %v |
- |
%Y |
Year, 4 digits |
- |
%y |
Year, 2 digits |
- |
%% |
Literal '%' |
% |
%x |
'x', any character not listed above |
x |

Among the output formats supported by date_format, %U, %u, %V, %v, %X, and %x are not currently supported.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot