Solved

Time conversion in Oracle PL/SQL

Posted on 2003-11-02
7
3,215 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now