How to use AT TIME ZONE?

The AT TIME ZONE clause is used in SQL to convert a timestamp from one time zone to another. This is particularly useful when dealing with timestamps that need to be displayed or processed in different time zones.

Here’s the general syntax:

SELECT your_timestamp_column AT TIME ZONE 'source_time_zone' AT TIME ZONE 'target_time_zone'
FROM your_table;

Example

Assuming you have a timestamp in UTC and you want to convert it to Eastern Standard Time (EST):

SELECT your_timestamp_column AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York' AS est_time
FROM your_table;

Explanation:

• your_timestamp_column: The column containing the timestamp you want to convert.
• 'UTC': The original time zone of the timestamp.
• 'America/New_York': The target time zone you want to convert to.

Important Notes:

• Make sure to use the correct time zone names as recognized by your SQL database system.
• The AT TIME ZONE functionality may vary slightly between different SQL databases (like PostgreSQL, SQL Server, etc.), so always refer to the specific documentation for your database system for any nuances.