Time conversion in Oracle PL/SQL

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???
axl747Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

catchmeifuwantCommented:
you can use this..

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
axl747Author Commented:
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
bmoshierCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Helena Markováprogrammer-analystCommented:
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
axl747Author Commented:
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
smoon63Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.