Con este script de Oracle SQL se crea una tabla DIM_TIEMPO y se rellena con los valores comprendidos entre las fechas que se indiquen en las variables FechaDesde y FechaHasta. Puede ser muy útil para la creación de la tabla de tiempo de cualquier Data Warehouse.
Esta es la versión para una base de datos Oracle, con Oracle SQL, que se suma a las que han creado anteriormente il_masacratore y Dario Bernabeu para Microsoft SQL Server y Oracle MySQL en sus respectivos blogs:
Estructura de la Dimensión Tiempo y Script de carga para Ms SQL Server
Estructura de la Dimensión Tiempo y Procedure de carga para MySQL
----------------------------------------------------
-- SQL de Creación de la tabla DIM_TIEMPO --
----------------------------------------------------
drop table DIM_TIEMPO;
create table DIM_TIEMPO
(
FechaSK number not null,
Fecha date not null PRIMARY KEY,
Año number not null,
Trimestre number not null,
Mes number not null,
Semana number not null,
Dia number not null,
DiaSemana number not null,
NTrimestre varchar2(7) not null,
NMes varchar2(15) not null,
NMes3L varchar2(3) not null,
NSemana varchar2(10) not null,
NDia varchar2(6) not null,
NDiaSemana varchar2(10) not null
);------------------------------------------------------------
-- Script Oracle SQL de carga de los datos entre fechas --
------------------------------------------------------------
DECLARE
FechaDesde date;
FechaHasta date;
BEGIN
--Borrar datos actuales, si fuese necesario
--TRUNCATE TABLE DIM_TIEMPO
--Rango de fechas a generar: del 01/01/2006 al 31/12/Año actual+2
FechaDesde := TO_DATE('20060101','YYYYMMDD');
FechaHasta := TO_DATE((TO_CHAR(sysdate,'YYYY')+2 || '1231'),'YYYYMMDD');
WHILE FechaDesde <= FechaHasta LOOP
INSERT INTO DIM_TIEMPO
(
FechaSK,
Fecha,
Año,
Trimestre,
Mes,
Semana,
Dia,
DiaSemana,
NTrimestre,
NMes,
NMes3L,
NSemana,
NDia,
NDiaSemana
)
VALUES
(
to_char(FechaDesde,'YYYYMMDD'),
FechaDesde,
to_char(FechaDesde,'YYYY'),
to_char(FechaDesde, 'Q'),
to_char(FechaDesde,'MM'),
to_char(FechaDesde,'WW'),
to_char(FechaDesde,'DD'),
to_char(FechaDesde,'D'),
'T'||to_char(FechaDesde, 'Q')||'/'||to_char(FechaDesde,'YY'),
to_char(FechaDesde,'MONTH'),
to_char(FechaDesde,'MON'),
'Sem '||to_char(FechaDesde,'WW')||'/'||to_char(FechaDesde,'YY'),
to_char(FechaDesde,'DD MON'),
to_char(FechaDesde,'DAY')
);
--Incremento del bucle
FechaDesde := FechaDesde + 1;
END LOOP;
END;
Como cada uno se adaptará el formato de las fechas al que más le convenga, aprovecho para adjuntar esta tabla de ayuda obtenida de Oradev. Contiene descripciones de la sintaxis que se puede utilizar en las máscaras de formato de fechas de las funciones TO_CHAR y TO_DATE de Oracle:
| Format mask | Description |
|---|---|
| CC | Century |
| SCC | Century BC prefixed with - |
| YYYY | Year with 4 numbers |
| SYYY | Year BC prefixed with - |
| IYYY | ISO Year with 4 numbers |
| YY | Year with 2 numbers |
| RR | Year with 2 numbers with Y2k compatibility |
| YEAR | Year in characters |
| SYEAR | Year in characters, BC prefixed with - |
| BC | BC/AD Indicator * |
| Q | Quarter in numbers (1,2,3,4) |
| MM | Month of year 01, 02...12 |
| MONTH | Month in characters (i.e. January) |
| MON | JAN, FEB |
| WW | Weeknumber (i.e. 1) |
| W | Weeknumber of the month (i.e. 5) |
| IW | Weeknumber of the year in ISO standard. |
| DDD | Day of year in numbers (i.e. 365) |
| DD | Day of the month in numbers (i.e. 28) |
| D | Day of week in numbers(i.e. 7) |
| DAY | Day of the week in characters (i.e. Monday) |
| FMDAY | Day of the week in characters (i.e. Monday) |
| DY | Day of the week in short character description (i.e. SUN) |
| J | Julian Day (number of days since January 1 4713 BC, where January 1 4713 BC is 1 in Oracle) |
| HH | Hournumber of the day (1-12) |
| HH12 | Hournumber of the day (1-12) |
| HH24 | Hournumber of the day with 24Hours notation (1-24) |
| AM | AM or PM |
| PM | AM or PM |
| MI | Number of minutes (i.e. 59) |
| SS | Number of seconds (i.e. 59) |
| SSSSS | Number of seconds this day. |
| DS | Short date format. Depends on NLS-settings. Use only with timestamp. |
| DL | Long date format. Depends on NLS-settings. Use only with timestamp. |
| E | Abbreviated era name. Valid only for calendars: Japanese Imperial, ROC Official and Thai Buddha.. (Input-only) |
| EE | The full era name |
| FF | The fractional seconds. Use with timestamp. |
| FF1..FF9 | The fractional seconds. Use with timestamp. The digit controls the number of decimal digits used for fractional seconds. |
| FM | Fill Mode: suppresses blianks in output from conversion |
| FX | Format Exact: requires exact pattern matching between data and format model. |
| IYY or IY or I the last 3,2,1 digits of the ISO standard year. Output only | |
| RM | The Roman numeral representation of the month (I .. XII) |
| RR | The last 2 digits of the year. |
| RRRR | The last 2 digits of the year when used for output. Accepts fout-digit years when used for input. |
| SCC | Century. BC dates are prefixed with a minus. |
| CC | Century |
| SP | Spelled format. Can appear of the end of a number element. The result is always in english. For example month 10 in format MMSP returns "ten" |
| SPTH | Spelled and ordinal format; 1 results in first. |
| TH | Converts a number to it's ordinal format. For example 1 becoms 1st. |
| TS | Short time format. Depends on NLS-settings. Use only with timestamp. |
| TZD | Abbreviated time zone name. ie PST. |
| TZH | Time zone hour displacement. |
| TZM | Time zone minute displacement. |
| TZR | Time zone region |
| X | Local radix character. In america this is a period (.) |
Adjunto procedimiento
Submitted by Anonim. (not verified) on 19 July, 2010 - 02:11
Adjunto procedimiento completo de PL/SQL de Oracle al que se le adicionan 3 campos: periodo que no es relevante, fecha de corte que es el ultimo dia del trimestre de la fecha, la fecha de corte anterior que es la fecha del anterior trimestre.
Espero les sirva.
CREATE OR REPLACE PROCEDURE CARGADIMTIEMPO IS
tmpVar NUMBER;
FechaDesde date;
FechaHasta date;
FechaDesdeStr VARCHAR2(8);
err_num NUMBER;
err_msg VARCHAR2(255);
BEGIN
tmpVar := 0;
FechaDesde := TO_DATE('19941231','YYYYMMDD');
FechaHasta := TO_DATE('20181231','YYYYMMDD');
WHILE FechaDesde <= FechaHasta LOOP
FechaDesdeStr := to_char( FechaDesde, 'YYYYMMDD') ;
INSERT INTO DIM_TIEMPO
(
FechaSK,
Fecha,
Año,
Trimestre,
Mes,
Semana,
Dia,
DiaSemana,
NTrimestre,
NMes,
NMes3L,
NSemana,
NDia,
NDiaSemana,
Periodo,
FechaCorte,
FechaCorteAnt
)
VALUES
(
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD') ,'YYYYMMDD'),
FechaDesde,
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'MM'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'WW'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'DD'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'D'),
'T'||to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q')||'/'||to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YY'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'MONTH'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'MON'),
'Sem '||to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'WW')||'/'||to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YY'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'DD MON'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'DAY'),
CASE
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 1 THEN 3
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 2 THEN 6
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 3 THEN 9
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 4 THEN 12
END,
CASE
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 1 THEN TO_DATE('31/03/' || to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'), 'DD/MM/YYYY')
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 2 THEN TO_DATE('30/06/' || to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'), 'DD/MM/YYYY')
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 3 THEN TO_DATE('30/09/' || to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'), 'DD/MM/YYYY')
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 4 THEN TO_DATE('31/12/' || to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'), 'DD/MM/YYYY')
END,
CASE
WHEN to_char(to_date(FechaDesdeStr,'YYYYMMDD'), 'Q') = 1 THEN TO_DATE('31/12/' || to_char(to_number(to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'))-1), 'DD/MM/YYYY')
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 2 THEN TO_DATE('31/03/' || to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'), 'DD/MM/YYYY')
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 3 THEN TO_DATE('30/06/' || to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'), 'DD/MM/YYYY')
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 4 THEN TO_DATE('30/09/' || to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'), 'DD/MM/YYYY')
END
);
-- Incremento del bucle
commit ;
FechaDesde := FechaDesde + 1;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
err_num := SQLCODE;
err_msg := SQLERRM;
DBMS_OUTPUT.put_line('Error Problemas :'||TO_CHAR(err_num) || ' ' || err_msg );
DBMS_OUTPUT.put_line(err_msg);
END CARGADIMTIEMPO;
Gracias por la aportación de
Submitted by Carlos on 19 July, 2010 - 11:05
In reply to Adjunto procedimiento by Anonim. (not verified)
Gracias por la aportación de esta ampliación :)