Convert number to date

Question

In Oracle, I have column named Create_date with data returning as 1400003659, 1400072380, and 1403796514 as examples. The column type is NOT NULL NUMBER(15). I'm trying to edit my SELECT statement to return these values as dates (or are these dates and times?).

I've tried the below, but all are returning errors:

SELECT to_date(Create_date, 'YYMMDD'),

SELECT to_date(to_char(Create_date), 'YYMMDD'),

SELECT to_timestamp(Create_date, 'YYMMDD'),

SELECT to_date(LPAD(Create_date, 15, '0'), 'YYMMDD'),

SELECT to_date(LPAD(Create_date), 'YYMMDD'),

An example error message I'm receiving:

SQL Error: ORA-01843: not a valid month
01843. 00000 -  "not a valid month"

They look like UNIX Timestamps - this may help: http://stackoverflow.com/questions/10554405/convert-unixtime-to-datetime-sql-oracle — Siyual, Jan 05 '16 at 15:12

score 4 · Accepted Answer · edited Apr 13 '17 at 12:42

4

This looks like a unix timestamp which is the number of seconds since 1/1/1970.

If you want just the date, you need to calculate the number of days and add it to 1/1/1970 like so:

to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval(1400003659,'SECOND')

If you want to retain the timestamp, you can do so like this:

to_char(to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval(1400003659,'SECOND'),'YYYY-MM-DD HH24:MI:SS')

See this for more information.

edited Apr 13 '17 at 12:42

Community

1
1

answered Jan 05 '16 at 15:19

Lefka

633
6
20

Thanks! Replacing the test value of 1400003659 with the column name Create_date works great! – Ken Jan 05 '16 at 15:24

Rahul Tripathi · Answer 2 · 2016-01-05T15:19:53.503

0

Try like this:

select to_date('01-01-1970 1:00:00','MM-DD-YYYY HH24:Mi:SS') + (1400003659/86400) from dual;

FIDDLE DEMO

edited Jan 05 '16 at 15:19

answered Jan 05 '16 at 15:10

Rahul Tripathi

168,305
31
280
331

score 0 · Answer 3 · answered Jan 05 '16 at 15:17

You need to know what that number means.

If those are UNIX dates (number of second since January 1, 1970), then you can use simple date arithmetic from that baseline by adding the value / 86400 (seconds in a day) to jan 1 1970:

select to_date('01011970','ddmmyyyy') + (1400003659/86400) from dual;

returns: 13/05/2014 5:54:19 PM

Wernfried Domscheit · Answer 4 · 2016-01-06T06:38:01.930

The answers are not precise! UNIX time is seconds since 1970-01-01 00:00:00 UTC!

So, unless your database server runs on UTC you should do it like this:

SELECT
   (TIMESTAMP '1970-01-01 00:00:00' AT TIME ZONE 'UTC' 
        + 1400003659 * INTERVAL '1' SECOND) AT LOCAL
FROM dual;

or

SELECT
   (TIMESTAMP '1970-01-01 00:00:00' AT TIME ZONE 'UTC' 
         + numtodsinterval(1400003659,'second')) AT LOCAL
FROM dual;

or to get the time at time zone of database server's operating system

SELECT
   (TIMESTAMP '1970-01-01 00:00:00' AT TIME ZONE 'UTC' 
         + numtodsinterval(1400003659,'second')) AT TO_CHAR(SYSTIMESTAMP, 'tzr')
FROM dual;

Convert number to date

4 Answers4