Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to find out the current time zone in Oracle?
FYI, this functionality is also available in 9i, where the database can be
created in a specific timezone and sessions can be in their own client
timezone using an ALTER SESSION command. To see the database timezone, you
simply SELECT dbtimezone FROM DUAL (like the old SELECT sysdate FROM DUAL)
and to see the client timezone, you SELECT sessiontimezone FROM DUAL.
I don't think this would be enough to make you upgrade to 9i, but it's worth knowing anyway! ;)
-- HTH. Additions and corrections welcome. Pete Author of "Oracle8i: Architecture and Administration Exam Cram" Now got a life back again that the book is released! "Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook "Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA "Nuno Souto" <nsouto_at_optushome.com.au.nospam> wrote in message news:3b8f80b5.3344404_at_news...Received on Fri Aug 31 2001 - 11:19:28 CDT
> On Fri, 31 Aug 2001 11:58:57 GMT, Benoit Losier
> <blosier_at_shareline.com> wrote:
>
> >I need to convert my date to GMT time using the NEW_TIME function but
> >can't seem to find a way to get the current database time zone.
> >
>
> You can't, at least not directly. To calculate the GMT time once you
> have the "displacement" of your current timezone (read on), you can
> use date arithmetic to find it.
>
> Say for example you're "+10" time zone:
>
> select to_char((sysdate-(10/24)),'yyyymmddhh24miss') from dual;
> gives you the GMT.
>
> If it was -10, then you'd sum instead of subtracting.
> (you *know* how to do that in a single operation, don't you?)
>
> >Our development site has a different time zone then our live sites so I
> >can't hard code the time zone in my script.
> >
>
> Create a table where you store the time zone of the instance,
> manually. Then just read from there when needed. Populate the table
> as part of moving to or installing the database in a new site. Works
> for me.
>
> Cheers
> Nuno Souto
> nsouto_at_optushome.com.au.nospam