time_format

time_format(time, fmt)

Description: The time_format function 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 the format specifiers of hour, minute, second, and microsecond. If the format string contains other specifiers, NULL or 0 is returned.

Return type: text.

Example:

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

postgres=#SELECT time_format('2009-10-04 22:23:00', '%M %D %W');
    time_format
--------------------
 
(1 row)
postgres=#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)
postgres=#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). Date-related formats are not supported. Other cases are treated as ordinary characters.

str_to_date(str, format)

Description: Converts a string (str) in the date/time format to a value of the date type according to the provided display format (format).

Return type: timestamp.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

postgres=#SELECT str_to_date('01,5,2021','%d,%m,%Y');
     str_to_date
---------------------
 2021-05-01 00:00:00
(1 row)
postgres=#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)

Refer to Table 1 for the format types applicable to the formatted input of str_to_date. Here, only date format and date + time format inputs are supported. For scenarios involving only time format inputs, use str_to_time.

str_to_time(str, format)

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

Return type: time.

Example:

1
2
3
4
5

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

Refer to Table 1 for the format types applicable to the formatted input of str_to_time. Here, only time format inputs are supported. For scenarios involving date format and date + time format inputs, use str_to_date.

week(date[, mode])

Description: Returns the number of weeks in the year of the specified date and time based on the mode. The default mode is 0.

Return type: integer.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

postgres=#select week('2018-01-01');
 week
------
    0
(1 row)

postgres=#select week('2018-01-01', 0);
 week
------
    0
(1 row)

postgres=#select week('2020-12-31', 1);
 week
------
   53
(1 row)

postgres=#select week('2020-12-31', 5);
 week
------
   52
(1 row)

weekday(date)

Description: Returns the weekday index corresponding to the given date, with Monday as the start of the week.

Range: 0 to 6.

Return type: integer.

Example:

1
2
3
4
5

postgres=#select weekday('2020-11-06');
 weekday
---------
       4
(1 row)

weekofyear(date)

Description: Returns the number of weeks in the current year for the week of the given date. The value ranges from 1 to 53, which is equivalent to week(date, 3).

Return type: integer.

Example:

1
2
3
4
5

postgres=#select weekofyear('2020-12-30');
 weekofyear
------------
         53
(1 row)

year(date)

Description: Retrieves the year of the date.

Return type: integer.

Example:

1
2
3
4
5

postgres=#select year('2020-11-13');
 year
------
 2020
(1 row)

yearweek(date[, mode])

Description: Returns the year and the number of weeks in the current year corresponding to the given date. The number of weeks ranges from [1, 53].

Return type: integer.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

postgres=#select yearweek('2019-12-31');
 yearweek
----------
   201952
(1 row)

postgres=#select yearweek('2019-1-1');
 yearweek
----------
   201852
(1 row)