STDDEV_POP on Timestamps

General

STDDEV_POP on Timestamps

I am stuggling with how to create standard deviation on timestamps.  I have tried breaking the the timestamp apart as an integer calculating and putting it back together but my issue now is how to take my integer and reconvert to a time?

Here is a sample of the sql can someone assist?

DROP TABLE ajEvents;

CREATE VOLATILE TABLE ajEvents AS (

SEL          pe.unvsl_pkg_nbr, 

                        p.dest_pstl_cd,

                        pe.pkg_event_tz_tmstp,

                        pe.pkg_event_dt,

                        CAST(OREPLACE(SUBSTR(CAST(pe.pkg_event_tz_tmstp AS CHAR(19)), 12,5), ':', '') AS INTEGER) AS tm_int

FROM  prod_view_db.package_event pe

JOIN   prod_view_db.event_code pec 

ON pe.event_id_nbr = pec.event_Id_nbr

JOIN   prod_view_db.f_package p 

ON pe.unvsl_pkg_nbr = p.unvsl_pkg_nbr

WHERE pe.pkg_event_dt >= '2015-03-01'

AND pec.clock_stop_flg = 1

AND  pec.event_type_cd = 'PSTDC'

QUALIFY ROW_NUMBER() OVER (PARTITION BY PE.unvsl_pkg_nbr ORDER BY PE.pkg_event_tz_tmstp) = 1

) WITH DATA ON COMMIT PRESERVE ROWS;

COLLECT STATS ON ajEvents COLUMN (unvsl_pkg_nbr);

--sel * from ajEvents;

SELECT dest_pstl_cd,

             --d.first_dow_dt,

                        STDDEV_POP(tm_int) AS SD,

                        MIN(tm_int) AS min_dlvr_tmstp,

                        MAX(tm_int) AS max_dlvr_tmstp,

                        CAST(AVG(tm_int) AS INTEGER) AS avg_dlvr_tmstp,

                        CAST(avg_dlvr_tmstp - (3 * sd) AS INTEGER) AS window_start,

                        CAST(avg_dlvr_tmstp + (3 * sd) AS INTEGER) AS window_end

FROM ajEvents aj

JOIN   d_date d ON aj.pkg_event_dt = d.date_dt

--where dest_pstl_cd in ('53151', '53146')

GROUP BY 1

ORDER BY 1

thanks in advance

Tags (2)