sql - How to convert timestamp with milliseconds to date in Oracle -

i have msstamp "timestamp milliseconds" in oracle, format: 1483228800000. how can cast milliseconds timestamp date format "yyyy-mm", in order count of finished rows per month previous years.

i have tried different variations of to_date, cast, to_char - i'm unable working.

select    count(*) "events",   to_date(msstamp, 'yyyy-mm') "finished_month"    db_table    msstamp < '1483228800000'  ,    status in ('finished')  group   finished_month asc 

if need convert milliseconds epoch then:

select timestamp '1970-01-01 00:00:00.000'          + numtodsinterval( 1483228800000 / 1000, 'second' )          time   dual 

which outputs:

time ----------------------- 2017-01-01 00:00:00.000 

it want year-month use trunc( timestamp, 'mm' ) or to_char( timestamp, 'yyyy-mm' ).

if need handle leap seconds can create utility package adjust epoch time account this:

create or replace package time_utils   function milliseconds_since_epoch(     in_datetime  in timestamp,     in_epoch     in timestamp default timestamp '1970-01-01 00:00:00'   ) return number;    function milliseconds_epoch_to_ts (     in_milliseconds in number,     in_epoch        in timestamp default timestamp '1970-01-01 00:00:00'   ) return timestamp; end; / show errors;  create or replace package body time_utils   -- list of seconds following leap seconds:   leap_seconds constant sys.odcidatelist := sys.odcidatelist(       date '1972-07-01',       date '1973-01-01',       date '1974-01-01',       date '1975-01-01',       date '1976-01-01',       date '1977-01-01',       date '1978-01-01',       date '1979-01-01',       date '1980-01-01',       date '1981-07-01',       date '1982-07-01',       date '1983-07-01',       date '1985-07-01',       date '1988-01-01',       date '1990-01-01',       date '1991-01-01',       date '1992-07-01',       date '1993-07-01',       date '1994-07-01',       date '1996-01-01',       date '1997-07-01',       date '1999-01-01',       date '2006-01-01',       date '2009-01-01',       date '2012-07-01',       date '2015-07-01',       date '2016-01-01'     );    hours_per_day           constant binary_integer := 24;   minutes_per_hour        constant binary_integer := 60;   seconds_per_minute      constant binary_integer := 60;   milliseconds_per_second constant binary_integer := 1000;    minutes_per_day         constant binary_integer := hours_per_day   * minutes_per_hour;   seconds_per_day         constant binary_integer := minutes_per_day * seconds_per_minute;    milliseconds_per_minute constant binary_integer := seconds_per_minute * milliseconds_per_second;   milliseconds_per_hour   constant binary_integer := minutes_per_hour   * milliseconds_per_minute;   milliseconds_per_day    constant binary_integer := hours_per_day      * milliseconds_per_hour;    function milliseconds_since_epoch(     in_datetime  in timestamp,     in_epoch     in timestamp default timestamp '1970-01-01 00:00:00'   ) return number       p_leap_milliseconds binary_integer := 0;     p_diff              interval day(9) second(3);   begin     if in_datetime null or in_epoch null       return null;     end if;      p_diff := in_datetime - in_epoch;      if in_datetime >= in_epoch       in 1 .. leap_seconds.count loop         exit when in_datetime < leap_seconds(i);         if in_epoch < leap_seconds(i)           p_leap_milliseconds := p_leap_milliseconds + milliseconds_per_second;         end if;       end loop;     else       in reverse 1 .. leap_seconds.count loop         exit when in_datetime > leap_seconds(i);         if in_epoch > leap_seconds(i)           p_leap_milliseconds := p_leap_milliseconds - milliseconds_per_second;         end if;       end loop;     end if;      return   milliseconds_per_second * extract( second p_diff )            + milliseconds_per_minute * extract( minute p_diff )            + milliseconds_per_hour   * extract( hour   p_diff )            + milliseconds_per_day    * extract( day    p_diff )            + p_leap_milliseconds;   end milliseconds_since_epoch;    function milliseconds_epoch_to_ts(     in_milliseconds in number,     in_epoch        in timestamp default timestamp '1970-01-01 00:00:00'   ) return timestamp       p_datetime timestamp;   begin     if in_milliseconds null or in_epoch null       return null;     end if;      p_datetime := in_epoch         + numtodsinterval( in_milliseconds / milliseconds_per_second, 'second' );      if p_datetime >= in_epoch       in 1 .. leap_seconds.count loop         exit when p_datetime < leap_seconds(i);         if in_epoch < leap_seconds(i)           p_datetime := p_datetime - interval '1' second;         end if;       end loop;     else       in reverse 1 .. leap_seconds.count loop         exit when p_datetime > leap_seconds(i);         if in_epoch > leap_seconds(i)           p_datetime := p_datetime + interval '1' second;         end if;       end loop;     end if;      return p_datetime;   end milliseconds_epoch_to_ts; end; / show errors; 

then can do:

select time_utils.milliseconds_epoch_to_ts(          in_milliseconds => 1483228800000,          in_epoch        => timestamp '1970-00-00 00:00:00.000'        ) time dual; 

and output:

time ----------------------- 2016-12-31 23:59:33.000 

note: need keep package up-to-date when new leap-seconds proposed.

update:

select count(*) "events",        trunc(          timestamp '1970-01-01 00:00:00.000'            + numtodsinterval( msstamp / 1000, 'second' ),          'mm'        ) "finished_month"   db_table  msstamp < 1483228800000 ,    status = 'finished' group        trunc(          timestamp '1970-01-01 00:00:00.000'            + numtodsinterval( msstamp / 1000, 'second' ),          'mm'        );