Home » SQL & PL/SQL » SQL & PL/SQL » Record sysdate to TIMESTAMP field in to UTC format (Windows 2003)
Record sysdate to TIMESTAMP field in to UTC format [message #336215] Fri, 25 July 2008 04:19 Go to next message
fifisave
Messages: 5
Registered: November 2000
Junior Member
Hello all,
i run a procedure inserting data into a table with a TIMESTAMP date type field from France.
I need to record the sysdate date into the TIMESTAMP date type field into UTC date format.
How to handle that please?
regards
Re: Record sysdate to TIMESTAMP field in to UTC format [message #336218 is a reply to message #336215] Fri, 25 July 2008 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First if you want a timestamp why not use systimestamp instead of sysdate?

Then, have a look at convert_timezone function from Barbara Boehmer.

Regards
Michel

Re: Record sysdate to TIMESTAMP field in to UTC format [message #336222 is a reply to message #336215] Fri, 25 July 2008 04:41 Go to previous messageGo to next message
fifisave
Messages: 5
Registered: November 2000
Junior Member
Does the systimestamp record a local date format to the UTC format automatically?
I don't define any session parameter into my application for now.
Do i need to define one before?
example:
Alter session set blablabla;
Insert into table (datefield) values (systimestamp);
commit;

This will record my datefield into the UTC date format?
regards
Re: Record sysdate to TIMESTAMP field in to UTC format [message #336232 is a reply to message #336222] Fri, 25 July 2008 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, it was not my purpose to say that, just a thought about datatype appropriateness: date <-> sysdate, timestamp <-> systimestamp.

For the conversion, see Barbara's function.
Or you could use, assuming your db time zone is the one of your server:
SQL> select systimestamp dbtime,
  2         sys_extract_utc(systimestamp) utc
  3  from dual
  4  /
DBTIME                         UTC
------------------------------ ------------------------------
25/07/2008 13:09:52.478 +02:00 25/07/2008 11:09:52.478

1 row selected.

Regards
Michel
Re: Record sysdate to TIMESTAMP field in to UTC format [message #336250 is a reply to message #336215] Fri, 25 July 2008 06:48 Go to previous messageGo to next message
fifisave
Messages: 5
Registered: November 2000
Junior Member
Ok i will try to use the timestamp format instead of the date which is supposed to be better as you mention and also the extract_utc function to convert the value.
Stayed in touch.
Thank's a lot
Regards
Philippe
Re: Record sysdate to TIMESTAMP field in to UTC format [message #336251 is a reply to message #336215] Fri, 25 July 2008 07:18 Go to previous message
fifisave
Messages: 5
Registered: November 2000
Junior Member
It works nice with the extract_utc and systimestamp date type format.
I really appreciate your precious help.
regards
Philippe
Previous Topic: Feasibility of dynamic conditional rule
Next Topic: Regular Expression ambiguous
Goto Forum:
  


Current Time: Sat Dec 10 14:43:05 CST 2016

Total time taken to generate the page: 0.07524 seconds