One of the standout features of PostgreSQL is its diverse collection of data type formatting functions, such as TO_CHAR(), TO_TIMESTAMP(), etc. These formatting functions allow us to convert data from one type to another, format it in a specific way, or even extract specific parts of a date or time value. These functions assist us in manipulating the data and presenting it in a clear and concise manner.
This Postgres blog will explain the below-listed formatting functions with syntax and examples:
- Postgres TO_CHAR() Function
- Postgres TO_NUMBER() Function
- Postgres TO_DATE() Function
- Postgres TO_TIMESTAMP() Function
Let’s begin with the TO_CHAR() function!
Postgres TO_CHAR() Function
TO_CHAR() is one of the most widely used formatting functions that convert a timestamp to string, interval to string, or number to string based on the specified format.
Syntax
The TO_CHAR() function accepts an expression and a format as arguments:
TO_CHAR(expression, format);
The expression can be an interval, a timestamp, or a numeric value that will be converted to a string based on the given format. The format must be valid as described in Postgres’ official documentation.
Example: How to Format a Timestamp in Postgres?
Let’s learn how to format a timestamp in Postgres via the TO_CHAR() function:
SELECT TO_CHAR(TIMESTAMP '2022-11-18 22:11:16', 'YYYY/MM/DD HH12:MI:SS');
In the above snippet,
- A TIMESTAMP '2022-11-18 22:11:16' is passed as an argument to the TO_CHAR() function.
- A valid format “YYYY/MM/DD HH12:MI:SS” is passed as an argument to the TO_CHAR() function.
- The TO_CHAR() function will convert the given timestamp according to the given format and retrieves a string as an output:
The input timestamp has been converted into a string based on the specified format. Visit the following blog post to learn more about the TO_CHAR() function.
Postgres TO_NUMBER() Function
TO_NUMBER() is a formatting function that converts a string into a number based on the given format.
Syntax
The below snippet shows the basic syntax of the TO_NUMBER() function:
TO_NUMBER(numeric_val, format);
The “numeric_val” argument represents a numeric string to be converted based on the specified format. The format must be a valid numeric pattern, as stated in Postgres’ official documentation.
Example: How to Convert a Number to a String in Postgres Via the TO_NUMBER() Function?
In the following example, we will show you how to convert a numeric string to a number using the TO_NUMBER() function:
SELECT TO_NUMBER(-572.14, 'S999D99');
Here, in the above example, we specified “S” that represents a sign, each “9” represents a single digit, and “D” represents a decimal point. On successful execution, the given numeric string will be converted into a number, as shown in the following snippet:
The output authenticates the working of the TO_NUMBER() function. Read the following article for a profound understanding of Postgres’ TO_NUMBER() function.
Postgres TO_DATE() Function
The TO_DATE() is a built-in formatting function that converts a date string to date based on the specified format.
Syntax
The below snippet shows the basic syntax for the TO_DATE() function:
TO_DATE(string, Format);
The format must be a valid date pattern as described in Postgres’ official documentation.
Example: How to Convert a String to a Date Via the TO_DATE() Function in Postgres?
Let’s pass a string and a valid date format to the TO_DATE() function:
SELECT TO_DATE('2022/12/14', 'YYYY/MM/DD');
The output snippet proves that the given string has been successfully converted into a date. To learn more about the TO_DATE() function, you must visit the following link.
Postgres TO_TIMESTAMP() Function
The TO_TIMESTAMP() function takes a string and a valid date format as arguments and converts the string into a timestamp.
Syntax
Here is the basic syntax of the Postgres’ TO_TIMESTAMP() function:
TO_TIMESTAMP(string, ‘date_format’);
The format must be a valid date pattern as described in Postgres’ official documentation.
Example: How to Convert a String Into a Timestamp Via the TO_TIMESTAMP() Function?
Let’s learn how the TO_TIMESTAMP() function works in Postgres via the following example:
SELECT TO_TIMESTAMP('2022-07-12', 'YYYY-MM-DD');
The output snippet proves that the given string has been successfully converted into a TIMESTAMP. Visit the following blog for a profound understanding of Postgres’ TO_TIMESTAMP() function.
That’s it from this blog!
Conclusion
PostgreSQL provides various built-in formatting functions such as TO_CHAR(), TO_TIMESTAMP(), etc. These formatting functions allow us to convert data from one type to another based on some valid format. The data formatting functions assist us in manipulating the data and presenting it in a clear and concise manner. This blog post explained the working of various built-in data type formatting functions via practical examples.
