Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

jdavidb (1361)

jdavidb
  (email not shown publicly)
http://voiceofjohn.blogspot.com/

J. David Blackstone has a Bachelor of Science in Computer Science and Engineering and nine years of experience at a wireless telecommunications company, where he learned Perl and never looked back. J. David has an advantage in that he works really hard, he has a passion for writing good software, and he knows many of the world's best Perl programmers.

Journal of jdavidb (1361)

Tuesday August 07, 2007
10:15 AM

Oracle gem of the day: converting epoch seconds

[ #34029 ]

To my knowledge, there is no Oracle TO_DATE format specifier for converting from epoch seconds to a DATE or TIMESTAMP. There should be.

To my knowledge, there is no Oracle built-in function to convert epoch seconds to a DATE or TIMESTAMP. There should be.

However, converting epoch seconds is not very hard. By definition, epoch seconds are seconds since January 1, 1970. So add the seconds to 1970.

Since my problem doesn't specify anything about location, time zone, or daylight saving time, my solution doesn't do anything to account for those. If you use this, you might want to think about those. I think everybody ought to just use UTC anyway. :)

Also, I don't specify the hour, minute, and second of the epoch I'm adding to. I may have some logic error here. It's good enough for my purposes, but if you need to use this, think about it and make sure it does the right thing before you depend on it. :)

CREATE OR REPLACE FUNCTION from_epoch(v_epochseconds NUMBER)
RETURN TIMESTAMP
AS
BEGIN
RETURN TO_TIMESTAMP('1970-01-01', 'YYYY-MM-DD') + v_epochseconds / 86400;
END;
/

86400 of course is the number of seconds in a day, so the division converts your input value to days for use with Oracle's arithmetic.

By the way, this also doesn't account for leap seconds.

Hmm, probably if I'm going to use TIMESTAMP instead of date I should convert the input value to a real, first-class, INTERVAL value and add that instead of relying on an implicit conversion from number to days.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • I know I've searched for Oracle epoch seconds before and come up with something similar to what you are using. I did it again today when I saw this post and ran across something bizarre (yet interesting): The story behind the Oracle DATE datatype [dizwell.com].
  • I dealt with this issue a long time ago (Oracle 7 or 8, IIRC), and I haven't had to deal with Oracle in quite a while, so forgive me if my examples seem a bit hazy, or if Oracle's handling of these issues has improved.

    In my experience, Oracle doesn't understand time zones, daylight savings adjustments, leap seconds, or anything else that makes dealing with date/times easy. So, the shortcut I eventually came up with was to use seconds from "now" as the epoch. That way you can deal with the various other is
    • Today Oracle understands time zones and daylight savings time if you use the TIMESTAMP WITH TIMEZONE data type, which is sort of a superset of the DATE datatype. There is also a plain TIMESTAMP datatype that has some features beyond DATE but does not do timezones.

      The only time I really had to mess with this was when I had Log4perl logging to a database table, and I demanded that the timestamp field be a TIMESTAMP WITH TIMEZONE in UTC time, with UTC as the TIMEZONE value. Somewhere back in my journal I h

      --
      J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers