Changing the time zone for a Synapse dedicated SQL pool

pmscorca 792

Hi,

inside a Synapse dedicated SQL pool I need to use the GETDATE() T-SQL function for some queries, but it returns a time minor of two hours respect to the Italian time.

This is the datetime returned by the GETDATE() using SSMS:

User's image

The real time is 14:35.

Also a my colleague has the same issue for the same Azure subscription and Synapse Analytics.

The SQL pool is into a West Europe resource group.

So, is it possible to change the Synapse time zone or to adjust the datetime returned by the GETDATE function?

Thanks

phemanth 5,840 Reputation points Microsoft Vendor

2024-04-26T05:54:35.4433333+00:00

@pmscorca Following up to see if the below answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

1 answer

phemanth 5,840 Reputation points Microsoft Vendor

2024-04-24T14:02:22.28+00:00
@pmscorca

Thanks for reaching out to Microsoft Q&A

The time zone of a dedicated SQL pool in Azure Synapse Analytics cannot be changed. The dedicated SQL pool uses the time zone of the Azure region it's deployed in, which in your case is likely UTC. This means that the GETDATE() function will always return the time in UTC.

Here are two ways to adjust the datetime returned by GETDATE() to Italian time:

Use the AT TIME ZONE clause: You can add the AT TIME ZONE clause to the GETDATE() function to specify the desired time zone. For example, the following query would return the current time in Italian time:

SELECT GETDATE() AT TIME ZONE 'Central European Time' AS ItalianTime;

Convert the UTC time to Italian time after it is returned by GETDATE(): You can use the DATEADD function to convert the UTC time to Italian time. For example, the following query would add two hours to the current UTC time to get the current Italian time:

SELECT DATEADD(HOUR, 2, GETDATE()) AS ItalianTime;

The query in the image you sent uses both methods to achieve the same result. The first three columns all return the same value, which is the current time in UTC. The fourth column uses the AT TIME ZONE clause to convert the UTC time to Central European Time.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Please sign in to rate this answer.
pmscorca 792 Reputation points

2024-04-24T16:07:48.01+00:00

Hi,

these are the installed European time zones:
I've tried to apply the AT TIME ZONE clause for each installed time zone, but the time isn't adjusted, and so the issue remains:

Moreover, do the European time zones consider the daylight savings time?
The location for the Synapse dedicated SQL pool is West Europe, so Synapse is deployed in the West Europe region and the related time zone is an UTC one, isn't it?

phemanth 5,840 Reputation points Microsoft Vendor

2024-04-25T08:04:53.2566667+00:00

@pmscorca

AT TIME ZONE Clause:

You mentioned that you tried using the AT TIME ZONE clause for each installed time zone, but the time wasn't adjusted. Here's a possible explanation:

The time zones listed in the query result ([sys] [time_zone_info]) are informational only. They represent the time zones supported by the SQL Server instance, but they don't define the time zone of the SQL pool itself.

Since Synapse dedicated SQL pools always operate in UTC, using the AT TIME ZONE clause with time zones from that list won't change the underlying UTC time stored in the database.

European Time Zones and Daylight Saving Time (DST)

Yes, most European time zones do observe Daylight Saving Time (DST). This means they shift forward one hour during the summer months to make better use of daylight.

However, this again doesn't affect the way Synapse dedicated SQL pools handle time. As mentioned earlier, these pools always store and operate in UTC, regardless of the observance of DST in a particular region.

West Europe Region and UTC Time Zone

You're correct. Synapse dedicated SQL pools deployed in the West Europe region are configured to use UTC time. This is because UTC is a global time standard that eliminates confusion caused by regional time zone variations.

Summary

To retrieve the time in a specific time zone like Italian Time (CET), you'll need to adjust the UTC time returned by GETDATE() using either the AT TIME ZONE clause with the appropriate time zone name (e.g., 'Central European Time') or the DATEADD function with an offset of +1 hour.

The time zones listed in the query result you sent are for informational purposes only and don't directly change the time zone behavior of your Synapse dedicated SQL pool.

pmscorca 792 Reputation points

2024-04-26T06:38:54.2066667+00:00

Hi,

in this manner I can adjust the actual time: In this period the current time considers the daylight saving time and I need to adjust the GETDATE() value using an unique statement that it has to be valid always.

Above, I've added two hours to adjust the GETDATE() value, but the DST isn't used for the entire year.

Now, how could I solve the issue definitively?

pmscorca 792 Reputation points

2024-04-26T20:36:50.5666667+00:00

Hi,

I think that the GETDATE() function takes into account the DST when it occurs during the year (in Italy the DST has started at March 31, 2024 and it will end at October 27, 2024):

phemanth 5,840 Reputation points Microsoft Vendor

2024-04-29T05:14:52.0933333+00:00

@pmscorca

Unfortunately, there's no single built-in function in Synapse SQL pools that automatically adjusts for Daylight Saving Time (DST). While GETDATE() reflects the actual server time, which might account for DST depending on the region settings, it's not guaranteed within Synapse dedicated SQL pools.

Here are two approaches to handle DST definitively in your queries:

Manual Adjustment based on DST Dates: This method involves adding or subtracting the appropriate time difference based on predefined DST periods. You can achieve this using conditional logic or a separate table storing DST information. Here's an example with a hypothetical table DST_Rules containing start and end dates for DST: SQL
DECLARE @offset INT; SELECT @offset = CASE WHEN GETDATE() >= StartDate AND GETDATE() < EndDate THEN 2 -- Assuming +2 hours for DST ELSE 0 END FROM DST_Rules WHERE Region = 'Italy'; SELECT GETDATE() + INTERVAL @offset HOUR AS ItalianTime;
This approach requires maintaining the DST_Rules table and updating it for any region-specific DST changes.

External Time Zone Service Integration: You can leverage external time zone service APIs to retrieve the current DST offset for a specific region. This eliminates the need for manual maintenance of DST rules. The specific implementation depends on the chosen service, but it generally involves making an HTTP request to the API and incorporating the retrieved offset into your query.

pmscorca 792 Reputation points

2024-04-29T07:59:02.63+00:00

Hi, using a DST_rules table for the Italian region could mean to insert new start and end dates for each year.

Respect to the dedicated SQL pool that I'm using the solution to adjust the GETDATE() value functions: once the SQL pool is deployed, could the related regional settings change?

Using the external time zone service APIs to retrieve the current DST offset for a specific region could mean to call the API whenever it occurs to use the GETDATE() function, isn't it?

phemanth 5,840 Reputation points Microsoft Vendor

2024-04-29T08:24:25.92+00:00

@pmscorca You're absolutely right about both approaches having limitations:

1. DST_Rules table:

Maintaining a table can be cumbersome, especially if you need to handle multiple regions or frequent DST rule changes.

You'll need to update the table entries manually whenever there's a DST rule change for a specific region.

2. External Time Zone Service API calls:

Calling an external API for every GETDATE() function might introduce performance overhead depending on the frequency of your queries and API latency.

Reliability can be a concern if the external service has downtime or limitations.

Alternative for DST_Rules table:

You could explore pre-populated, up-to-date DST rule datasets available online. These datasets might be downloadable or accessible through APIs, potentially reducing manual maintenance.

Reducing API calls for external service:

Cache the retrieved DST offset for a specific region for a certain period (e.g., one day) to avoid excessive API calls. Update the cached value periodically to ensure accuracy.

Choosing the best approach depends on your specific needs:

If you only need to handle a few regions with infrequent DST changes, the DST_Rules table with potential updates might be sufficient.

If performance and reliability are critical, or you need to handle many regions with frequent DST updates, using a cached external service API might be a better option.

It's also worth considering a hybrid approach, combining a pre-populated DST_Rules table for common regions with an external service API for less frequent cases
Sign in to comment