Generate Time Dimension in Netezza

Posted: December 17, 2015 in Netezza

SELECT B.*, SUBSTR(B.DATE_TIME,1,2) AS CUR_HOUR ,
SUBSTR(B.DATE_TIME, instr(B.DATE_TIME,’:’,1,1)+1 , instr(B.DATE_TIME,’:’,1,2) – instr(B.DATE_TIME,’:’,1,1) -1)  AS MINUTE ,
SUBSTR(B.DATE_TIME,7,9) AS SECOND
from (SELECT A.*, A.DW_TIME_ID / 60 AS CUM_Minutes
FROM (SELECT DISTINCT  (’00:00:01′::TIME * ((mult.idx * 1024) + base.idx)) as DATE_TIME , (‘1’ * ((mult.idx * 1024) + base.idx)) as DW_TIME_ID
FROM  _v_vector_idx base,
(SELECT idx FROM _v_vector_idx) mult
WHERE DATE_TIME BETWEEN ’00:00:01′ and ’23:59:59′ ) A ) B

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s