ORACLE SQL adjust date for timezone without casting to timestamp or char-CodePudding

For ORACLE SQL, Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production,

Is there a way to convert a date (including date/time) from GMT to local time (allowing for Daylight Savings adjustment) directly just as a date (including date/time)?

I have seen several solutions that involve timestamps or that use timestamps and character conversions with the relevant timezone offset.

However, I'd like to keep everything as a date.

Example using timestamp conversion:

SELECT OUR_DATE, from_tz(cast( OUR_DATE as timestamp), 'GMT') at time zone 'US/Eastern'
from OUR_DB.OUR_TBL;

CodePudding user response：

It seems that you need something like this (which would be a lot easier if Oracle also had "date with time stamp" data types):

...
cast(from_tz(cast(your_date as timestamp), 'UTC') at time zone 'US/Eastern'
  as date) as your_column_alias
...

Cast the date as timestamp, so you can give it a time zone (UTC is the new GMT - that's a separate issue), convert to your required time zone, and then convert back to date - no time zone, no fractional seconds.