如果您只需要将自纪元以来的毫秒数转换为日期，则：
SELECt TIMESTAMP '1970-01-01 00:00:00.000' + NUMTODSINTERVAL( 1483228800000 / 1000, 'SECOND' ) AS TIMEFROM DUAL
哪个输出：
TIME-----------------------2017-01-01 00:00:00.000
您只需要年月，然后使用
TRUNC( timestamp, 'MM' )或即可
TO_CHAr( timestamp, 'YYYY-MM' )。
如果您需要处理leap秒，则可以创建一个实用程序包，该实用程序包将调整纪元时间以解决此问题：
CREATE OR REPLACe PACKAGE time_utilsIS 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_utilsIS -- List of the seconds immediately 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_DAYConSTANT 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 IS p_leap_milliseconds BINARY_INTEGER := 0; p_diff INTERVAL DAY(9) TO SECOND(3); BEGIN IF in_datetime IS NULL OR in_epoch IS NULL THEN RETURN NULL; END IF; p_diff := in_datetime - in_epoch; IF in_datetime >= in_epoch THEN FOR i IN 1 .. leap_seconds.COUNT LOOP EXIT WHEN in_datetime < leap_seconds(i); IF in_epoch < leap_seconds(i) THEN p_leap_milliseconds := p_leap_milliseconds + MILLISECONDS_PER_SECOND; END IF; END LOOP; ELSE FOR i IN REVERSE 1 .. leap_seconds.COUNT LOOP EXIT WHEN in_datetime > leap_seconds(i); IF in_epoch > leap_seconds(i) THEN p_leap_milliseconds := p_leap_milliseconds - MILLISECONDS_PER_SECOND; END IF; END LOOP; END IF; RETURN MILLISECONDS_PER_SECOND * EXTRACT( SECOND FROM p_diff )+ MILLISECONDS_PER_MINUTE * EXTRACT( MINUTE FROM p_diff )+ MILLISECONDS_PER_HOUR * EXTRACT( HOUR FROM p_diff )+ MILLISECONDS_PER_DAY * EXTRACT( DAY FROM 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 IS p_datetime TIMESTAMP; BEGIN IF in_milliseconds IS NULL OR in_epoch IS NULL THEN RETURN NULL; END IF; p_datetime := in_epoch + NUMTODSINTERVAL( in_milliseconds / MILLISECONDS_PER_SECOND, 'SECOND' ); IF p_datetime >= in_epoch THEN FOR i IN 1 .. leap_seconds.COUNT LOOP EXIT WHEN p_datetime < leap_seconds(i); IF in_epoch < leap_seconds(i) THEN p_datetime := p_datetime - INTERVAL '1' SECOND; END IF; END LOOP; ELSE FOR i IN REVERSE 1 .. leap_seconds.COUNT LOOP EXIT WHEN p_datetime > leap_seconds(i); IF in_epoch > leap_seconds(i) THEN p_datetime := p_datetime + INTERVAL '1' SECOND; END IF; END LOOP; END IF; RETURN p_datetime; END milliseconds_epoch_to_ts;END;/SHOW ERRORS;
然后，您可以执行以下操作：
SELECt TIME_UTILS.milliseconds_epoch_to_ts( in_milliseconds => 1483228800000, in_epoch => TIMESTAMP '1970-00-00 00:00:00.000' ) AS timeFROM DUAL;
并获得输出：
TIME-----------------------2016-12-31 23:59:33.000
注意：建议使用新的leap秒时，您将需要使软件包保持最新。
更新 ：
SELECt COUNT(*) "EVENTS", TRUNC( TIMESTAMP '1970-01-01 00:00:00.000'+ NUMTODSINTERVAL( MSSTAMP / 1000, 'SECOND' ), 'MM' ) "FINISHED_MONTH"FROM DB_TABLEWHERe MSSTAMP < 1483228800000AND STATUS = 'FINISHED'GROUP BY TRUNC( TIMESTAMP '1970-01-01 00:00:00.000'+ NUMTODSINTERVAL( MSSTAMP / 1000, 'SECOND' ), 'MM' );
