Oracle enforce SYSDATE to PT

Abraham García Lomeli 0
Viewed 33

EDIT: I do not want to change SYSDATE, now I understand this is not possible unless the Host of the DB changes, I would still like my data to be in Pacific Time so I guess the way to go is using TIMESTAMP WITH LOCAL TIMEZONE

I have a database for which I want to enforce SYSDATE to be Pacific Time. I cannot modify the date of the host for my DB due to business rules and currently SYSDATE is CDT.

I am aware that I can change the timezone for my session, however I cannot control that other users will alter their sessions as well

Is there a way I can enforce users to use PT without modifying the date of the host?

I was thinking maybe a sort of PL/SQL trigger that alters the session every time a new connection to DB is stablished.

Thanks1

plsql oracle database sql
2 Answers

I don't think you're asking the right question. You can't do what you're trying to do. SYSDATE returns the date and time of the operating system the database is running on. It returns a DATE value, and DATEs are not time zone aware. I guess you could change the time zone of the operating system, but you'd have to talk to your system administrator about that.

You might want to work more with CURRENT_TIMESTAMP which returns a TIMESTAMP WITH TIME ZONE value and the display of that can be modified with the session time zone.

I'd say the bigger question is why are you trying to change the time zone for SYSDATE?

eaolson0

You can use an "after logon" trigger to set time zone.

Include the following sentence inside trigger:

execute immediate 'ALTER SESSION SET TIME_ZONE=''-05:00''';

Change "-05:00" with time offset of "Pacific Time"

alvalongo0
Related