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 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/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.

week(date[, mode])

Description: Returns the number of weeks in the year of the specified datetime. The default value 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

SELECT week('2018-01-01');
 week
------
    0
(1 row)

SELECT week('2018-01-01', 0);
 week
------
    0
(1 row)

SELECT week('2020-12-31', 1);
 week
------
   53
(1 row)

SELECT week('2020-12-31', 5);
 week
------
   52
(1 row)

weekday(date)

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

Value range: 0 to 6

Return type: integer

Example:

1
2
3
4
5

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

SELECT weekofyear('2020-12-30');
 weekofyear
------------
         53
(1 row)

year(date)

Description: Obtains the year of the date.

Return type: integer

Example:

1
2
3
4
5

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 for the given date. The number of weeks ranges from 1 to 53.

Return type: integer

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT yearweek('2019-12-31');
 yearweek
----------
   201952
(1 row)

SELECT yearweek('2019-1-1');
 yearweek
----------
   201852
(1 row)