Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Time conversion in Oracle PL/SQL

Posted on 2003-11-02
7
Medium Priority
?
3,229 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 252 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 248 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

610 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