Using MAKE_TIMESTAMPTZ Function in PostgreSQL

The MAKE_TIMESTAMPTZ function in PostgreSQL is a convenient utility to create timestamp with time zone values from individual date and time parts. In this tutorial, we will delve into the uses, purpose, and practical examples of the MAKE_TIMESTAMPTZ function, helping you incorporate it effectively in your PostgreSQL queries.

Overview

PostgreSQL is equipped with various date and time functions to perform complex operations on temporal data easily. One of such functions is MAKE_TIMESTAMPTZ, which was introduced in PostgreSQL version 9.4, released in December 2014.

The purpose of the MAKE_TIMESTAMPTZ function is to construct a timestamp with time zone value from separate values for year, month, day, hour, minute, second, and optional time zone. This function is very useful when the date and time components are stored separately or come from different sources and need to be combined into a single timestamp value.

Syntax

The general syntax of the MAKE_TIMESTAMPTZ function is as follows:

MAKE_TIMESTAMPTZ(year, month, day, hour, minute, second, timezone)

Where:

• year is an integer representing the year.
• month is an integer representing the month (1-12).
• day is an integer representing the day of the month (1-31, depending on the month and year).
• hour, minute, and second are integers (or floats for second to accommodate fractions) representing the hour (0-23), minute (0-59), and seconds (0-59.9999) respectively.
• timezone (optional) is a string representing the time zone (e.g., ‘PST’, ‘UTC’, ‘+5:30’). If omitted, the current time zone setting of the session is used.

The function returns a timestamp with time zone.

Examples

Example 1: Basic Usage

When you have the date and time components as individual values, you can construct a precise timestamp with MAKE_TIMESTAMPTZ easily.

In this example, we declare individual date and time parts and combine them into a timestamp with the time zone:

SELECT MAKE_TIMESTAMPTZ(2023, 3, 14, 15, 9, 26, 'UTC') AS exact_timestamp;

Example 2: With a Localized Time Zone

When working with different time zones, you can specify the time zone in which you want to view your timestamp.

This example constructs a timestamp with a specific time zone other than UTC.

SELECT MAKE_TIMESTAMPTZ(2023, 3, 14, 10, 9, 26, 'America/New_York') AS exact_timestamp;

Example 3: Omitting Time Zone

If the time zone parameter is omitted, the current session’s time zone setting is taken as default.

This example creates a timestamp with the session’s default time zone:

SELECT MAKE_TIMESTAMPTZ(2023, 3, 14, 10, 9, 26) AS exact_timestamp;

Conclusion

The MAKE_TIMESTAMPTZ function is an effective tool in PostgreSQL for creating time zone-aware timestamp values from separate date-time components. With its introduction in version 9.4, it has offered developers a straightforward method to handle temporal values across differing time zones and ensures that temporal data is accurately represented in global applications. By understanding and utilizing the MAKE_TIMESTAMPTZ function, developers can streamline the process of generating timestamp with time zone values, enhancing data integrity and easing temporal data manipulations within PostgreSQL.