RE: Set Time Zone

From: Jenner Mike <M.Jenner_at_southampton.gov.uk>
Date: Thu, 13 Dec 2001 07:18:00 -0800
Message-ID: <F001.003DBD53.20011213065524@fatcity.com>

Mike,

        Here's a thing you may well be able to modify slightly to your requirements. It will be useful only for running the application through Unix. The following is taken from the .profile of an account I set up here in 1999 to test Y2K.
Hope it helps
- Mike J.

---

echo
echo ' Enter new date required in format ddMONyyyy' echo  

read newdate  

hours=`sqlplus -s user/password <<EOF

       set pagesize 0 linesize 499 feedback off echo off verify off;
       define date='$newdate';
       select (trunc(sysdate) - to_date('$newdate','DDMONYYYY')) * 24 from

dual;
EOF`   if test `echo $hours | grep -c 'ORA-'` -ne 0

   then

       echo $hours
       exit 1
   else
       hours=`echo $hours | sed 's/^ *//g'`
#       echo "INFO: hours set to $hours"
       TZ=GMT${hours};export TZ
       date2=`sqlplus -s user/password   <<EOF
              set pagesize 0 linesize 499 feedback off echo off scan off;
              select to_char(sysdate,'DD MON YYYY HH24:MI.SS') from dual;

EOF`
fi  

echo "INFO: UNIX Environment Variable TZ set to $TZ"
echo "INFO: hours set to $hours"
echo "INFO: Oracle Sysdate set to $date2"

# env
echo
echo "OK to continue and use this date for this application session?
[y/n]\c"

read dummy
if [ "$dummy" != "" ]; then

        if [ "$dummy" = "y" -o "$dummy" = "Y" ]; then
                echo
        else
                echo
                echo Bye,then
                sleep 3
                exit
        fi

fi
echo RUNNING with $date2
sleep 5
# start application here...

---

Mike Jenner.
Database Administrator,
Southampton City Council,
02380 833985

-----Original Message-----
[mailto:hemantchitale_at_charteredsemi.com]
Sent: 12 December 2001 03:15
To: Multiple recipients of list ORACLE-L

Can't , not in 8i.
9i has introduced a TIMEZONE datatype where time can be reported offset by the UTC+/- offset.

You'd have to build the logic in your application to increment/decrement the sysdate, depending
on the user/client location (use a lookup table for location and timezone-offset) when fetching
the sysdate.
SYSDATE would always return the date on the server where the database instance is running.

Hemant

Mike T <tran.manh_at_videotron.ca> 12/12/2001 10:50 AM Sent by: root_at_fatcity.com

Please respond to ORACLE-L  

             To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

             cc: (bcc: CHITALE Hemant Krishnarao/Prin DBA/CSM/ST Group)

             Subject: Set Time Zone      

Hi Gurus,

How can I set up a time zone for each region that connect on SAME Database 8i

Case: Database in NewYork. Clients in San Franciso, Paris .. When they use "select sysdate from dual ;" ( on Client or server side )  They should see their local time .??

Thanks in advance
Tran

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mike T
  INET: tran.manh_at_videotron.ca

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).






[This e-mail is confidential and may also be privileged. If you are not the

intended recipient, please delete it and notify us immediately; you should
not copy or use it for any purpose, nor disclose its contents to any other
person. Thank you.]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: hemantchitale_at_charteredsemi.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jenner Mike
  INET: M.Jenner_at_southampton.gov.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Dec 13 2001 - 09:18:00 CST