PostgreSQL Mexico

Inicio

Novedades de pgsql en la 7.0

Entrevista con Bruce Momjian

Proyecto eros

Ligas interesantes

Manejo de Estilos de Fechas en PostgreSQL

Introduccion a PostgreSQL
Manejo de estilos de fechas en PostgreSQL
Manejo de estilos de fechas en PostgreSQL
Roberto Andrade Fonseca
Tabla de contenidos
Fechas y formatos
Formato de salida de fechas y horas
Formato de entrada de fechas y horas
Interpretación de Fechas
Fechas y formatos
Uno de los temas recurrentes en la lista de discusión en español de PostgreSQL, es el relacionado con el problema que representa la inserción de fechas en las tablas que tienen campos (columnas) con ese tipo de dato, así que consideré útil escribir un pequeño artículo acerca del manejo de las fechas al usar PostgreSQL.
Vamos a empezar por recordar que hay varios tipos de datos relacionados con la fecha y la hora: timestamp que sirve para guardar fechas y horas (antes datetime), interval, para almacenar el tiempo transcurrido entre dos momentos (antes timespan), date y time, además de una variante para dos de ellas.
En PostgreSQL existe un formato por omisión para la captura de las fechas: el estilo ISO-8601 (año-mes-día), diferente al que acostumbramos en los paises hispano-americanos (día-mes-año). Debido a que los diversos formatos de captura que maneja Postgres cambian el orden en que deben entrar el mes y la fecha, es muy importante que al codificar un script o un programa definamos explícitamente la manera en que deseamos capturar esta información. Es aconsejable que la interfase con el usuario muestre el formato que esperamos y que además validemos ese valor antes de enviarlo a nuestra base de datos.
Es importante saber que Postgres permite definir tanto el formato con que deseamos capturar los datos, como el formato con el cual deseamos que salga esa información, es decir tenemos un formato de entrada y un formato de salida.
Le recomendamos que revise la página A Summary of the International Standard Date and Time Notation para conocer con detalle la norma ISO-8601.

Formato de salida de fechas y horas
Formato de salida de fechas y horas
La manera en que Postgres forma la salida de las fechas se puede modificar por medio se la sentencia SET DateStyle TO 'estilo_deseado';
En donde estilo_deseado puede tomar los valores: ISO , SQL , Postgres y German , de acuerdo a la siguiente tabla:
Tabla 1. Estilos de entrada de fechas y horas
Especificación del estilo Descripción Ejemplo
'ISO' Norma ISO-8601 1997-12-17 07:37:16-08
'SQL' Estilo tradicional 12/17/1997 07:37:16.00 PST
'Postgres' Estilo original Wed Dec 17 07:37:16 1997 PST
'German' Estilo regional 17.12.1997 07:37:16.00 PST
Los diversos estilos tienen las variantes European y NonEuropean o US. El primer mundo se olvida de que el Sur también existe.
A continuación aparece un script desarrollado con Perl y DBI, en donde se muestra como se modifica el formato de salida al cambiar el estilo de la fecha. La estructura de la tabla utilizada es la siguiete:
                                      Table "persona"
      Attribute      |   Type    |                         Modifier                         
---------------------+-----------+----------------------------------------------------------
 id_persona          | integer   | not null default nextval('persona_id_persona_seq'::text)
 id_titulo           | integer   | 
 nombre              | text      | 
 apel_pat            | text      | 
 apel_mat            | text      | 
 salario             | money     | 
 fecha_contrato      | date      | 
 fecha_actualizacion | timestamp | 
Index: persona_id_persona_key
Observe que el campo fecha_actualizacion es de tipo timestamp y que en la sentencia SQL del script estamos recuperando la fecha y la hora, y, por medio de la función date(), solamente la fecha, para visualizar claramente el efecto del cambio de formato.
#!/usr/bin/perl -w

# La vida es simple con DBI
use DBI;

# Base de datos de prueba
$dbname = 'pruebas';

# Conexión a la base de datos
$dbh = DBI -> connect("dbi:Pg:dbname=$dbname");

# Más vale...
defined ($dbh) or die "No pude conectar a la base de datos.\n";

# Arreglo de estilos de salida para postgresqldocsql 7.x
@estilos_salida = qw(Postgres ISO SQL German);

# Fijamos el estilo de entrada, aunque no es usado en este script.
$dbh->do("SET DATESTYLE TO 'European'");

# Nuestra sentencia SQL nos trae la fecha de actualización de los datos
$sql = "SELECT fecha_actualizacion, date(fecha_actualizacion) FROM persona WHERE id_persona = 1;";

print "-=" x 35, "\n";
print "Estilo          Fecha/Hora                Fecha\n";
print "-=" x 35, "\n";

# Visitamos cada estilo y hacemos la consulta
foreach $estilo (@estilos_salida) {
	$dbh->do("SET DATESTYLE TO '$estilo'");
	$sth=$dbh->prepare($sql);
	$sth->execute;
	while ( @row = $sth->fetchrow_array ) {
		print "$estilo:\t\t$row[0]\t\t$row[1]\n";
	}
}
La salida debe ser algo como:
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Estilo          Fecha/Hora                		Fecha
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Postgres:	Tue 01 Aug 11:43:10 2000 CDT		01-08-2000
ISO:		2000-08-01 11:43:10-05			2000-08-01
SQL:		01/08/2000 11:43:10.00 CDT		01/08/2000
German:		01.08.2000 11:43:10.00 CDT		01.08.2000
Es notable la diferencia en que se despliegan las fechas en el estilo Postgres, en un caso muestra el nombre abreviado del día y del mes, pero al usar la función date() el formato es numérico. Esto es de primordial importancia cuando se requiere analizar sintácticamente la salida de este tipo de datos, ya sea para desplegarla o validarla.
Anterior Inicio Siguiente
Manejo de estilos de fechas en PostgreSQL Formato de entrada de fechas y horas
Formato de entrada de fechas y horas
Manejo de estilos de fechas en PostgreSQL
Anterior Siguiente
Formato de entrada de fechas y horas
La entrada de fechas y horas (date y time) se puede hacer de varias maneras, ya sea usando el estilo ISO-8601 o el tradicional de postgresqldocs. Es muy importante que el usuario o programador de postgres use explicitamente un estilo definido para la captura de las fechas y horas, pues de esta manera evita la ambigüedad que se genera al suponer que el postmaster recibirá cualquier cosa que le enviemos.
El formato de entrada para fechas y horas se especifica con el comando: SET DateStyle TO 'estilo_deseado';
Puesto que los diversos formatos son ambiguos respecto al orden de el día y la fecha, es que existen las opciones European y NonEuropean. Obviamente el estilo European es que se conforma con la manera en que lo usamos en losa paises hispano-americanos, así que es el con seguridad debemos fijar en nuestros programas de captura.
Los estilos ISO , SQL , Postgres y German no modifican el orden del mes y el día, solamente cambian el separador entre los valores numéricos de la fecha y, en caso del estilo Postgres, las posiciones de las cadenas de nombre del día, nombre del mes y número del día.
El script siguiente muestra la manera en que es interpretada una fecha ambigua en función del estilo de facha establecido. Nótese que la fecha '1/8/2000 11:43:10' puede significar 'primero de agosto' u 'ocho de enero'.
#!/usr/bin/perl -w

# La vida es simple con DBI
use DBI;

# Base de datos de prueba
$dbname = 'pruebas';

# Conexión a la base de datos
$dbh = DBI -> connect("dbi:Pg:dbname=$dbname");

# Más vale...
defined ($dbh) or die "No pude conectar a la base de datos.\n";

# Fijamos el estilo de salida a 'Postgres'
$dbh->do("SET DATESTYLE TO 'Postgres'");

# Arreglo de estilos de entrada para postgresql 7.x
@estilos_entrada= qw(Postgres ISO SQL German);
@estilos_salida = qw(NonEuropean European);

# Escogemos una fecha ambigua, puede ser 1 de agosto u 8 de enero!!!
$fecha_entrada = '1/8/2000 11:43:10';

foreach $estilo_1 (@estilos_entrada) { 
	$dbh->do("SET DATESTYLE TO '$estilo_1'");
	print "\n                      Estilo 1: $estilo_1\n";
	# Visitamos cada estilo y hacemos la consulta
	foreach $estilo_2 (@estilos_salida) {
		# Nuestra sentencia SQL modifica fecha de contratación de una persona
		$sql = "UPDATE persona set fecha_actualizacion = '$fecha_entrada' WHERE id_persona = 1;";
		$dbh->do("SET DATESTYLE TO '$estilo_2'");
		$sth=$dbh->prepare($sql);
		$sth->execute;
		print "Estilo 2: $estilo_2\nEntrada: $fecha_entrada, ";
	
		# Consultamos el registro para observar como se modificó 
		$sql= "SELECT fecha_actualizacion FROM persona WHERE id_persona = 1;";
		$sth=$dbh->prepare($sql);
   	 $sth->execute;
		while ( @row = $sth->fetchrow_array ) {
			print " Salida: $row[0]\n";
		}
	}
}
El resultado de la ejecución del script es el siguiente. Observe que usando el estilo Postgres es más facil visualizar que la cadena de entrada fue interpretada de diferente manera en función del Estilo 2; en cambio, con el estilo SQL permanece la duda acerca de la manera en que entró la fecha, aunque recordando que este estilo se modifica en la salida según sea europeo o no, es claro que en el primer caso la fecha es 'enero 8 de 2000' y en el segundo 'agosto 1 de 2000', como lo esperábamos. Note que la salida de los estilos ISO y German son insensibles: siempre serán 'aaaa.mm.dd' y 'dd.mm.aaaa', respectivamente.
                      Estilo 1: Postgres
Estilo 2: NonEuropean
Entrada: 1/8/2000 11:43:10,  Salida: Sat Jan 08 11:43:10 2000 CST
Estilo 2: European
Entrada: 1/8/2000 11:43:10,  Salida: Tue 01 Aug 11:43:10 2000 CDT

                      Estilo 1: ISO
Estilo 2: NonEuropean
Entrada: 1/8/2000 11:43:10,  Salida: 2000-01-08 11:43:10-06
Estilo 2: European
Entrada: 1/8/2000 11:43:10,  Salida: 2000-08-01 11:43:10-05

                      Estilo 1: SQL
Estilo 2: NonEuropean
Entrada: 1/8/2000 11:43:10,  Salida: 01/08/2000 11:43:10.00 CST
Estilo 2: European
Entrada: 1/8/2000 11:43:10,  Salida: 01/08/2000 11:43:10.00 CDT

                      Estilo 1: German
Estilo 2: NonEuropean
Entrada: 1/8/2000 11:43:10,  Salida: 08.01.2000 11:43:10.00 CST
Estilo 2: European
Entrada: 1/8/2000 11:43:10,  Salida: 01.08.2000 11:43:10.00 CDT
Anterior Inicio Siguiente
Formato de salida de fechas y horas Interpretación de Fechas
Interpretación de Fechas
Manejo de estilos de fechas en PostgreSQL
Anterior
Interpretación de Fechas
Finalmente, vamos a analizar la manera en que Postgres interpreta la entrada de diversas fechas. De acuerdo con el Appendix UG1. Date/Time Support, de la versión 7.0.2:
3. Si el token es un número o un campo numérico.
a. Si hay más de 4 dígitos y si no ha sido leído previamente otros campos de fecha, entonces se interpreta como una "fecha concatenada" (es decir 19990118). 8 y 6 dígitos se interpretan como año, mes y día, mientras que 7 y 5 dígitos se interpretan como año, día del año, respectivamente.
b. Si el token es de tres dígitos y el año ya ha sido decodificado, entonces se interpreta como día del año.
c. Si hay cuatro o más dígitos, se interpreta como un año.

d. Si se trata de un modo de fecha europeo (European), y el campo del día no ha sido léido, y si el valor es menor o igual a 31, entonces se interpreta como el día.

e. Si el campo del mes no ha sido leído, y si el valor es menor o igual a 12, entonces se interpreta como un mes.

f. Si el campo del día no ha sido leído, y si el valor es menor o igual a 31, entonces se interpreta como día.

g. Si hay dos dígitos o cuatro o más dígitos, se interpreta como año.

h. De otra manera, devuelve un error.
En nuestra prueba trataremos de introducir cuatro fechas: '2000-12-31','2000-31-12','31-12-2000'y '12-31-2000'. Las cuatro pretenden ser el 31 de diciembre de 2000, aunque escritas de diferentes maneras. Usaremos el formato de salida Postgres y los dos formatos de entrada que ofrece nuestra base de datos: NonEuropean y European, por medio del siguiente script con Perl:
 

#!/usr/bin/perl -w
# Manejo de fechas ambiguas.
# DBI: La neta para acceso a bases de datos con perl.
use DBI;

# Base de datos de prueba
$dbname = 'pruebas';

# Conexión a la base de datos
# Evitamos que DBI imprima el error en STDOUT
$dbh = DBI -> connect("dbi:Pg(PrintError=>0):dbname=$dbname");

defined ($dbh) or die "No pude conectar a la base de datos.\n";

# Fijamos el estilo de salida a 'Postgres'
$dbh->do("SET DATESTYLE TO 'Postgres'");
@estilos = qw(NonEuropean European);

# Arreglo de fechas de prueba
@fechas = ("2000-12-31","2000-31-12","31-12-2000","12-31-2000");

foreach $estilo (@estilos) {
	$dbh->do("SET DATESTYLE TO '$estilo'");
	print "\n                             $estilo\n\n";
	foreach $fecha(@fechas) {
		$sql = "UPDATE persona set fecha_actualizacion = ? WHERE id_persona = 1;";
		$sth=$dbh->prepare($sql);
		# Nuestra sentencia SQL modifica fecha de contratación de una persona
		$sth->execute($fecha);
		$rc = $sth->err;
		# Primera parte del mensaje: La entrada ...
		unless ($rc) {$msg="Entrada: $fecha, "} else {$msg = "Error>>> $fecha, "};
		# Consultamos el registro para observar como se modificó 
		$sql= "SELECT fecha_actualizacion FROM persona WHERE id_persona = 1;";
		$sth=$dbh->prepare($sql);
		$sth->execute;
		while ( @row = $sth->fetchrow_array ) {
			#... y la salida.
			print " $msg  Salida: $row[0]\n";
		}
	}
}
El cual genera la siguiente salida al ejecutarse:
 
                             
							 NonEuropean

 Entrada: 2000-12-31,   Salida: Sun Dec 31 00:00:00 2000 CST
 Entrada: 2000-31-12,   Salida: Sun Dec 31 00:00:00 2000 CST
 Entrada: 31-12-2000,   Salida: Sun Dec 31 00:00:00 2000 CST
 Entrada: 12-31-2000,   Salida: Sun Dec 31 00:00:00 2000 CST

                             European

 Entrada: 2000-12-31,   Salida: Sun 31 Dec 00:00:00 2000 CST
 Entrada: 2000-31-12,   Salida: Sun 31 Dec 00:00:00 2000 CST
 Entrada: 31-12-2000,   Salida: Sun 31 Dec 00:00:00 2000 CST
 Error>>> 12-31-2000,   Salida: Sun 31 Dec 00:00:00 2000 CST
Podemos observar comportamientos diferentes en función del formato de entrada. Con el estilo NonEuropean no existe ningún problema: las fechas son bien interpretadas en los cuatro casos, como se puede ver en la salida. Sin embargo, usando el formato European la fecha '12-31-2000' no puede ser interpretada y se considera un error. La causa del error se puede encontrar en las reglas anotadas más arriba: La regla 3.d menciona que si el modo es European y el campo del día no ha sido leído y el valor es menor o igual a 31, o sea nuestro caso, entonces ese valor se interpreta como ¡el día!, así que Postgres nos regresa un error al detectar que no hay un mes '31'.

Podemos concluir que Postgres hará todo cuanto sea posible por interpretar de la mejor manera la entrada de la fecha, pero las reglas que tiene no son infalibles. así que no debemos descuidarnos en ese flanco tomando, al menos, estas precauciones:
Indique de manera explícita el formato con el que espera que se capturen las fechas; ya sea como parte de la etiqueta qye acompaña al campo de captura, o en las salida que genere el parámetro de ayuda, algo como:
>mi_script --ayuda

Esta es la ayuda de mi_script:

mi_script espera como parámetro una fecha con el formato 'dd/mm/aaaa', por ejemplo: '21/12/2001'.
Valide la entrada de la fecha. Puede hacerlo analizando sintacticamente cada uno de loe elementos de la fecha o uando algún módulo de Perl, por ejemplo Date-Calc.
Planee cuidadosamente los formatos de entrada y salida que definirá con la sentencia SET DateStyle TO 'estilo_deseado'.
Para termina solamente me resta comentarle que las siglas CST y CDT que acompañan a algunos estilos, son producto de las zonas temporales, es decir de manera relativa al Greenwich Mean Time (GMT). Puede ver los obscuros detalles en la documentación de PostgreSQL: http://www.postgresql.org/docs/postgres/datetime-appendix.htm#AEN28108.
Roberto Andrade Fonseca

[email protected]
Anterior Inicio
Formato de entrada de fechas y horas