PostgreSQL TO_CHAR() Data Type Formatting Function

PostgreSQL TO_CHAR() Data Type Formatting Function

The PostgreSQL TO_CHAR() function is used to convert various data types (timestamp, interval, numeric) to a string according to a specified format. This function is essential for formatting and displaying data in a readable and customized way.

Syntax

TO_CHAR(value, format)

The TO_CHAR() function has the following components:

  • value: The value to be converted, which can be of type timestamp, timestamp with time zone, interval, or numeric.
  • format: The format to which the value should be converted.

Example PostgreSQL TO_CHAR() Queries

Let's look at some examples of PostgreSQL TO_CHAR() function queries:

1. TO_CHAR() with Timestamp

SELECT TO_CHAR(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS') AS formatted_time;

This query converts the timestamp '2002-04-20 17:31:12.66' to a string in the format 'HH12:MI:SS', resulting in '05:31:12'.

2. TO_CHAR() with Timestamp with Time Zone

SELECT TO_CHAR(timestamp with time zone '2002-04-20 17:31:12.66-04', 'HH12:MI:SS TZ') AS formatted_time;

This query converts the timestamp with time zone '2002-04-20 17:31:12.66-04' to a string in the format 'HH12:MI:SS TZ', resulting in '05:31:12 EDT'.

3. TO_CHAR() with Interval

SELECT TO_CHAR(interval '15h 2m 12s', 'HH24:MI:SS') AS formatted_interval;

This query converts the interval '15h 2m 12s' to a string in the format 'HH24:MI:SS', resulting in '15:02:12'.

4. TO_CHAR() with Numeric

SELECT TO_CHAR(125, '999') AS formatted_number;

This query converts the number 125 to a string in the format '999', resulting in '125'.

SELECT TO_CHAR(125.8::real, '999D9') AS formatted_number;

This query converts the real number 125.8 to a string in the format '999D9', resulting in '125.8'.

SELECT TO_CHAR(-125.8, '999D99S') AS formatted_number;

This query converts the number -125.8 to a string in the format '999D99S', resulting in '125.80-'.

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the TO_CHAR() function to format various data types.

Step 1: Creating a Table

This step involves creating a new table named data to store different types of data.

CREATE TABLE data (
    id SERIAL PRIMARY KEY,
    timestamp_value TIMESTAMP,
    interval_value INTERVAL,
    numeric_value NUMERIC
);

In this example, we create a table named data with columns for timestamp_value, interval_value, and numeric_value.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the data table.

INSERT INTO data (timestamp_value, interval_value, numeric_value)
VALUES ('2002-04-20 17:31:12.66', '15h 2m 12s', 125.8);

Here, we insert data into the data table.

Step 3: Using the TO_CHAR() Function

This step involves using the TO_CHAR() function to format the data in the data table.

Format the timestamp value:

SELECT TO_CHAR(timestamp_value, 'HH12:MI:SS') AS formatted_time
FROM data;

This query converts the timestamp value to a string in the format 'HH12:MI:SS'.

Format the interval value:

SELECT TO_CHAR(interval_value, 'HH24:MI:SS') AS formatted_interval
FROM data;

This query converts the interval value to a string in the format 'HH24:MI:SS'.

Format the numeric value:

SELECT TO_CHAR(numeric_value, '999D9') AS formatted_number
FROM data;

This query converts the numeric value to a string in the format '999D9'.

Conclusion

The PostgreSQL TO_CHAR() function is a fundamental tool for formatting and displaying various data types in a readable and customized way. Understanding how to use the TO_CHAR() function and its syntax is essential for effective data presentation in PostgreSQL databases.