Solved

Time conversion in Oracle PL/SQL

Posted on 2003-11-02
7
3,226 Views
Last Modified: 2013-12-11
I am writing a package to process data into a schema and a requirement to store a timestamp as "no of seconds since 01/01/1970 UCT time" has surfaced.
I am not aware of any packaged procedure etc in Oracle which does this conversion.  Will I have to do this calc manually or is there a nicer way???
0
Comment
Question by:axl747
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 12

Accepted Solution

by:
catchmeifuwant earned 63 total points
ID: 9669240
you can use this..

select empno,ename,sal,hiredate,
(hiredate - to_date('01-Jan-1970','DD-Mon-YYYY') )*86400 as Secs
from emp
0
 

Author Comment

by:axl747
ID: 9783551
So there is no other way but to do it manually then?  Also, as I indicated, I also need to convert the date to UCT time (thats GMT without daylight savings I believe), and I still haven't found a way to do this in SQL.
0
 
LVL 4

Assisted Solution

by:bmoshier
bmoshier earned 62 total points
ID: 9849780
You should do the TOD - to_date(01/01/1970, etc.)*86400 in the insert statement.  This way you don't have to keep computing the TOD information with each display.  As for the timezone difference, there are easy ("quick and dirty") and complex methods that take a lot of situations into consideration. A couple of questions:  Is the TOD coming from the database (e.g. systime) or from another location?  Is the location of where the time coming from stable (e.g. the timezone doesn't move)?  If so, all you need to do is hard code the time zone difference between UTC and the local time zone.  As for daylight saving time, is the place (or places) you are getting the original time affected by daylight saving times?

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA
http://www.bmoshier.net/bertram
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Expert Comment

by:Helena Marková
ID: 10279266
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Split between catchmeifuwant and bmoshier.

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Henka
EE Cleanup Volunteer
0
 

Author Comment

by:axl747
ID: 10284565
Thanks for the suggestions - I guess the answer to my initial question is 'no nicer way' at least not in 8i!!

We already had a solution using unix scripts so we have left it at that.
0
 

Expert Comment

by:smoon63
ID: 10445863
Actually the assisted answer should replace the accepted answer as it comes closer to solving the original problem. The accepted answer does nothing to account for the UTC offset problem. The assisted answer doesn't actually solve it, but at least it offers several avenues of exploration on the issue.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question