CODEWITHSUNDEEP
sqloracle-database
angus
angus

Reputation: 3320

Converting NUMBER to DATE

I have a numeric field in my Oracle database that represents the date.

I'm not so familiar with Oracle commands.

I was wondering if anyone could provide some guide here.

Thanks.

example: 1435755908 = 7/1/2015 9:05

Upvotes: 0

Views: 98

Answers (2)

Alex Poole
Alex Poole

Reputation: 191235

Since there seems to be a time zone difference:

select date '1970-01-01' + 1435755908/86400 as converted from dual;

CONVERTED                              
----------------------------------------
2015-07-01 13:05:08

you seem to need to do some time zone manipulation. Epoch times are UTC so you can use from_tz to declare that, and then at time zone to get the US/East Coast equivalent:

select from_tz(cast(date '1970-01-01' + 1435755908/86400 as timestamp), 'UTC')
  at time zone 'America/New_York' as converted from dual;

CONVERTED                              
----------------------------------------
2015-07-01 09:05:08.000 AMERICA/NEW_YORK

Which is a time stamp with time zone. If you want it as a plain date then cast it:

select cast(from_tz(cast(date '1970-01-01' + 1435755908/86400 as timestamp), 'UTC')
  at time zone 'America/New_York' as date) as converted from dual;

CONVERTED                              
----------------------------------------
2015-07-01 09:05:08

Upvotes: 1

dnoeth
dnoeth

Reputation: 60462

This is a Unix Timestamp, i.e. the seconds since January 1970, try this formula:

timestamp '1970-01-01 00:00:00' + 1435755908/86400

Upvotes: 1

Related Questions