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' );
Comments
Post a Comment