Is there a way to extract timezone_abbr from snowflake database?

ORACLE Query: select extract(timezone_abbr FROM sysdate at TIME zone 'America/New_York') from dual;

Is there any way to get the timezone_abbr in snowflake database, similar to what we are doing in ORACLE?

There is no direct way to extract timezone abbreviation from snowflake similar to Oracle. But we can find it by calculating the difference between the 2 time zones.

SELECT cast(your_column AS TIMESTAMP) yourtimecolumn
    ,decode(
        extract(
           hour FROM 
            convert_timezone('America/New_York', 'UTC', 
              cast(your_column AS TIMESTAMP)
            )
          ) - 
      extract(hour FROM cast(your_column AS TIMESTAMP)
      ), 
  4, 'EDT', -20, 'EDT', 5, 'EST', -19, 'EST') timezone
FROM your_table
WHERE cast(your_column AS TIMESTAMP) IS NOT NULL 
limit 100

The above query assumes your timestamp is in America/Newyork and it tries to convert it to UTC since that is the global time zone.

After the conversion, as the usual way if the difference is 4 then the timezone is in EDT and if the difference is 5 then the timezone is in EST.

EDIT: To include the time that exceeds a day due to the UTC conversion