How to determine if a particular TIMESTAMP WITH TIME ZONE is in the summer or not

Question

I have an application that stores time in GMT, but is connected to airports that have Olsen tz names such as "America / New York."

I need to determine if a given date is within the local summer period.

The closest I could find is TZ_OFFSET ('Tzname'), but there is no easy way to get the next start / end of DST relative to some date.

It seems like a step in the right direction ...

select cast (mydate as timestamp) in the GMT time zone as mydateZ, from_tz (cast (mydate as timestamp), 'GMT') in the myzoneName time zone as mydateLocal

Answer 1

Thinking about a similar problem, I wrote the following question to your question:

select decode(to_char(from_tz(cast (sysdate as timestamp),'CET'),'TZR'),
to_char(from_tz(cast (sysdate as timestamp),'CET'),'TZD'),'N','Y') is_daylight 
from dual

Short explanation: I convert the current date (sysdate) into my time zone ("CET") into a timestamp and compare the common name ("TZR") and the daylight saving time name ("TZD") of the time zone. If they match, then sysdate is not in daylight, and if they do not match (for example, sysdate-100 is "CEST" for the CET time zone), then the date is in daylight saving time. Date and time zone can be a query parameter.