Home » SQL & PL/SQL » SQL & PL/SQL » Timestamp value with US Time zone. (Oracle 10g)
Timestamp value with US Time zone. [message #326618] Thu, 12 June 2008 02:13 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
our client has a database server in US and the problem is -

When a Stockist places a order he gets the time stamp of US time zone where as the stockist is in India. All the procedures which get fired are at server side and thus get the US time zone.
How can counter this issue for printing the relevant time zone correctly for a specific country.

Thanks in advance,
Mona
Re: Timestamp value with US Time zone. [message #326626 is a reply to message #326618] Thu, 12 June 2008 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use TIMESTAMP WITH [LOCAL] TIME ZONE datatype.

Regards
Michel
Re: Timestamp value with US Time zone. [message #326640 is a reply to message #326626] Thu, 12 June 2008 02:35 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks for your answer Michel....

But the database server is in US and the Stockist places a order online, so how to the respective timezone would work..... as the server is in US and the stockist is in India placing order online.
Won't all the timestamp value will come only in US timezone if I use LOCAL timezone.
Since the procedures which get fired are at server side and thus get the US time zone so how to get the timezone where stockist is making entry from.


Thanks,
Mona

[Updated on: Thu, 12 June 2008 02:36]

Report message to a moderator

Re: Timestamp value with US Time zone. [message #326645 is a reply to message #326640] Thu, 12 June 2008 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on your procedure.
Is it using a timestamp function to get the time or is the timestamp passed by the client through a procedure parameter?
What is the datatype of the columns in which you store the value?
Did you want trhe value for queries? From client or only on server?
Do you need to have the original time zone in your data? Or just the value for the client time zone when you query it?
...
Many questions, answer depends on all these.

Regards
Michel
Re: Timestamp value with US Time zone. [message #326701 is a reply to message #326645] Thu, 12 June 2008 05:47 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Michel,
Please find my answers to your questions that I have just received from end programmer.

Michel Cadot wrote on Thu, 12 June 2008 02:43
It depends on your procedure.
Is it using a timestamp function to get the time or is the timestamp passed by the client through a procedure parameter?


==> No. We are using only SYSDATE in procedure. As procedure runs on server while executing the procedure SYSDATE takes the server current date and stores it in to respective tables.

Quote:

What is the datatype of the columns in which you store the value?


===> Date
Quote:
Did you want the value for queries? From client or only on server?


==> There are package structure. All procedures are stored in packages. All Package/procedures are on the server. In the procedure,SYSDATE fucntion is used.
Quote:

Do you need to have the original time zone in your data? Or just the value for the client time zone when you query it?


==> There is need for Indian Timezone's date only. Just the value and timezone is not mandatory


This is sample procedure from my programmer for your reference.
In this date_Modified function uses SYSDATE function
update TBL_SCHEME

set
SCHEME_NAME=trim(p_SCHEME_NAME),
DESCRIPTION = trim(p_Description),
SCHEME_QUANTITY = trim(p_SCHEME_QUANTITY),
QUANTITY_FREE = trim(p_QUANTITY_FREE) ,
PERCENTAGE_FREE = trim(p_PERCENTAGE_FREE) ,
FROM_DATE = trim(p_FROM_DATE),
TO_DATE = trim(p_TO_DATE) ,
ACTIVE = trim(p_ACTIVE) ,
DATE_MODIFIED = TO_DATE(TO_CHAR(SYSDATE,'MM-DD-YYYY'),'MM-DD-YYYY'),
REGION_ID=upper(trim(p_REGION_ID)),
UPLOADED_FILE_PATH=trim(p_File_Upload)
Where SCHEME_ID = p_Scheme_ID ;

The need is to get the time in Indian Time only that is IST while the server is kept in US and currently taking US time even when the portal is using user input from india only. So only a conversion to Indian time is needed.
Please suggest a way where the server should take time as IST and not US time.



Thanks for looking into this,
Mona
Regards,
Mona
Re: Timestamp value with US Time zone. [message #326712 is a reply to message #326701] Thu, 12 June 2008 06:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case, you have to use SESSIONTIMEZONE, DBTIMEZONE and NEW_TIME function to correct SYSDATE with time zone data.

Have a look at this topic: http://www.orafaq.com/forum/m/258044/102589/?srch=new_time#msg_258044

Regards
Michel
Re: Timestamp value with US Time zone. [message #326714 is a reply to message #326701] Thu, 12 June 2008 06:41 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
Is there a way to change the session timezone before any procedure execute. How can we change the time given by SYSDATE function on a Oracle server to give a IST(Indian standard time) time when we are working on a US server.

How to change the time given by SYSDATE in Indian time when the sysdate is giving a SYSDATE of US since the Oracle server is placed in US.

A way I found is to substact some minutes to current time being displayed at US server and it gives the IST time.
But don't know its implications when the US EST time will change to EDT and vice versa.


SELECT TO_CHAR(SYSDATE - INTERVAL '570' MINUTE, 'HH:MI:SS AM')
FROM dual;


Please suggest me a good answer that will help in both cases with daylight saving and without that too.

IS there a way to alter the session someway or to do something so that I do not have to make changes to each and every object created where SYSDATE is being used.
Please look into this.


Thanks,
Mona

[Updated on: Thu, 12 June 2008 06:59]

Report message to a moderator

Re: Timestamp value with US Time zone. [message #326721 is a reply to message #326714] Thu, 12 June 2008 07:21 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there a way to change the session timezone before any procedure execute.

Yes "ALTER SESSION"

Quote:
How can we change the time given by SYSDATE function on a Oracle server to give a IST(Indian standard time) time when we are working on a US server.

You can't SYSDATE is ALWAYS server date/time
This is why I point you to this conversion function.

Quote:
Please suggest me a good answer that will help in both cases with daylight saving

NEW_TIME as I said.

Quote:
without that too.

Use time zones as I said or with the topic I pointed to.

Quote:
IS there a way to alter the session someway or to do something so that I do not have to make changes to each and every object created where SYSDATE is being used.

No.

Quote:
Please look into this.

Please do it for all the elements I gave you.

The other (and more better) way is to change your datatype to one that supports time zone.

Regards
Michel

[Updated on: Thu, 12 June 2008 07:22]

Report message to a moderator

Previous Topic: How unique index duplicated ?
Next Topic: problem in date?
Goto Forum:
  


Current Time: Sun Dec 11 08:09:03 CST 2016

Total time taken to generate the page: 0.12238 seconds