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.

Parameter description:

• date: input date or timestamp.
• mode (optional): An integer from 0 to 7 that defines the logic used to determine the number of the week. For details, see Table 1.

  The function accepts various integer values for the mode parameter. It converts these values into a standard range of 0 to 7 using the formula: (mode % 8 + 8) % 8. For example, 8 becomes 0, -1 becomes 7, and -2 becomes 6. You can enter any 32-bit integer, such as 2147483647.

Return type: integer

Example:

Return the year and week of a given date.

1
2
3
4
5

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

Set mode to -2 (equivalent to 6). The week begins on Sunday. Monday, January 1, 2024, falls as the second day of the first week. January 1, 2024 falls as the first week.

1
2
3
4
5

SELECT yearweek('2024-1-1', -2);
 yearweek 
----------
   202401
(1 row)

Set mode to -6 (equivalent to 2). The week begins on Wednesday. Monday, January 1, 2024, falls as the last week of the previous year (the 53th week of 2023).

1
2
3
4
5

SELECT yearweek('2024-1-1', -6);
 yearweek 
----------
   202353
(1 row)

Set mode to 2147483647 (equivalent to 7). The first week of the year starts on Monday. Monday, January 1, 2024, falls as the first day of the first week. January 1, 2024, falls as the first week.

1
2
3
4
5

SELECT yearweek('2024-1-1', 2147483647);
 yearweek 
----------
   202401
(1 row)