CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Kousalik
Kousalik

Reputation: 3137

Insert UTC timestamp (millis) into Oracle timestamp column

Is there a way how to directly convert value in milliseconds (e.g. 1480515430991) to Oracle TIMESTAMP(6) column? Like some pattern I'm missing for the TO_TIMESTAMP or TO_DATE functions?

All I could find so far are some calculations with intervals and to_date('1970-01-01','YYYY-MM-DD') or other crazy "manual" calculations.

Thanks

EDIT: Thanks guys. I didn't ask how to do the conversion though. I asked if there is a direct (native, more straightforward) way to achieve it and avoid these calculations for a given input. I am just a curious person and there are many undocumented features out there (Oracle not excluded). I guess NO is my answer then.

Upvotes: 1

Views: 3091

Answers (3)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59476

Correct function, i.e. include time zone consideration and milliseconds would be this one (using literals):

create or replace function epoch2timestamp(epoch number) return timestamp as
begin
return (TIMESTAMP '1970-01-01 00:00:00 UTC' + epoch/1000 * INTERVAL '1' SECOND) AT LOCAL;
end;
/

Upvotes: 2

jera
jera

Reputation: 322

If you want to express the unix time, the time since epoch, you need seconds, not milliseconds. See Unixtime

Oracle's datatype TIMESTAMP is in fractional seconds, as you can read in the Oracle documentation. Link zu 11g documentation

Year, month, and day values of date, as well as hour, minute, and second values of time, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values of fractional_seconds_precision are 0 to 9.

And to answer your question: there is a TO_TIMESTAMP function. See 11g documentation

The TO_TIMESTAMP function converts text data to a value of TIMESTAMP data type.

You can use it like this

TO_TIMESTAMP('2016/11/30 15:53:18', 'YYYY/MM/DD HH:MI:SS')

and would get '30-NOV-16 15.53.18.000000000 AM'

If, for some reason, you really need to display the seconds sind epoch, you can use the noncrazy calculation

select (SYSDATE - to_date('1970-01-01', 'yyyy-MM-dd')) * 24 * 60 * 60 from dual;

Upvotes: -1

Kacper
Kacper

Reputation: 4818

This is how to get timestamp from epoch.:

select to_timestamp('1970-01-01','yyyy-mm-dd') + ( 1 / 24 / 60 / 60 / 1000) * epoch from dual;

So in insert please insert to_timestamp('1970-01-01','yyyy-mm-dd') + ( 1 / 24 / 60 / 60 / 1000) * epoch instead of epoch. You can also create function for that:

create or replace function epoch2timestamp(epoch number) return timestamp as
begin
return to_timestamp('1970-01-01','yyyy-mm-dd') + ( 1 / 24 / 60 / 60 / 1000) * epoch;
end;
/

And then operate on function. These are not "crazy manual calculations" just a legal way of conversion.

Upvotes: 1

Related Questions