-- SET @pbi_datetime = '2018-01-01 10:00:10'

-- POPULATE DIM_ORDER_TIME
insert into `dim_order_time`
(pk_dim_order_time, hour, minute, second, second_of_day, `inserted_at`)
select
  t.pk_dim_order_time                                     as `pk_dim_order_time`,
  FLOOR(t.time_sec / 3600)          as `hour`,
  FLOOR(MOD(t.time_sec, 3600) / 60) as `minute`,
  MOD(t.time_sec, 60)               as `second`,
  t.time_sec                                        as `second_of_day`,
  @db_date_time as inserted_at
from (
  select
    CAST(st.`time` as SIGNED) as pk_dim_order_time,
    CAST(SUBSTRING(st.`time`, 1, 2) as SIGNED) * 3600
      + CAST(SUBSTRING(st.`time`, 3, 2) as SIGNED) * 60
            + CAST(SUBSTRING(st.`time`, 5, 2) as SIGNED) as time_sec
  from `bim_stg_time` st
  WHERE CAST(st.`time` AS SIGNED) > (SELECT COALESCE(max(`pk_dim_order_time`), 0) FROM `dim_order_time`)
) t
order by t.time_sec ASC;

-- {{IF_ANALYZE}}dim_order_time{{/IF_ANALYZE}}