PostgreSQL TO_TIMESTAMP() Data Type Formatting Function

PostgreSQL TO_TIMESTAMP() Data Type Formatting Function

The PostgreSQL TO_TIMESTAMP() function is used to convert a string to a timestamp with time zone according to a specified format. This function is essential for transforming text representations of timestamps into actual timestamp values for accurate time-based calculations and comparisons.

Syntax

TO_TIMESTAMP(text, format)

The TO_TIMESTAMP() function has the following components:

  • text: The string to be converted to a timestamp.
  • format: The format to which the string should be converted.

Example PostgreSQL TO_TIMESTAMP() Queries

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

1. Basic TO_TIMESTAMP() Example

SELECT TO_TIMESTAMP('05 Dec 2000', 'DD Mon YYYY') AS formatted_timestamp;

This query converts the string '05 Dec 2000' to a timestamp in the format 'DD Mon YYYY', resulting in '2000-12-05 00:00:00+00'.

2. TO_TIMESTAMP() with Different Date and Time Format

SELECT TO_TIMESTAMP('2024-06-04 14:30:00', 'YYYY-MM-DD HH24:MI:SS') AS formatted_timestamp;

This query converts the string '2024-06-04 14:30:00' to a timestamp in the format 'YYYY-MM-DD HH24:MI:SS', resulting in '2024-06-04 14:30:00+00'.

3. TO_TIMESTAMP() with Column Values

SELECT id, TO_TIMESTAMP(event_time_text, 'DD/MM/YYYY HH24:MI') AS event_time
FROM events;

This query retrieves the id and the event time converted from the text representation in 'DD/MM/YYYY HH24:MI' format for each row in the events table.

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the TO_TIMESTAMP() function to convert text to timestamp values.

Step 1: Creating a Table

This step involves creating a new table named events to store event data, including their times as text.

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name TEXT,
    event_time_text TEXT
);

In this example, we create a table named events with columns for id, event_name, and event_time_text.

Step 2: Inserting Data into the Table

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

INSERT INTO events (event_name, event_time_text)
VALUES ('Meeting', '05/12/2000 14:30'),
       ('Conference', '23/05/2024 09:00'),
       ('Webinar', '15/08/2022 16:45');

Here, we insert data into the events table.

Step 3: Using the TO_TIMESTAMP() Function

This step involves using the TO_TIMESTAMP() function to convert the text event time values to actual timestamp values in the events table.

Convert text event time to timestamp value:

SELECT id, event_name, TO_TIMESTAMP(event_time_text, 'DD/MM/YYYY HH24:MI') AS event_time
FROM events;

This query converts the text event time values to timestamp values in the format 'DD/MM/YYYY HH24:MI'.

Conclusion

The PostgreSQL TO_TIMESTAMP() function is a fundamental tool for transforming text representations of timestamps into actual timestamp values. Understanding how to use the TO_TIMESTAMP() function and its syntax is essential for accurate time-based calculations and comparisons in PostgreSQL databases.