PostgreSQL MAKE_TIMESTAMPTZ Date/Time Function

PostgreSQL MAKE_TIMESTAMPTZ Date/Time Function

The PostgreSQL MAKE_TIMESTAMPTZ function is used to create a timestamp with time zone value from specified year, month, day, hour, minute, and seconds fields. If the timezone is not specified, the current time zone is used. This function is essential for constructing timestamp with time zone values from individual components, which can be useful in various date and time-based calculations and data manipulations.

---

Syntax

MAKE_TIMESTAMPTZ(year int, month int, day int, hour int, min int, sec double precision [, timezone text])

The MAKE_TIMESTAMPTZ function has the following components:

• year: The year component of the timestamp (negative years signify BC).
• month: The month component of the timestamp.
• day: The day component of the timestamp.
• hour: The hour component of the timestamp.
• min: The minute component of the timestamp.
• sec: The seconds component of the timestamp.
• timezone: The optional time zone for the timestamp.

---

Example PostgreSQL MAKE_TIMESTAMPTZ Queries

Let's look at some examples of PostgreSQL MAKE_TIMESTAMPTZ function queries:

1. Basic MAKE_TIMESTAMPTZ Example

SELECT MAKE_TIMESTAMPTZ(2013, 7, 15, 8, 15, 23.5) AS constructed_timestamptz;

This query creates a timestamp with time zone from the year 2013, month 7, day 15, hour 8, minute 15, and seconds 23.5, resulting in '2013-07-15 08:15:23.5+01' assuming the session time zone is Europe/London.

2. MAKE_TIMESTAMPTZ with Specified Time Zone

SELECT MAKE_TIMESTAMPTZ(2013, 7, 15, 8, 15, 23.5, 'America/New_York') AS constructed_timestamptz;

This query creates a timestamp with time zone from the year 2013, month 7, day 15, hour 8, minute 15, and seconds 23.5, with the time zone 'America/New_York', resulting in '2013-07-15 13:15:23.5+01' assuming the session time zone is Europe/London.

3. MAKE_TIMESTAMPTZ with Column Values

SELECT id, name, MAKE_TIMESTAMPTZ(year, month, day, hour, minute, second, timezone) AS event_timestamptz
FROM events;

This query retrieves the id, name, and constructs a timestamp with time zone from the year, month, day, hour, minute, second, and timezone columns 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 MAKE_TIMESTAMPTZ function to construct timestamp with time zone values from individual components.

Step 1: Creating a Table

This step involves creating a new table named events to store event data, including their timestamp components and time zone.

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name TEXT,
    year INT,
    month INT,
    day INT,
    hour INT,
    minute INT,
    second DOUBLE PRECISION,
    timezone TEXT
);

In this example, we create a table named events with columns for id, name, year, month, day, hour, minute, second, and timezone.

Step 2: Inserting Data into the Table

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

INSERT INTO events (name, year, month, day, hour, minute, second, timezone)
VALUES ('Meeting', 2021, 10, 31, 9, 30, 0, 'Europe/London'),
       ('Conference', 2022, 5, 15, 14, 0, 0, 'America/New_York'),
       ('Webinar', 2023, 8, 20, 11, 45, 30.75, 'Asia/Tokyo');

Here, we insert data into the events table.

Step 3: Using the MAKE_TIMESTAMPTZ Function

This step involves using the MAKE_TIMESTAMPTZ() function to construct timestamp with time zone values from the individual components in the events table.

Construct event timestamp with time zone from year, month, day, hour, minute, second, and timezone columns:

SELECT id, name, MAKE_TIMESTAMPTZ(year, month, day, hour, minute, second, timezone) AS event_timestamptz
FROM events;

This query constructs the event timestamp with time zone from the year, month, day, hour, minute, second, and timezone columns for each row in the events table.

---

Conclusion

The PostgreSQL MAKE_TIMESTAMPTZ() function is a fundamental tool for constructing timestamp with time zone values from individual year, month, day, hour, minute, second, and optional timezone components. Understanding how to use the MAKE_TIMESTAMPTZ() function and its syntax is essential for accurate timestamp construction in PostgreSQL databases.