Sling Academy
Home/PostgreSQL/Using MAKE_TIMESTAMPTZ Function in PostgreSQL

Using MAKE_TIMESTAMPTZ Function in PostgreSQL

Last updated: January 05, 2024

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.

Next Article: Explore Date Value Extractors in PostgreSQL

Previous Article: Using MAKE_INTERVAL Function in PostgreSQL: A Comprehensive Guide

Series: PostgreSQL Tutorials: From Basic to Advanced

PostgreSQL

You May Also Like

  • PostgreSQL with TimescaleDB: Querying Time-Series Data with SQL
  • PostgreSQL Full-Text Search with Boolean Operators
  • Filtering Stop Words in PostgreSQL Full-Text Search
  • PostgreSQL command-line cheat sheet
  • How to Perform Efficient Rolling Aggregations with TimescaleDB
  • PostgreSQL with TimescaleDB: Migrating from Traditional Relational Models
  • Best Practices for Maintaining PostgreSQL and TimescaleDB Databases
  • PostgreSQL with TimescaleDB: Building a High-Performance Analytics Engine
  • Integrating PostgreSQL and TimescaleDB with Machine Learning Models
  • PostgreSQL with TimescaleDB: Implementing Temporal Data Analysis
  • Combining PostgreSQL, TimescaleDB, and Airflow for Data Workflows
  • PostgreSQL with TimescaleDB: Visualizing Real-Time Data with Superset
  • Using PostgreSQL with TimescaleDB for Energy Consumption Analysis
  • PostgreSQL with TimescaleDB: How to Query Massive Datasets Efficiently
  • Best Practices for Writing Time-Series Queries in PostgreSQL with TimescaleDB
  • PostgreSQL with TimescaleDB: Implementing Batch Data Processing
  • Using PostgreSQL with TimescaleDB for Network Traffic Analysis
  • PostgreSQL with TimescaleDB: Troubleshooting Common Performance Issues
  • Building an IoT Data Pipeline with PostgreSQL and TimescaleDB