Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL Time Zone Conversions

PostgreSQL Date Functions are used to manipulate and retrieve date and time values, including converting between time zones. These functions are essential for applications that require consistent date-time handling across different geographic regions.

Usage

Date Functions in PostgreSQL are used to perform operations on date and time values, such as converting timestamps from one time zone to another for accurate time representations. The syntax for converting time zones typically involves the AT TIME ZONE construct.

timestamp AT TIME ZONE timezone;

In this syntax, a timestamp is converted into the specified timezone, allowing for the adjustment of date-time data to reflect different regional settings.

Examples

1. Basic Time Zone Conversion

SELECT '2023-10-01 14:00:00'::timestamp AT TIME ZONE 'UTC';

This example converts the given timestamp from UTC to the local time zone of the server. Note that the server's current time zone setting affects the result, so it's necessary to know this setting to understand the output.

2. Converting Time with a Specific Time Zone

SELECT '2023-10-01 14:00:00'::timestamp AT TIME ZONE 'America/New_York';

Here, the timestamp is converted to the Eastern Time Zone (America/New_York). The resulting timestamp will adjust to reflect this specific time zone.

3. Converting and Displaying Time Zone Adjustments

SELECT '2023-10-01 14:00:00'::timestamp AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Tokyo';

This example first converts a UTC timestamp to the server's local time and then to the Asia/Tokyo time zone. This showcases how a timestamp can be shifted through multiple time zones.

4. Handling Daylight Saving Time Changes

SELECT '2023-03-12 01:30:00'::timestamptz AT TIME ZONE 'America/New_York';

This example demonstrates how PostgreSQL automatically adjusts for daylight saving time changes, which is crucial when accurate time representation is needed during such transitions.

Tips and Best Practices

  • Use timezone-aware data types. Prefer using timestamptz over timestamp to store timezone information. The timestamptz type automatically handles time zone conversions and daylight saving time changes, while timestamp does not retain time zone information.
  • Be explicit with time zones. Always specify the time zone when converting times to avoid ambiguity.
  • Consider daylight saving time. Be mindful of daylight saving time changes when working with time zones to ensure accurate time calculations.
  • Leverage PostgreSQL's time zone database. Use PostgreSQL's comprehensive time zone support for accurate conversions.
  • Test with known values. Validate conversions using known time differences to ensure accuracy.