Question: How do I get seconds since the EPOCH UTC in Oracle SQL*PLUS?

In MySQL this is super simple using unix_timestamp(now())

With Oracle it is a pain in the ass so I thought I'd share (yes this accounts for timezones different than GMT):

UPDATE! Does not account for timezones that break semi-hourly

select (sysdate - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400 - substr(TZ_OFFSET(sessiontimezone),1,instr(TZ_OFFSET(sessiontimezone),':')-1) * 3600 from dual;

Now how to you go from seconds since the EPOCH to a formatted, human readable date?

May want to set this first:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

select from_tz(cast(to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS') as timestamp),'UTC') at local + numtodsinterval(1208906459,'SECOND') from dual;

substituting your own seconds from EPOCH for this example: 1208906459
Note that this will convert to your local time zone using the FROM_TZ() function.

If you're migrating (converting) a MySQL database to Oracle RDBMS, here is a universal date setting that will save you time and many headaches because of the irreversible mapping of date types (MySQL has much finer granularity with DATE, TIME, YEAR types, etc)

With default NLS_DATE_FORMAT, this won't work:

SQL> desc banner_dailies;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(38)
 DAYDATE                                   NOT NULL DATE
 IMP                                       NOT NULL NUMBER(38)
 CTS                                       NOT NULL NUMBER(38)
 ACQ                                       NOT NULL NUMBER(38)
 REV_TYPE_ID                               NOT NULL CHAR(1)

SQL> 

insert into banner_dailies values (45,'2008-04-20',1,2,3,'t')
                                      *
ERROR at line 1:
ORA-01861: literal does not match format string

With a little magic it will work (no TO_DATE() function needed):

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> insert into banner_dailies values (45,'2008-04-20',1,2,3,'t');

1 row created.

SQL> 

and even this:

insert into banner_dailies values (45,'20080420164442',1,2,3,'t');

So it will be compatible with MySQL's flexible date formats and further, you won't need to concern yourself so much if the applications you're supporting will be expected to insert from MySQL DATE or DATETIME types (though this will not work for YEAR types).

Cheers - Reed Sandberg (Posted at 22-APR-08 04.20.59.000000000 PM -07:00 or 1208906459)