Formato de la fecha en PostgreSQL

Pip · #1 (**permalink**) 09/03/2007, 11:26

Hola.

Supongo que más de uno pensará que este es un posible tema trillado (porque es una de las primeras cosas que saltan a la vista cuando empiezas a usar la base de datos) pero no he encontrado nada al respecto buscando en el foro por la palabra "fecha".

Resulta que cambiar el formato de la fecha para introducir los datos con los INSERTs sé como se hace (en el fichero postgresql.conf hay que cambiar la línea que está alrededor de la 407 y donde pone "datestyle = 'iso, dmy'" y, como se puede observar, he puesto el día primero, mes después y año al final). Sin embargo, al hacer un SELECT, sigue mostrándome primero el año. ¿Alguien sabe dónde se cambia esto?

cacr · #2 (**permalink**) 09/03/2007, 16:59

Hola...

Sistema operativo?

para hacer el select, estás usando psql? phppgadmin? pgadmin3? pgacces? python? c? perl? php?

Pendientes

Pip · #3 (**permalink**) 09/03/2007, 17:58

Perdón, sinceramente, por no especificar esos datos.

Sistema Operativo: Windows XP
Método de manipulación de la base de datos: pgAdminIII (cliente que viene con el instalador .msi de postgreSQL). Lo estoy usando porque, aunque finalmente usaré php para conectar a la base de datos ella, estoy probando el buen funcionamiento de la ella y en esta fase introduzco los datos con esta utilidad, que es bastante más cómodo que con php.

De todas maneras no sé si cambiará el resultado del select cuando recupere los datos de la fecha en php, imagino que la base de datos me los devuelve siempre en el mismo formato. ¿No?

cacr · #4 (**permalink**) 12/03/2007, 11:20

El servidor de BD te lo va a devolver siempre de la misma forma, pero, una cosa es lo que le devuelve, y otro quién te lo muestra... es decir, el cliente también tiene una codificación...

Pip · #5 (**permalink**) 12/03/2007, 16:29

Hola cacr.

Estoy perdido. Como te dije, las fechas las ingreso (desde pgAdminIII) con el formato tradicional español, es decir: dd-mm-yyyy. Pero los select realizados desde ese propio cliente y desde php me muestran el formato cambiado.

Si dices que cada cliente tiene una codificación, ¿Quieres decirme que puedo configurar en el php.ini alguna opción para ello? Php.ini dispone de ";date.timezone = " pero, no sé qué valores se le puede asignar (no encuentro información por google) y creo que eso se referiría a fechas calculadas por php,no fechas recibidas por petición a una base de datos.

Espero que puedas darme un poco de luz sobre esta duda.

cacr · #6 (**permalink**) 13/03/2007, 06:42

Hola...

Cita:

Iniciado por Pip

...las fechas las ingreso (desde pgAdminIII) con el formato tradicional español, es decir: dd-mm-yyyy.

¿serías capaz de copiar un setencia de insert y otro de selec aquí, con los respectivos resultados?

Cita:

Iniciado por Pip

Si dices que cada cliente tiene una codificación, ¿Quieres decirme que puedo configurar en el php.ini

No siempre es buena idea cambiar los patrones de configuración, para PHP, revisa -> http://www.php.net/manual/es/ref.datetime.php

Para pgadmin3 no recuerdo como es, pero con phppgadmin, él toma la configuración de los locales (linux) para mostrar los resultados...

Pendientes

Pip · #7 (**permalink**) 13/03/2007, 07:38

Lo pondré tal y como lo he hecho por pasos. Tengo esta tabla:

Código PHP:

  CREATE TABLE BANEADOS (
    COD_BAN SERIAL PRIMARY KEY,
    IP TEXT UNIQUE,
    AVISOS INTEGER,
    FALLO DATE,
    BANEADO BOOL
);

Está vacía y hago este INSERT:

Cita:

insert into baneados (ip,avisos,fallo,baneado) values('80.56.23.89',1,'10-03-2007','f');

Que devuelve como mensaje del servidor:

Cita:

La consulta se ejecutó con éxito: 1 filas efectadas, en 10 ms.

Hago un SELECT de todo lo que hay en la tabla:

Cita:

select * from baneados;

Y devuelve:

He puesto una imagen, espero que dure, al menos, algunos días para que se pueda ver, por si no se viera, lo transcribo a texto así:

Código PHP:

  cod ban            ip                    avisos                 fallo                 baneado
integer             text               integer              date                boolean
25                   80.56.23.89     1                     2007-03-10      f

Cita:

No siempre es buena idea cambiar los patrones de configuración, para PHP, revisa -> http://www.php.net/manual/es/ref.datetime.php

Pese a eso, si cambio el php.ini, la fecha seguirá mostrándose igual porque, a fin de cuentas, php lo tratará como una cadena de texto, ¿No? Es decir, que pienso que sigue siendo responsabilidad del cliente. Aunque es una suposición.

cacr · #8 (**permalink**) 13/03/2007, 07:57

ok... dices que la fecha, en el archivo de configuración de postgres, la pusiste? reiniciaste el servidor después del hacer el cambio?

Esa imágen, corresponde a qué cliente?

Imagino que estás haciendo una aplicación en elagún lenguaje, PHP?, entonces, revisaste el enlace que te pasé?

Por lo demás, yo veo tu consulta está correcta, o ya me perdí... jeje

Pip · #9 (**permalink**) 13/03/2007, 08:16

Hola cacr.

La imagen corresponde a pgAdmin III. En el archivo de configuración postgreSQL postgresql.conf cambié la línea "datestyle = 'iso, dmy'" y la dejé tal y como la he puesto. De esta manera como te digo, los INSERT los acepta con el formato nuestro dd-mm-yyyy.

Uso php pero sigo creyendo que el enlace que me pasaste es para el tratamiento de fechas que genera php y no para las que recibe de una base de datos que, como te dije (aunque seguramente esté equivocado), creo que tratará como simples cadenas de texto.

calvin_naranjo · #10 (**permalink**) 13/03/2007, 20:13

Hola,

Perdon por entrometerme. Debes de estar desarrollando, puedes usar las funciones para formato de fechas que provee el postgres.

Pip · #11 (**permalink**) 14/03/2007, 02:28

Nadie se entromete calvin_naranjo, para eso está el post.

Respecto a tu propuesta, no sé si te refieres a usar funciones como to_date (porque no sé si en PostgreSQL hay alguna otra función en concreto concreto para el formato de fechas). En Oracle sé bien cómo usarla, en postgreSQL no estoy tan seguro porque si se me ocurre hacer:

Código PHP:

  select to_date(fallo,'DD MM YYYY') from baneados;

Donde fallo es una fecha, si haciendo:

Código PHP:

  select fallo from baneados;

Me devuelve esta fecha:

Código PHP:

  2007-03-10

Con la función to_date tal y como la he puesto arriba me retorna:

Código PHP:

  0010-01-03

Es decir, me deforma totalmente la fecha o, al menos, así lo entiendo yo. Y aunque me devolviera el valor deseado, sigo pensando que tiene que existir alguna manera de establecer eso de manera predeterminada. Espero que si alguien lo sabe lo comente.

cacr · #12 (**permalink**) 14/03/2007, 08:35

Has probado con phppgadmin?

o desde el psql?

calvin_naranjo · #13 (**permalink**) 14/03/2007, 13:51

Hola

Mira lo que yo hago es usar to_char y le agrego un formato, tanto en lo que desarrollo en JAVA como en PHP.

Ejemplo:
echo "select cedula,personal.nombre, to_char(fecha_entrada,'YYYY-MM-DD - HH24:MI:SS') as fecha1 ,to_char(fecha_salida,'YYYY-MM-DD - HH24:MI:SS') as fecha2 ,to_char(fecha_salida-fecha_entrada,'HH24:MI:SS') as labor from turnos,usuarios_turnos,personal, registro_entrada_salida where turnos.codigo = usuarios_turnos.turno and usuarios_turnos.empleado = personal.codigo and registro_entrada_salida.usuario = personal.codigo and personal.cedula = '$cedula' and (fecha_entrada >= '$fechaini') and (fecha_entrada <= '$fechafin') order by registro_entrada_salida.control;";

Este para PHP.

Pip · #14 (**permalink**) 14/03/2007, 16:40

Hola de nuevo.

El problema de "recomponer" la fecha a su formato original o, quizás mejor dicho, al formato que nosotros queremos, no es un problema. En las faq de php indican como hacerlo, es tan sencillo como esto:

Código PHP:

   $var = explode('-',$variabledefecha);
echo "$var[2]-$var[1]-$var[0]";

El problema sigue siendo configurar postgreSQL para que lo haga por defecto. No tengo instalado phppgadmin cacr. Pero si lo tuviera seguramente me devolvería la fecha también alrevés (como me lo hace en php y en el cliente que viene con postgreSQL).

Gracias por responder a los dos. A ver si alguien sabe como configurarlo por defecto.

cacr · #15 (**permalink**) 15/03/2007, 06:35

Pip... creo que no nos estamos entendiendo...

Puedes colocar la líena de referencia, del archivo de configuración acá?

cacr · #16 (**permalink**) 15/03/2007, 06:46

De paso...

http://www.postgresql.org/docs/7.3/i...e/sql-set.html

Pip · #17 (**permalink**) 15/03/2007, 07:08

Hola cacr.

Voy a pegar aquí la línea. Archivo de configuración postgresql.conf (la primera de las líneas que pego es la número 405 y la de datastyle la 407):

Código PHP:

  405  # - Locale and Formatting -
406  
407  datestyle = iso, 'dmy'
408  timezone = GMT            # actually, defaults to TZ 
409                      # environment setting

He visto el enlace que me has puesto. La idea es que me hubiera servido para lo que quería (aunque no sé si sigo entendiéndolo mal) porque ahí te dice lo valores que debe tomar datestyle para que la fecha vaya por salida como yo quiero. Al menos eso he interpretado de esta parte (que se refiere a datastyle) de la web que me has linkado:

Código PHP:

  European           Use dd/mm/yyyy for numeric date representations.

¿Se trataría de cambiar el iso que tengo por European y el formato de salida de la fecha sería el que pone (es decir: dd/mm/yyyy)?

cacr · #18 (**permalink**) 15/03/2007, 07:15

no estoy seguro de que European sea el que te solucione el asunto, pero, uno de esos es... prueba

calvin_naranjo · #19 (**permalink**) 15/03/2007, 14:19

hola CACR, esta modificación tambien afectaria el pgadmin? o este tiene su propio formato y estilo?

Pip · #20 (**permalink**) 15/03/2007, 15:41

Probé con European y me daba problemas al arrancar el servidor de postgreSQL. Lo extraño es que poniendo las líneas como estaban antes, tampoco arranca. Tendré que reinstalarlo o probar a copiar el .conf de postgreSQL que tengo en el portatil.

¿Qué te hacía pensar que no funcionaría con European y quizás con otro sí cacr?

cacr · #21 (**permalink**) 15/03/2007, 19:01

Cita:

Iniciado por calvin_naranjo

hola CACR, esta modificación tambien afectaria el pgadmin? o este tiene su propio formato y estilo?

Ni idea... no recuerdo una sóla vez que haya tenido que modificar el formato de las fechas, en el archivo de configuración... esas cosas las resuelvo con las funciones de "alguien más"...

Y de paso, casi nunca uso pgadmin3... me muevo entre psql y phppgadmin

cacr · #22 (**permalink**) 15/03/2007, 19:03

Cita:

Iniciado por Pip

Probé con European y me daba problemas al arrancar el servidor de postgreSQL. Lo extraño es que poniendo las líneas como estaban antes, tampoco arranca. Tendré que reinstalarlo o probar a copiar el .conf de postgreSQL que tengo en el portatil.

Qué te dicen los logs? ers más fácil mirarlos y resolver que desinstalar y volver a isntalar... al menos la mayoría de las veces....

Cita:

Iniciado por Pip

¿Qué te hacía pensar que no funcionaría con European y quizás con otro sí cacr?

La documentación... creo, ahorita no lo recuerdo (y estoy en horas de no trabajar), que European es para formatear la entrada... creo...

Pip · #23 (**permalink**) 16/03/2007, 02:51

Hola de nuevo cacr.

Respondiéndote a:

Cita:

La documentación... creo, ahorita no lo recuerdo (y estoy en horas de no trabajar), que European es para formatear la entrada... creo...

Código PHP:

         The following two options determine both a substyle of the        "SQL" and "PostgreSQL" output formats        and the preferred interpretation of ambiguous date input.         
European           Use dd/mm/yyyy for numeric date representations.

De ahí creo entender que es para el formato de salida. No obtante, quizás lo traduzca mal, ya que mi nivel de inglés no es como para tirar cohetes.

Cita:

Qué te dicen los logs? ers más fácil mirarlos y resolver que desinstalar y volver a isntalar... al menos la mayoría de las veces....

Seguramente tienes razón, he mirado el log y no sé en qué momento empezó a actuar como ahora, parándose su iniciación cuando trato de iniciarlo. De todas maneras este es un problema menor que solucionaré sin daros la lata.

lriosv · #24 (**permalink**) 30/05/2008, 17:53

Hola soy nuevo en el foro, creo tener la solucion que Pip busca, pero aunque sea una monada mi apoyo ahi te sugiero que cambies el encode de tu base de datos creo el uft8 o un iso??? no recuerdo o usa el latin1.
Ahora estoy creando funciones que me devuelva el mes de una fecha, eso es lo malo de las funciones de posgres son escasas tienes que crearlas tu mismo, yo hice una que me rellena con ceros a la izquierda y a la derecha, cosas asi
ojala te sirva y se acabo con mi funcion te lo paso para ver si le sacas provecho a tu problema.

lriosv · #25 (**permalink**) 31/05/2008, 10:20

amigo Pip como te prometi aca te paso las dos funciones, en mi caso tengo postgres 8.0.0 mi encoding es LATIN1, cuando inserto fechas me inserta en este formato mm/dd/yyyy la funcion 2 te lo pasa a dd/mm/yyyy:

1. te devuelve el mes en mayusculas

CREATE OR REPLACE FUNCTION "public"."Mes" (fecha_actual date) RETURNS text AS
$body$
/* New function body */
DECLARE
str_nro VARCHAR;
cadena TEXT;
BEGIN
SELECT TO_CHAR(fecha_actual,'MM') INTO str_nro;
IF (str_nro = '01') THEN
cadena = 'ENERO';
ELSIF(str_nro = '02')THEN
cadena = 'FEBRERO';
ELSIF(str_nro = '03')THEN
cadena = 'MARZO';
ELSIF(str_nro = '04')THEN
cadena = 'ABRIL';
ELSIF(str_nro = '05')THEN
cadena = 'MAYO';
ELSIF(str_nro = '06')THEN
cadena = 'JUNIO';
ELSIF(str_nro = '07')THEN
cadena = 'JULIO';
ELSIF(str_nro = '08')THEN
cadena = 'AGOSTO';
ELSIF(str_nro = '09')THEN
cadena = 'SETIEMBRE';
ELSIF(str_nro = '10')THEN
cadena = 'OCTUBRE';
ELSIF(str_nro = '11')THEN
cadena = 'NOVIEMBRE';
ELSIF(str_nro = '12')THEN
cadena = 'DICIEMBRE';
ELSE
RAISE EXCEPTION 'NO EXISTE EL MES INIDICADO';
END IF;
RETURN cadena;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

-----------------------------------------------------------------------------------------------------------------------
2. esta es creo la solucion a tu problema:

CREATE OR REPLACE FUNCTION "public"."formatear_fecha" (fecha date) RETURNS date AS
$body$
/* New function body */
DECLARE
str_fecha TEXT;
fecha_actual DATE;
BEGIN
SELECT TO_CHAR(fecha,'MM/DD/YYYY') INTO str_fecha;
SELECT TO_DATE(str_fecha,'MM/DD/YYYY') INTO fecha_actual;
RETURN fecha_actual;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

bueno amigo ahi lo tienes, mas bien una consulta como ya salio la version 8.3.0 que es estable y dicen que tiene muchas mejoras y hablan bien de esa version como sabes que tengo la 8.0.0 no tendras un manual o docuentacion español q me pases o un link te lo agradecere por q si es asi debo saber como migrar la data a esa version sin perjudicar a los usuario finales
saludos

seyko · #26 (**permalink**) 02/06/2008, 02:57

uffff que manera de complicarse la vida amigo!

seyko · #27 (**permalink**) 02/06/2008, 02:58

Para el manual mira en la web de postgres...
http://www.postgresql.org/docs/

Orantz · #28 (**permalink**) 18/11/2009, 03:24

Un poco tarde pero bueno... Yo me he encontrado con un problema similar y lo he solucionado:

Tenia una fecha 20/09/2009 guardada en mi base de datos como un text y necesitaba hacer una comparacion con la fecha actual (CURRENT_DATE) de la siguiente manera

SELECT ecs.descripcion, count(es.id), es.codigo
FROM ma_exportacion_codigos_sensibilidades ecs inner
join ma_exportacion_sensibilidades es on ecs.codigo=es.codigo
inner join ma_exportacion_reconocimientos er on er.dni=es.dni
WHERE empresa='9814' and es.fecha_fin > CURRENT_DATE
GROUP BY ecs.descripcion, es.codigo;

Pero no funciona bien porque como es.fecha_fin es un text no me lo compara bien con CURRENT_DATE... Tras mucho probar..
Con DATE(es.fecha_fin) > CURRENT_DATE me funciona en el pgAdmin pero no en mi aplicacion web porque tienen disitintos estilos de fecha, lo mismo ocurre con CAST(es.fecha_fin as DATE).

Con to_chart no me ha ido ni en pgadmin

Al final he puesto esto to_date(es.fecha_fin,'DD-MM-YYYY') > to_date(CURRENT_DATE,'DD-MM-YYYY')

Así controlo que mi fecha tipo texto sea DD-MM-YYYY y qe CURRENT_DATE tambien siga este estilo de fecha y puedo compararlas sin problemas.

Por lo menos a mi me ha funcionado. Espero q ha alguien más le funcione

xdrtas · #29 (**permalink**) 18/11/2009, 06:04

Hola Pip, si copiaste y pegaste este texto exacto de tu conf, te digo que tiene un pequeño error.

Código PHP:

  405  # - Locale and Formatting - 
406   
407  datestyle = iso, 'dmy' 
408  timezone = GMT            # actually, defaults to TZ  
409                      # environment setting

Esta línea datestyle = iso, 'dmy' tiene que ir así datestyle = 'iso, dmy', con la comilla simple detras de la palabra "iso".

Usando ISO no te va a funcionar con el formato que tu buscas. Te muestro el ejemplo con la herramienta psql y con la siguiente configuración datestyle = 'iso, dmy':

Código:

aikido=# select * from "Album" where "alb_ID" > 20 order by "alb_ID" desc;
 alb_ID | alb_Titulo | alb_Fecha  | alb_Descripcion
--------+------------+------------+-----------------
     26 | Desde Web  | 2009-11-18 | Desde la web.
     25 | Prueba     | 2009-11-18 | Descripcion
     24 | Prueba     | 2009-11-18 | Descripcion
     23 | Prueba     | 2009-11-18 | Descripcion
     22 | Prueba     | 2009-11-18 | Descripcion
     21 | Titulo     | 2009-11-17 | Prueba bash

Ahora cambié la línea así: datestyle = 'SQL, DMY' y mira el resultado que me dá:

Código:

aikido=# select * from "Album" where "alb_ID" > 20 order by "alb_ID" desc;
 alb_ID | alb_Titulo | alb_Fecha  | alb_Descripcion
--------+------------+------------+-----------------
     26 | Desde Web  | 18/11/2009 | Desde la web.
     25 | Prueba     | 18/11/2009 | Descripcion
     24 | Prueba     | 18/11/2009 | Descripcion
     23 | Prueba     | 18/11/2009 | Descripcion
     22 | Prueba     | 18/11/2009 | Descripcion
     21 | Titulo     | 17/11/2009 | Prueba bash

Como puedes observar, se ha cambiado el formato, te dejo la tabla de comparación que trae la documentación.

ISO ISO 8601/SQL standard --> 1997-12-17 07:37:16-08
SQL traditional style --> 12/17/1997 07:37:16.00 PST
POSTGRES original style --> Wed Dec 17 07:37:16 1997 PST
German regional style --> 17.12.1997 07:37:16.00 PST

Desde PHP me funciona correctamente a la hora de guardar la fecha, sin usar funciones de conversión. Desde el pgAdmin III no he logrado que me devuelva la fecha en el formato que quiero, pero eso ya es problema de configuración de la herramienta pgadmin. Internamente me está guardando la fecha en el formato que quiero, que es DD/MM/YYYY. Si consigo configurar el output de la fecha en el pgadmin III les aviso.

Te pongo un ejemplo de insert sobre la tabla "Album" para que veas que no uso ninguna función especial y luego el select para que veas el resultado:

Código sql:

Ver originalINSERT INTO "Album" VALUES (NEXTVAL('"Album_alb_ID_seq"'), 'titulo', '18/11/2009', 'Descripcion');
SELECT * FROM "Album" WHERE "alb_ID" > 20 ORDER BY "alb_ID" DESC;
 
 alb_ID | alb_Titulo | alb_Fecha  | alb_Descripcion
--------+------------+------------+-----------------
     27 | titulo     | 18/11/2009 | Descripcion
     26 | Desde Web  | 18/11/2009 | Desde la web.
     25 | Prueba     | 18/11/2009 | Descripcion
     24 | Prueba     | 18/11/2009 | Descripcion
     23 | Prueba     | 18/11/2009 | Descripcion
     22 | Prueba     | 18/11/2009 | Descripcion
     21 | Titulo     | 17/11/2009 | Prueba bash

Espero que te sirva, recuerda para el formato DD/MM/YYYY usa datestyle = 'SQL, DMY', saludos.