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.
Mode |
First Day of the Week |
Week Range |
Rule for Determining the First Week |
---|---|---|---|
0 |
Sunday |
0–53 |
Week containing the first Sunday after New Year's Day |
1 |
Monday |
0–53 |
Week with four or more days after New Year's Day |
2 |
Sunday |
1-53 |
Week containing the first Sunday after New Year's Day |
3 |
Monday |
1-53 |
Week with four or more days after New Year's Day |
4 |
Sunday |
0–53 |
Week with four or more days after New Year's Day |
5 |
Monday |
0–53 |
Week containing the first Monday after New Year's Day |
6 |
Sunday |
1-53 |
Week with four or more days after New Year's Day |
7 |
Monday |
1-53 |
Week containing the first Monday after New Year's Day |
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) |
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