This discussion is archived
11 Replies Latest reply: Jul 1, 2008 1:01 PM by 643317 RSS

Built-in epoch time function

user458496 Newbie
Currently Being Moderated
Does anyone know when Oracle plans on adding a built-in function to handle epoch time conversion (both converting to and from)? We have quite a few entries that are given in epoch, and we have had to create JAVA functions. Other applictions have this as a built-in function already.
  • 1. Re: Built-in epoch time function
    Ganesh Srivatsav Guru
    Currently Being Moderated
    cant you create your own?
    SQL> create or replace function date_to_epoch(in_date in date) return number is
      2  epoch number(38);
      3  begin
      4  select (sysdate- to_date('1-1-1970 00:00:00','MM-DD-YYYY HH24:Mi:SS'))*24*3600 into epoch from 
    dual;
      5  return epoch;
      6  exception when others then
      7  null;
      8  end;
      9  /
    
    Function created.
    
    SQL> select date_to_epoch(sysdate) from dual;
    
    DATE_TO_EPOCH(SYSDATE)
    ----------------------
                1213895125
    
    SQL> 
  • 2. Re: Built-in epoch time function
    571482 Oracle ACE
    Currently Being Moderated
    1) The function should be:
    create or replace function date_to_epoch(in_date in date) return number is epoch number(38);
    begin
    select (in_date- to_date('1-1-1970 00:00:00','MM-DD-YYYY HH24:Mi:SS'))*24*3600 into epoch from dual;
    return epoch;
    end;

    Otherwise it'll always return sysdate in epoch and not the input date (also when others then null is evil).

    2) For better performance - avoid the function call:
    select epoch_to_date(sysdate) from dual connect by level<1000;
    ....
    Elapsed: 00:00:00.11
    
    select (sysdate-to_date('1-1-1970 00:00:00','MM-DD-YYYY HH24:Mi:SS'))*24*3600 from dual connect by level<1000;
    ...
    Elapsed: 00:00:00.06
  • 3. Re: Built-in epoch time function
    643317 Newbie
    Currently Being Moderated
    note it is wrong to assume we all live at GMT+00:00
    SCOTT@LSC01> select
      2    extract(day from (current_timestamp-timestamp '1970-01-01 00:00:00 +00:00'))*86400+
      3    extract(hour from (current_timestamp-timestamp '1970-01-01 00:00:00 +00:00'))*3600+
      4    extract(minute from (current_timestamp-timestamp '1970-01-01 00:00:00 +00:00'))*60+
      5    extract(second from (current_timestamp-timestamp '1970-01-01 00:00:00 +00:00')) n
      6  from dual;
             N
    ----------
    1213952023
    
    SCOTT@LSC01>
    SCOTT@LSC01> select (sysdate-to_date('1-1-1970 00:00:00','MM-DD-YYYY HH24:Mi:SS'))*24*3600 n from dual;
             N
    ----------
    1213959223
    
    SCOTT@LSC01>
    SCOTT@LSC01> host perl -e "print time"
    1213952023
    from the 7200 seconds difference corresponds to my time zone offset
  • 4. Re: Built-in epoch time function
    user458496 Newbie
    Currently Being Moderated
    We have created our own. We used JAVA's built-in EPOCH converter to create a stored function. My question was, why hasn't Oracle created a date_time function to handle EPOCH time. We can create quite a few things between stored PL/SQL and JAVA, but Oracle has made life easier by creating stored system functions to handle this. This is just one more thing. I would create an SR for this, but I have created SR's for things like this in the past and they have been hard-closed as they are not pertinent to Oracle's functionality. In other words, this is a "Nice to have, but is not needed for Oracle to function". This was actually just a qustion (1) to see if any one at Oracle had thought of this, also, and (2) to see if was the only one who found this to be an annoyance
  • 5. Re: Built-in epoch time function
    643317 Newbie
    Currently Being Moderated
    you created the stored procedure in java?
    what's the code you did write?
  • 6. Re: Built-in epoch time function
    Ganesh Srivatsav Guru
    Currently Being Moderated
    Thanks Chen,

    Started without function call.

    Forgot to replace that with the variable.

    G
  • 7. Re: Built-in epoch time function
    user458496 Newbie
    Currently Being Moderated
    We modified this script found at http://forum.java.sun.com/thread.jspa?threadID=780788&messageID=4441267. This one changes time to epoch.

    import java.util.Calendar;
    import java.util.Date;
    import java.util.GregorianCalendar;

    public class Main {

    public Main() {
    }

    public static void main(String[] args) {
    final long MS_PER_DAY = 24 * 60 * 60 * 1000;

    // Get the current time
    Date now = new Date();

    // Get a time from yesterday
    Date yesterday = new Date(now.getTime() - MS_PER_DAY);

    Calendar calendar = new GregorianCalendar();
    calendar.setTime(yesterday);

    // Get the time yesterday morning at 12:00AM
    calendar.set(Calendar.HOUR_OF_DAY, 0);
    calendar.set(Calendar.MINUTE, 0);
    calendar.set(Calendar.SECOND, 0);
    Date yesterdayStart = calendar.getTime();

    // Get the time yesterday night at 11:59:59PM
    calendar.set(Calendar.HOUR_OF_DAY, 23);
    calendar.set(Calendar.MINUTE, 59);
    calendar.set(Calendar.SECOND, 59);
    Date yesterdayEnd = calendar.getTime();

    System.out.println(
    "Yesterday start: " + yesterdayStart +
    "\t Since Epoch:" + yesterdayStart.getTime());
    System.out.println(
    "Yesterday end: " + yesterdayEnd +
    "\t Since Epoch:" + yesterdayEnd.getTime());
    }
    }

    This site can help you convert epoch to a readible format: http://www.epochconverter.com/

    String date = new java.text.SimpleDateFormat("dd/MM/yyyy HH:mm:ss").format(new java.util.Date (epoch*1000));

    Hope that this helps
  • 8. Re: Built-in epoch time function
    user458496 Newbie
    Currently Being Moderated
    P.S.
    In SQL Server, epoch conversion is done this way:
    Epoch > Readable: DATEADD(s, epoch, '19700101')
    Readable > Epoch: SELECT DATEDIFF(s, '19700101', time field)

    And in MySQL:
    Epoch > Readable: from_unixtime(epoch, optional output format) The default output format is YYY-MM-DD HH:MM:SS
    Readable > Epoch: SELECT unix_timestamp(time) Time format: YYYY-MM-DD HH:MM:SS or YYMMDD or YYYYMMDD

    Source: http://www.epochconverter.com/

    For Oracle?.... This is why I asked if this could be simplified in Oracle.

    End of rant.
  • 9. Re: Built-in epoch time function
    643317 Newbie
    Currently Being Moderated
    here is the code for a java stored procedure
    SCOTT@LSC01> create function epoch return number as language java name'java.lang.System.currentTimeMillis()return int';
      2  /
    
    Function created.
    
    SCOTT@LSC01> select epoch from dual;
               EPOCH
    ----------------
       1214562599878
  • 10. what about
    81993 Newbie
    Currently Being Moderated
    select
    extract(day from (from_tz(cast(current_timestamp as timestamp), sessiontimezone) at time zone 'UTC' -timestamp '1970-01-01 00:00:00 +00:00'))*86400+
    extract(hour from (from_tz(cast(current_timestamp as timestamp), sessiontimezone) at time zone 'UTC' -timestamp '1970-01-01 00:00:00 +00:00'))*3600+
    extract(minute from (from_tz(cast(current_timestamp as timestamp), sessiontimezone) at time zone 'UTC' -timestamp '1970-01-01 00:00:00 +00:00'))*60+
    extract(second from (from_tz(cast(current_timestamp as timestamp), sessiontimezone) at time zone 'UTC' -timestamp '1970-01-01 00:00:00 +00:00')) n
    from dual
    ?
  • 11. Re: what about
    643317 Newbie
    Currently Being Moderated
    you do not need all this cast and from_tz, do you?

    Message was edited by:
    laurent schneider 2

    how does it differ from my first answer?