Solved

Time conversion in Oracle PL/SQL

Posted on 2003-11-02
7
3,224 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

Technology Partners: 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!

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

749 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