Introduction to PostgreSQL TO_CHAR
PostgreSQL provides a set of powerful tools that can help in converting different data types. These can be a date, integer, float to formatted strings. One of the functions which is widely used for this purpose is the TO_CHAR function. The TO_CHAR function in PostgreSQL is used to convert various data types like date or time, integer, floating-point to formatted strings, and also, they can be used to convert formatted strings to specific data types. They are a part of Data type formatting functions. Let us have a look in detail.
Syntax
The syntax for to_char() is as follows:
to_char(expression, format)
Explanation: The to_char() function has two arguments as mentioned above. Both of these arguments are mandatory. The first argument is expression. Bypassing an appropriate value to this argument the data can be converted to any given format. This format can be any of the ones which are explained below. The expression can be a decimal, date, or any numeric value which needs to be converted to character.
The second argument is the format. This argument signifies the format which is needed for the resultant string.
Return Value: The outcome of the To_Char() function is a string in text format. This string represents the first argument to be formatted according to the specified format. Please note there are other formats also which can be used in addition to the ones mentioned above. Let us have a look at them as well.
Various Formats
The main conversion which TO_CHAR does is for dates. The format for it can be as below:
TO_CHAR(datetime, format)
The format specifiers are as below,
| Oracle TO_CHAR | Format Specifier |
| YYYY | The year which can be used in 4 digit |
| YY | The year which can be used in 4 digit |
| MON | Abbreviation of Months in a three-letter word like Jan, Feb, etc |
| MONTH | Name of the entire month |
| MM | Numerical format for a month like 1 to 12 |
| DY | Abbreviated day (Sun-Sat) |
| DD | Numerical format for a day like a day from 1 to 31 |
| HH24 | Time in the 24-hour format which can be 00 to 24 |
| HH or HH12 | Time in the 12-hour format like |
| MI | Minutes which will be displayed between 0 to 59 |
| SS | Seconds which will be displayed between 0 to 59 |
In addition to these different patterns which can be used are as below:
Y, YYY or 9,99,999: The year in digits with a comma. This can also be used with numbers. It will return the comma at the mentioned position. Multiple commas can also be given in a given number
Month: The month should be specified in this format that is camel case.
MONTH: The month should be specified in all capital letters
WW: Week number of the year
9 and 0: They are the numeric values that represent the number of digits specified and the leading zeroes in it respectively.
D: It will return the specified position of the decimal character.
RN: It signifies the roman numbers which can range from 1 to 3999
L: This can be used as a currency symbol.
MI: The minus sign which has specified place for numbers which are less than 0.
PL: The plus sign which has specified place for numbers which are more than 0.
How does the PostgreSQL TO_CHAR function work?
Let us have a look at how the TO_CHAR() function works.
TO_CHAR(
TIMESTAMP '2017-08-18 22:30:59',
'HH24:MI:SS'
)
Here the function converts timestamp to a character string with the format of the 24-hour clock.
Code:
TO_CHAR(TIMESTAMP '2017-08-18 22:30:59', 'HH24:MI:SS')
TIMESTAMP '2017-08-18 22:30:59'
TO_CHAR()
Output: 22:30:59
Explanation: Consider the timestamp which takes the date and time as an input. This timestamp is given to the TO_CHAR() function. This function then converts to the format specified. The format here mentioned is HH24:MI: SS. This indicates the 24 hour clock time format. It converts it to the string format having an output as 22:30:59.
Example to Implement TO_CHAR in PostgreSQL
Below are examples to Implement TO_CHAR in PostgreSQL using various methods:
We now know what the to_char() function is and how it works. To understand better let us have a look at some examples.
Example #1
Let us convert a date variable to a string that uses the 24-hour format:
Code:
SELECT
SERVICE_END_DT,
TO_CHAR(
SERVICE_END_DT,
'HH12:MI: SS'
) END_DT
FROM
APP_MAESTRO.SBSCRBRDIM
ORDER BY
SERVICE_END_DT;
Output:
Explanation: Here the SERVICE_END_DT is being converted to time. The variable which will be for this converted time is END_DT. Above is the output of the query that we have run. It converts the time from 00:00:00 to 12:00:00 as we have specified the 24-hour format.
Example #2
Let us now convert an entire timestamp value into a different format:
Code:
SELECT
SERVICE_END_DT,
TO_CHAR(
SERVICE_END_DT,
'MON-DD-YYYY HH12: MIPM'
) END_DT
FROM
APP_MAESTRO.SBSCRBRDIM
ORDER BY
SERVICE_END_DT;
Output:
Explanation: The above code converts the service_end_dt to a completely different format. It is taking the Month in three-letter abbreviations, the day in a numbered format, the year in 4 digit number format. The time also is being taken in the 12-hour format with AM or PM is specified. The output that you see is JAN-01-1753 12:00 AM. It is as per the format we have specified.
Example #3
Let us add a currency symbol to the amount present in the table in this example:
Code:
SELECT
customer_key,
amount,
TO_CHAR(
amount,
'L99D99'
) amt
FROM
ODS_ABP.BL1_RC_RATES
ORDER BY
customer_key;
Output:
Explanation: The above example is selecting a customer key, amount. This amount is then being converted to the amount preceding the $ symbol. If you observe the format in to_char() function you will see that it specifies ‘L99D99’. This adds a ‘$’ in front of the amount specified. The output is hence -$34.00. Here we have made use of ‘L’ and ‘D’ which were specified in the formats. By using these we have added the currency symbol and the digit position as well.
Conclusion
Like all the functions the to_char() function is a very helpful formatting string function. It helps in formatting the numerical or date values to character or strings in the specified format. This helps in having a uniform format for the entire data set. With the different formats and patterns, it makes it easy to use and convert the data to string and use it or even compare if required.
Recommended Articles
This is a guide to PostgreSQL TO_CHAR. Here we discuss introduction to PostgreSQL TO_CHAR, syntax, how does it work, examples with code and output. You can also go through our other related articles to learn more –
