Time and Date Formatting Functions

date_format(timestamp, fmt)

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

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.

In the preceding table, %U, %u, %V, %v, %X, and %x are not supported currently.

time_format(time, fmt)

Description: Converts the date parameter into a string in the format specified by fmt. It is similar to the date_format function, but the format string can contain only hour, minute, second, and microsecond format specifiers. If other specifiers are contained, NULL or 0 is returned.

Return type: text

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

SELECT time_format('2009-10-04 22:23:00', '%M %D %W');
    time_format
--------------------
 
(1 row)
SELECT time_format('2021-02-20 08:30:45', '%Y-%m-%d %H:%i:%S');
     time_format
---------------------
 0000-00-00 08:30:45
(1 row)
SELECT time_format('2021-02-20 18:10:15', '%r-%T');
     time_format
----------------------
 06:10:15 PM-18:10:15
(1 row)

time_format supports only time-related formats (%f, %H, %h, %I, %i, %k, %l, %p, %r, %S, %s, and %T) and does not support date-related formats. In other cases, time_format is output as common characters.

str_to_date(str, format)

Description: Converts a string of the date or time type to a value of the date type according to the provided display format.

Return type: timestamp

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT str_to_date('01,5,2021','%d,%m,%Y');
     str_to_date
---------------------
 2021-05-01 00:00:00
(1 row)
SELECT str_to_date('01,5,2021,09,30,17','%d,%m,%Y,%h,%i,%s');
     str_to_date
---------------------
 2021-05-01 09:30:17
(1 row)

For details about the input format types applicable to str_to_date, see Table 1. Only input values of the date or date + time type can be converted. Use str_to_time when only values of the time type are input.

str_to_time(str, format)

Description: Converts a string of the time type to a value of the time type according to the provided display format.

Return type: time

Example:

1
2
3
4
5

SELECT str_to_time('09:30:17','%h:%i:%s');
 str_to_time
-------------
 09:30:17
(1 row)

For details about the input format types applicable to str_to_time, see Table 1. Only input values of the time type can be converted. Use str_to_date when values of the date or date + time type are input.