manejo de timestamp

[augusto_jaramil]

Que tal amigos....

Una pregunta ignorante (de ignorante):

Como puedo manejar el tipo de datos TIMESTAMP WITH TIME ZONE que maneja el PostgreSql por ejemplo.

En PostgreSql al hacer insert sobre este tipo de datos me queda registrado como
YYYY-MM-DD HH24:MI:SS.xxxx-yy
Como ejemplo 2008-02-05 18:12:54.6979-05

En Oracle cual es su tipo de dato currespondiente, ya que al definirlo como DATE me da error de formato en fecha

Esto se me hace necesario para hacer una migracion de PostgreSql a Oracle y me esta fallando en ese pedacito

Agradezco de antemano sus valiosos comentarios

Un Cordial Saludo

[matanga]

Hola,

Código:

SQL> create table t10 (fecha timestamp);

Table created.

SQL> insert into t10 values (sysdate);

1 row created.

SQL> select * from t10;

FECHA
---------------------------------------------------------------------------
01-FEB-08 11.11.08.000000 AM

EDITADO

Diria que tengo que corregir la fecha del servidor :)

Saludos

[augusto_jaramil]

Gracias matanga por responder.

Cree la tabla como me indicates create table x (fec timestamp with time zone)

Al hacer insert into x values ('2008-02-05 06:12:54.6979-05') que es como me esta llegando en un archivo plano (.csv)

Me da este error
insert into x values ('2008-02-05 18:12:54.6979-05')
*
ERROR at line 1:
ORA-01849: hour must be between 1 and 12

Al cambiar 18:12:54 por 06:12:54 me arroja este error
ORA-01849: hour must be between 1 and 12

Un Cordial Saludo

[matanga]

Hola,

Cuando haces una conversion de String a Fecha tienes que definir una mascara, te dejo dos links, uno para definirlo a nivel de sentencia SQL y otro para definirlo a nivel de sesion o parametro de la base de datos.

http://download.oracle.com/docs/cd/B...htm#sthref2377
http://download.oracle.com/docs/cd/B....htm#sthref566

Saludos

[kikolice]

"Cree la tabla como me indicates create table x (fec timestamp with time zone)"

que no era?

create table x (fec timestamp)

[augusto_jaramil]

Gracias de nuevo matanga y kikolice por su valiosa colaboracion

Les cuento que pailas... nada que quiere cancionar

Atendiendo los links que me indica matanga encontre esto
alter session set nls_timestamp_tz_format = 'YYYY-MM-DD HH:MI:SS.FF-TZH';

Pero al hacer este insert a la tabla con el formato que me llega de PostgreSql
insert into x values ('2008-02-05 18:12:54.6979-05');

Me reporta la siguiente excepcion
ORA-01849: hour must be between 1 and 12

Al cambiar el 18:12:54..... por 06:12:54..... me reporta la misma excepcion

Que hago?

Un Cordial Saludo

[matanga]

Hola,

Intenta con esta mascara.

Código:

alter session set nls_timestamp_tz_format = 'YYYY-MM-DD HH24:MI:SS.FF-TZH'

Saludos

[augusto_jaramil]

Saludos matanga

Finalmente creo que canciono....

Hice lo siguiente
create table x (fec timestamp with time zone);
alter session set nls_timestamp_tz_format = 'YYYY-MM-DD HH24:MI:SS.FF-TZH';
insert into x values ('2008-02-05 18:12:54.6979-05');
select * from x;

resultado:
FEC
---------------------------------------------------------------------------
2008-02-05 18:12:54.697900-+05

Agradezco sinceramente su valiosa colaboracion y la de kikolice igualmente por su interes en el tema.... me sacaron de un lio el tenaz


Un Cordial Saludo

[Destroyer]

Hola a todos;

Tengo un pequeño problema y una duda, quisiera saber si el manejo del timestamp se puede aplicar en una importación de datos que se encuentran en un archivo de texto, los registros se encuentran por fecha, hora y minutos. Necesito importarlos a una tabla en la db de oracle, lo estoy haciendo por medio del sqlldr pero me da un error en el campo de fecha a la hora de importar.

[matanga]

Hola,

¿Cual es el error que estas teniendo?, si necesitas aplicar la funcion TO_TIMESTAMP en la carga a traves del SQL*Loader, tu fichero de control sera algo asi.

Código:

load data
infile 'datos.dat'
into table t1
fields terminated by '|' optionally enclosed by '"'
(
id,
fecha "to_timestamp(:fecha,'DD-Mon-RR HH24:MI:SS.FF')"
)

Saludos

[Destroyer]

El fichero de control es este:

LOAD DATA
INFILE 'Prueba.txt'
INTO TABLE CENGICAN
FIELDS TERMINATED BY "\t"
(FECHA "TO_TIMESTAMP (:FECHA, 'DD-MM-YYYY HH24:MI')",
TEMPERATURA,
RAD_SOLAR,
HUMEDAD_REL,
PRECIPITACION,
VEL_VIENTO,
PRES_ATMOSFERICA,
MOJADURA,
DIR_VIENTO,
BATERIA,
FREQ_ENTRADA,
FREQ_SALIDA,
PANEL_SOLAR)

Pero me da el siguiente error:
Registro 1: Rechazado - Error en tabla CENGICAN, columna FECHA.
ORA-01830: la máscara de formato de fecha termina antes de convertir toda la cadena de entrada.

[matanga]

Hola,

Código:

(FECHA "TO_TIMESTAMP (:FECHA, 'DD-MM-YYYY HH24:MI')",

El problema esta en esta linea, es probable que en el fichero de datos exista una fecha con mas informacion que la mascara, por ejemplo, con segundos.

Intenta con una mascara mas completa, si los datos no existen, Oracle los completa con 0

Código:

(FECHA "TO_TIMESTAMP (:FECHA, 'DD-MM-YYYY HH24:MI:SS.FF')",

Saludos

[Destroyer]

Hola matanga, con tu ayuda he podido solucionar el problema del campo fecha, pero ahora me esta dando otro error, es en el campo siguiente, en la tabla de la base de datos de oracle este campo esta como numérico y el error es el siguiente:

Registro 1: Rechazado - Error en tabla CENGICAN, columna TEMPERATURA.
ORA-01722: número no válido.

me imagino que también hay que hacerle la conversión de string a numérico, tengo entendido que para eso se utiliza la función TO_NUMBER pero no se como va el formato... podría ayudarme, gracias.

[matanga]

Hola,

El problema es que la funcion TO_NUMBER te va a arrojar el mismo error, parece ser que la columna TEMPERATURA en el fichero de datos contiene algunos valores que no son numericos, esto lo puedes ver en el fichero de bad que genera el SQL*Loader.

Saludos

[Destroyer]

Hola,
En el fichero de datos solo tengo un dato de tipo fecha y el resto de datos deberían ser numéricos, pero con se encuentran en un archivo de texto por default todos son de tipo caracter, y necesito importarlos a un tabla en la base datos donde ya pasarían cada dato con su tipo.

En el TO_TIMESTAMP, tenías razón a la hora de escribir el formato, me faltaban los segundos, ya lo corregí, pero ahora el problema es con el resto de datos que deberían de ser tipo numéricos.

[matanga]

Hola,

El error ORA-01722 lo da Oracle cuando le pedimos convertir un valor al tipo NUMBER y no lo puede hacer porque existen caracteres no convertibles, fijate el ejemplo

Código:

SQL> select to_number('1') from dual;

TO_NUMBER('1')
--------------
             1

SQL> select to_number('a') from dual;
select to_number('a') from dual
                 *
ERROR at line 1:
ORA-01722: invalid number

Si generas el fichero bad, donde el SQL*Loader deja las filas descartadas, vas a ver que al menos uno de los campos del fichero contiene valores que no se pueden convertir a NUMBER.

Como bien dices, los datos en el fichero no tienen tipo, Oracle hace la conversion implicita en funcion del tipo de dato de cada una de las columnas de las tablas, en el caso de la funcion TO_TIMESTAMP hay una tarea adicional que es indicarle la mascara, es decir, la forma en que viene la fecha, esto solo hace falta para los tipos NUMBER cuando quieres especificar una mascara para decimales.

Con el parametro bad le indicas el nombre del fichero.

Código:

sqlldr / control=control.ctl data=datos.dat bad=datos.bad

Saludos

[Destroyer]

Tienes razón, voy a ver de que otra manera le doy solución a esta importación, me habían dicho que importara los datos a una tabla plana donde todos sus campos fueran de tipo caracter y luego con un procedimiento las pasara a una tabla formal de la base de datos, pero creo que hay que usar el lenguaje PL/SQL y no tengo mucha experiencia con este lenguaje...

[matanga]

Hola,

Suponiendo que haces eso, tabla con todos los tipos varchar y un proceso en PL/SQL para pasar los datos a la tabla definitiva, ¿que seran de los registros que no cumplan con la conversion a NUMBER?.

Si la respuesta es descartarlos, el SQL*Loader puede hacer eso perfectamente.

Saludos

[Destroyer]

Como lo podría hacer? la verdad es que los datos que no son numéricos no me interesan... y mejor dijo no son datos, son asteriscos.

Lo que pasa es que utilizo un software que recopila todos estos datos por medio de un Gateway, los datos son de un red de estaciones meteorológicas que se encuentran fuera de la oficina, pues el software me permite elegir en que tipo de formato deseo guardar los archivos, siempre y cuando sean extensiones de windows, podría ser .txt, .xls, .csv, .doc etc. pero cuando no hace bien una lectura entonces coloca un * en ves de un dato numérico por lo tanto no me interesan esos datos, solo los que realmente son numéricos.

Como podría hacer para importar los datos que solamente son numéricos por medio del sqlldr???

[matanga]

Hola,

Para terminar de entender bien, supongamos que tienes el siguiente registro

Código:

12-02-2008|20|15|*|200

Lo que te interesa es ¿descartar el registro completo, o reemplazar el * por NULL? (NULL por dar un ejemplo)

Si la idea es descartar el registro completo, no tienes que hacer nada, el SQL*Loader lo hace por defecto y continua con el resto de la carga, lo unico que hay que configurar es el parametro errors que por defecto es 50.

Si la idea es reemplazar el * por un NULL, puedes crear una funcion en Oracle que realice la tarea e invocarla en la carga, de la misma forma que invocas TO_TIMESTAMP o TO_NUMBER.

Saludos

[Destroyer]

Las dos formas son correctas, en alguna ocasión hay que reemplazar el * por un NULL, pero la mayoría de veces hay que omitir el registro completo, se supone que el SQLLDR lo hace automáticamente solo hay que configurar el parámetro de errores que trae por defecto, pero entonces ya no tengo que utilizar la función TO_NUMBER???

[matanga]

Hola,

La funcion TO_NUMBER solo te hace falta cuando necesitas implementar una mascara con decimales, si este no es el caso, Oracle hace una conversion implicita, es lo que te comentaba en algun post anterior.

El parametro errors=valor es la cantidad maxima de errores permitidos (registros descartados) antes de que el SQL*Loader aborte la carga, por defecto 50.

De momento no me doy cuenta como implementar las dos cosas al mismo tiempo, reemplazar * por NULL y descartar registros, diria que son excluyentes, a menos que estemos hablando de campos diferentes, por ejemplo

Si el campo de posicion 2 es *, reemplazar por NULL y
Si el campo de posicion 4 es *, descartar el registro

Pero si la logica es

Si el campo de posicion 2 es * y el registro es el numero 10, reemplazar por NULL y
Si el campo de posicion 2 es * y el registro es el numero 20, descartar el registro

ya no creo que se pueda hacer.

Saludos

[Destroyer]

Ya cambien el parámetro de errors=3000 y logre importar solo los datos numéricos que son los que me interesan, los demás los esta omitiendo hasta ese paso esta bien, pero resulta que las fechas las esta importando pero en la tabla solo aparece el registro de la fecha, debería de aparecer también la hora y minutos.