Home » SQL & PL/SQL » SQL & PL/SQL » Timestamp in ISO 8601 compliant standard (Oracle 10g)
Timestamp in ISO 8601 compliant standard [message #448755] Thu, 25 March 2010 02:11 Go to next message
mjkreddy1927
Messages: 20
Registered: February 2007
Junior Member
Hi Friends,

I Need help from you all,

My senario is like
i need to insert and retrive the date feilds in
ISO 8601 compliant standard ie..YYYY-MM-DDThh:mm:ss±hh:mm.

i have many locations to get reflected.

Please help me out with any suggestions or Docs or links. Relevent

Regards
JK
Re: Timestamp in ISO 8601 compliant standard [message #448757 is a reply to message #448755] Thu, 25 March 2010 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select to_char(systimestamp,'YYYY-MM-DD"T"hh24:mi:sstzh:tzm') isodt from dual;
ISODT
-------------------------
2010-03-25T08:03:40+01:00

Regards
Michel

[Updated on: Thu, 25 March 2010 04:29]

Report message to a moderator

Re: Timestamp in ISO 8601 compliant standard [message #448762 is a reply to message #448757] Thu, 25 March 2010 02:27 Go to previous messageGo to next message
mjkreddy1927
Messages: 20
Registered: February 2007
Junior Member
Michel Cadot wrote on Thu, 25 March 2010 02:15
SQL> select to_char(systimestamp,'YYYY-MM-DD"T"hh24:mi:sstzh:tzm') isodt from dual;
ISODT
-------------------------
2010-03-25T08:03:40+01:00

Regards
Michel



Hi Michel,

Thanks for the imm reply
This is fine while selecting but i want to insert and select and selecting the existing date feilds&rows aswell.

[Updated on: Thu, 25 March 2010 04:28] by Moderator

Report message to a moderator

Re: Timestamp in ISO 8601 compliant standard [message #448764 is a reply to message #448755] Thu, 25 March 2010 02:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Dates in Oracle are stored in an internal date structure - they only show up with a specific format when you extract them and format them with a TO_CHAR
Re: Timestamp in ISO 8601 compliant standard [message #448769 is a reply to message #448762] Thu, 25 March 2010 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To insert you use TO_TIMESTAMP with the same format.
You cannot use a DATE datatype but have to use one of the TIMSTAMP% ones as DATE does not know nor support time zone.
You can nevertheless use DATE if you assume/convert all your date/time into DB time zone and use DBTIMEZONE function to retrieve the date:
SQL> select to_char(sysdate,'YYYY-MM-DD"T"hh24:mi:ss')||dbtimezone osidt from dual;
OSIDT
-------------------------
2010-03-25T08:03:30+01:00


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand

[Updated on: Thu, 25 March 2010 04:29]

Report message to a moderator

Re: Timestamp in ISO 8601 compliant standard [message #448785 is a reply to message #448769] Thu, 25 March 2010 03:50 Go to previous messageGo to next message
mjkreddy1927
Messages: 20
Registered: February 2007
Junior Member
Michel Cadot wrote on Thu, 25 March 2010 02:35
To insert you use TO_TIMESTAMP with the same format.
You cannot use a DATE datatype but have to use one of the TIMSTAMP% ones as DATE does not know nor support time zone.
You can nevertheless use DATE if you assume/convert all your date/time into DB time zone and use DBTIMEZONE function to retrieve the date:
SQL> select to_char(sysdate,'YYYY-MM-DD"T"hh24:mi:ss')||dbtimezone osidt from dual;
OSIDT
-------------------------
2010-03-25T08:03:30+01:00


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand


Hi Still i am unable to follow the INSERT way.. can you please give me the syantax or example.

Do we need to set or alter session before doing that?

Thanks Michal

[Updated on: Thu, 25 March 2010 04:28] by Moderator

Report message to a moderator

Re: Timestamp in ISO 8601 compliant standard [message #448786 is a reply to message #448755] Thu, 25 March 2010 04:03 Go to previous messageGo to next message
John Watson
Messages: 6564
Registered: January 2010
Location: Global Village
Senior Member
Would this help?
 alter session set nls_date_format='YYYY-MM-DD"T"hh24:mi:ss';

(I've also corrected the typo of mm for mi)
Re: Timestamp in ISO 8601 compliant standard [message #448796 is a reply to message #448786] Thu, 25 March 2010 04:23 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
My senario is like
i need to insert and retrive the date feilds in
ISO 8601 compliant standard ie..YYYY-MM-DDThh:mm:ss±hh:mm.


sriram Smile
Re: Timestamp in ISO 8601 compliant standard [message #448797 is a reply to message #448785] Thu, 25 March 2010 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Hi Still i am unable to follow the INSERT way.. can you please give me the syantax or example.

What about showing us what you tried?
What is the case you want to achieve? TIMESTAMP or DATE?
Here's an example for timestamp, I let you do it for date accordingly to your case and need.

insert into mytable (mytimestamp)
values (to_timestamp('2010-03-25T08:03:30+01:00','YYYY-MM-DD"T"hh24:mi:sstzh:tzm'));

Or something like that, I didn't check it.


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand

[Updated on: Thu, 25 March 2010 04:28]

Report message to a moderator

Re: Timestamp in ISO 8601 compliant standard [message #448800 is a reply to message #448797] Thu, 25 March 2010 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or rather:
insert into t values
(from_tz(to_timestamp('2010-03-25T08:03:30','YYYY-MM-DD"T"hh24:mi:ss'),'+01:00'));

Now it depends on what is the exact stuff you get and in which exact table you want to store.


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand
Re: Timestamp in ISO 8601 compliant standard [message #448804 is a reply to message #448800] Thu, 25 March 2010 04:42 Go to previous messageGo to next message
mjkreddy1927
Messages: 20
Registered: February 2007
Junior Member
Michel Cadot wrote on Thu, 25 March 2010 04:33
Or rather:
insert into t values
(from_tz(to_timestamp('2010-03-25T08:03:30','YYYY-MM-DD"T"hh24:mi:ss'),'+01:00'));

Now it depends on what is the exact stuff you get and in which exact table you want to store.


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand


Hi,

For inserting the Such date type what should be the datatype we need to use? TIMESTAMP WITH TIME ZONE?????

i did:

Alter session set NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD"T"HH24:MI:SSTZR';

SQL> create table table_tstz(c_id NUMBER,c_tstz TIMESTAMP WITH TIME ZONE);
Re: Timestamp in ISO 8601 compliant standard [message #448805 is a reply to message #448804] Thu, 25 March 2010 04:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
For inserting the Such date type what should be the datatype we need to use? TIMESTAMP WITH TIME ZONE?????

Yes.

Regards
Michel
Re: Timestamp in ISO 8601 compliant standard [message #448806 is a reply to message #448805] Thu, 25 March 2010 04:47 Go to previous messageGo to next message
mjkreddy1927
Messages: 20
Registered: February 2007
Junior Member
Michel Cadot wrote on Thu, 25 March 2010 04:46
Quote:
For inserting the Such date type what should be the datatype we need to use? TIMESTAMP WITH TIME ZONE?????

Yes.

Regards
Michel


is there any alternate way?????
Re: Timestamp in ISO 8601 compliant standard [message #448819 is a reply to message #448806] Thu, 25 March 2010 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To store a timezone, no.
To do what you want, it depends on your environment, I told you about alternative ways in a previous post.

Note: a lone interrogation mark is sufficient to indicate this is a question, you don't need to multiply them (unless you want to make a breeding).

Regards
Michel
Re: Timestamp in ISO 8601 compliant standard [message #448821 is a reply to message #448819] Thu, 25 March 2010 05:27 Go to previous messageGo to next message
mjkreddy1927
Messages: 20
Registered: February 2007
Junior Member
Michel Cadot wrote on Thu, 25 March 2010 05:25
To store a timezone, no.
To do what you want, it depends on your environment, I told you about alternative ways in a previous post.

Note: a lone interrogation mark is sufficient to indicate this is a question, you don't need to multiply them (unless you want to make a breeding).

Regards
Michel


thanks michel, for the responce
Re: Timestamp in ISO 8601 compliant standard [message #449030 is a reply to message #448821] Fri, 26 March 2010 05:43 Go to previous messageGo to next message
mjkreddy1927
Messages: 20
Registered: February 2007
Junior Member
mjkreddy1982 wrote on Thu, 25 March 2010 05:27
Michel Cadot wrote on Thu, 25 March 2010 05:25
To store a timezone, no.
To do what you want, it depends on your environment, I told you about alternative ways in a previous post.

Note: a lone interrogation mark is sufficient to indicate this is a question, you don't need to multiply them (unless you want to make a breeding).

Regards
Michel


thanks michel, for the responce


Hi

Here i have done something please check and give me some comments..wethere is this helpfull for converting the existing timestamp to required timezone.


Convert_TimeZone(Date, From Timezone, To TimeZone)
Function:
create or replace
function CONVERT_TimeZone (dt in timestamp, DestinationTimeZone in varchar2, SourceTimeZone in varchar2)
return timestamp with time zone
as
	retval timestamp with time zone
begin	

	retval := from_tz(dt, timezone1) at time zone SourceTimeZone;
	return retval;
end;

Function will return null if there's exception.
select CONVERT_TimeZone(to_timestamp('03/18/2010 15:24','mm/dd/yyyy hh24:mi'),'-5:00','+9:30') from dual ;

Output: 3/19/2010 5:54:00 AM


UNIX Script
DELETED AT OP'S DEMAND


Regards
Jk




CM: Added [code] tags, please do so yourself next time, see the orafaq forum guide if you're not sure how.

[Updated on: Tue, 15 June 2010 11:41] by Moderator

Report message to a moderator

Re: Timestamp in ISO 8601 compliant standard [message #449032 is a reply to message #449030] Fri, 26 March 2010 05:47 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
mjkreddy1982 wrote on Fri, 26 March 2010 10:43
Function:
create or replace
function CONVERT_TimeZone (dt in timestamp, DestinationTimeZone in varchar2, SourceTimeZone in varchar2)
return timestamp with time zone
as
	retval timestamp with time zone
begin	

	retval := from_tz(dt, timezone1) at time zone SourceTimeZone;
	return retval;
end;

Function will return null if there's exception.


Not as you've got it written there it won't, you'd need to add an exception handler.
Re: Timestamp in ISO 8601 compliant standard [message #449040 is a reply to message #449032] Fri, 26 March 2010 06:29 Go to previous messageGo to next message
mjkreddy1927
Messages: 20
Registered: February 2007
Junior Member
cookiemonster wrote on Fri, 26 March 2010 05:47
mjkreddy1982 wrote on Fri, 26 March 2010 10:43
Function:
create or replace
function CONVERT_TimeZone (dt in timestamp, DestinationTimeZone in varchar2, SourceTimeZone in varchar2)
return timestamp with time zone
as
	retval timestamp with time zone
begin	

	retval := from_tz(dt, timezone1) at time zone SourceTimeZone;
	return retval;
end;

Function will return null if there's exception.


Not as you've got it written there it won't, you'd need to add an exception handler.



What type of exception i need to handel? is that mandate? please link me out..

Re: Timestamp in ISO 8601 compliant standard [message #449043 is a reply to message #448755] Fri, 26 March 2010 06:44 Go to previous message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you really want it to return null for any error then a simple:
EXCEPTION WHEN OTHERS THEN
  RETURN NULL;


Will do the job.
It's not normally good practice to hide all errors but for a function this simple it may be acceptable.

Documentation
Previous Topic: windows 7 and nls_lang
Next Topic: How to store the content of a csv file into an array in oracle
Goto Forum:
  


Current Time: Sat Dec 03 16:04:58 CST 2016

Total time taken to generate the page: 0.09930 seconds