oracle 日期维度,【求助】请问如何用date和time来创建时间维度表

//时间维度

//易铎

//2011-08-01

$x = 0;

for($i = 0; $i <= 23; $i ++) {

for($j = 0; $j <= 59; $j ++) {

if ($i < 12) {

$appm = '上午';

} else {

$appm = '下午';

}

if (($i >= 9 && $i < 12) || ($i >= 13 && $i < 18)) {

$worktime = "是";

} else {

$worktime = "不是";

}

echo sprintf ( "insert into DimTime ([TimeKey] ,[HourKey],[HourText],[worktime],[MinuteKey],[ampm])values(%d,%d,'%d点','%s','%02d:%02d','%s')", $x, $i, $i, $worktime, $i, $j, $appm ) . "\r\n";

$x ++;

}

}

?>

执行结果：

insert into DimTime ([TimeKey] ,[HourKey],[HourText],[worktime],[MinuteKey],[ampm])values(0,0,'0点','不是','00:00','上午')

insert into DimTime ([TimeKey] ,[HourKey],[HourText],[worktime],[MinuteKey],[ampm])values(1,0,'0点','不是','00:01','上午')

insert into DimTime ([TimeKey] ,[HourKey],[HourText],[worktime],[MinuteKey],[ampm])values(2,0,'0点','不是','00:02','上午')

...

insert into DimTime ([TimeKey] ,[HourKey],[HourText],[worktime],[MinuteKey],[ampm])values(1437,23,'23点','不是','23:57','下午')

insert into DimTime ([TimeKey] ,[HourKey],[HourText],[worktime],[MinuteKey],[ampm])values(1438,23,'23点','不是','23:58','下午')

insert into DimTime ([TimeKey] ,[HourKey],[HourText],[worktime],[MinuteKey],[ampm])values(1439,23,'23点','不是','23:59','下午')

共1440条，粒度为每分钟一条。

时间维度表结构

CREATE TABLE [dbo].[DimTime](

[TimeKey] [smallint] NOT NULL,

[HourKey] [tinyint] NOT NULL,

[MinuteKey] [nchar](5) NOT NULL,

[worktime] [nchar](2) NOT NULL,

[ampm] [nchar](4) NOT NULL,

[HourText] [nchar](3) NOT NULL,

CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED

(

[TimeKey] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]