Converting UTC to EST with daylight saving

Question

I am trying to convert UTC to EST with following formula but its not taking daylight saving time to consideration.

toString(fromUTC(currentUTC(),'EST'),'yyyyMMddHHmmss')

it shows time one hour behind EST. Is there a workaround? 

Answer 1

@Jiby Jacob

Thanks for reaching out to Microsoft Q&A.

You're right, the formula doesn't account for Daylight Saving Time (DST). Here are two workarounds to convert UTC to EST considering DST in SQL:

1. Using Zone Identification:

This method uses functions to identify the specific time zone and handle DST adjustments.

SELECT
  toString(
    atTimeZone(fromUTC(currentUTC()), 'America/New_York'),  -- Adjust 'America/New_York' for your specific location
    'yyyyMMddHHmmss'
  ) AS EST_time
FROM your_table;

Explanation:

• atTimeZone(fromUTC(currentUTC()), 'America/New_York'): This function converts the UTC time to the time zone specified by 'America/New_York' (replace with your actual location if needed). It automatically adjusts for DST based on the historical rules for that zone.
• toString(...): This converts the resulting time zone-aware object to a string in the desired format ('yyyyMMddHHmmss').

2. Conditional logic based on DST dates (Less reliable):

This method involves checking if the current date falls within the DST period and applying an offset accordingly. However, it requires maintaining the logic for DST start and end dates, which can be less reliable.

DECLARE @dst_start date = '2024-03-10';  -- Update with actual DST start date for your region
DECLARE @dst_end date = '2024-11-03';  -- Update with actual DST end date for your region
SELECT
  toString(
    CASE
      WHEN current_date >= @dst_start AND current_date < @dst_end THEN
        DATEADD(hour, -4, fromUTC(currentUTC()))  -- Subtracts 4 hours for EDT
      ELSE
        DATEADD(hour, -5, fromUTC(currentUTC()))  -- Subtracts 5 hours for EST
    END,
    'yyyyMMddHHmmss'
  ) AS EST_time
FROM your_table;

Explanation:

• This approach defines variables for DST start and end dates (replace with the actual dates for your region).
• A CASE statement checks if the current date falls within the DST period. If DST applies, it subtracts 4 hours from the UTC time to convert to EDT. Otherwise, it subtracts 5 hours for EST.
  • toString(...) converts the resulting time to the desired format.

Recommendation:

The first method using zone identification is generally preferred as it's more reliable and handles historical DST rule changes automatically. The second method requires manual updates for DST dates and might not be suitable for long-term use.

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

Answer 2

@Jiby Jacob - I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .

Ask: Converting UTC to EST with daylight saving.

Solution: The issue is resolved by @Jiby Jacob . it worked when i used America/new york timezone.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 3

Because you're using EST. EST (Eastern Standard Time) is only in effect during fall and winter. In spring and summer it switches to EDT (Eastern Daylight Time). EST is UTC-05 while EDT is UTC-04. So if you use EDT then you'll get the correct EDT. However since EST/EDT is based upon the date and varies a little by year then this is not really a workable solution. You need to determine what the current timezone is first and then convert to it.