How to find if a particular TIMESTAMP WITH TIME ZONE is within daylight saving or not

I have an app that stores times in GMT, but associated with airports which have Olsen tz names like 'America/New York'.

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

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

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

select cast (mydate as timestamp) at time zone 'GMT' as mydateZ, from_tz(cast (mydate as timestamp),'GMT') at time zone myzo开发者_StackOverflow社区neName as mydateLocal

---

Thinking about a similar problem I wrote the following query 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

A short explanation: I convert the current date (sysdate) in my timezone ('CET') to a timestamp and compare the the normal name ('TZR') and daylight savings time name ('TZD') of the timezone. If they match then sysdate is not within daylight saving and if they do not match (for example sysdate-100 is 'CEST' for 'CET' timezone) then the date is within daylight saving. Date and timezone can be a parameter of the query.