11 Replies Latest reply: Jul 1, 2008 3:01 PM by 643317 RSS

    Built-in epoch time function

    user458496
      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
          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
            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
              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
                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
                  you created the stored procedure in java?
                  what's the code you did write?
                  • 6. Re: Built-in epoch time function
                    Ganesh Srivatsav
                    Thanks Chen,

                    Started without function call.

                    Forgot to replace that with the variable.

                    G
                    • 7. Re: Built-in epoch time function
                      user458496
                      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
                        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
                          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
                            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
                              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?