Which function to convert "983898064" to a time? 1

[hallux]

Hello,
From a table I have been asked to select the time from an integer field and it contains numbers like "983898064". Anybody ideas?

Thanks,
-Hallux

 

[SantaMufasa]

Hallux,

That number is "Universal Time Coordinated" (UTC) time. It represents the number of seconds since January 1, 1970. Following are two functions for you that go either direction, (UTC to Oracle) and (Oracle to UTC), followed by examples using your data:

create or replace function utc_to_oracle (utc_in in number) return date is
begin
	return to_date('01.01.70','dd.mm.rr')+(utc_in/(60*60*24));
end;
/

create or replace function utc_from_oracle (date_in in date) return number is
begin
	return (date_in-to_date('01.01.70','dd.mm.rr'))*(60*60*24);
end;
/

SQL> col a heading "Hallux's UTC|to Oracle" format a20
SQL> select to_char(utc_to_oracle(983898064),'yyyy-mm-dd hh24:mi:ss') a from dual;

Hallux's UTC
to Oracle
--------------------
2001-03-06 17:01:04

SQL> col b heading "Hallux's|Date to|UTC" format 999999999
SQL> select utc_from_oracle(to_date('2001-03-06 17:01:04','yyyy-mm-dd hh24:mi:ss')) b from dual;

  Hallux's
   Date to
       UTC
----------
 983898064
SQL>

Let me know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:19 (13Feb04) UTC (aka "GMT" and "Zulu&quot, 18:19 (12Feb04) Mountain Time)

 

[hallux]

Wow!
Thanks alot for the detailed response.
FYI: I'm located in WVC.
-Hallux