Sangre de Bestia + Corazón de Pingüino | Un blog acerca de Linux, *BSD, Open Source, Software Libre, Programación …
Este es el Post #10 del Topic: Guía Práctica de PostgreSQL
El manejo de fechas suele ser útil, sobretodo cuando se tiene configurado zonas horarias diferentes en el servidor de base de datos, el el sistema operativo, o incluso en la aplicación cliente.
Aquí les muestro alguna de las formas mas usadas para mostrar, convertir o incluso realizar operaciones entre fechas. Apuesto a que con solo comparar las sentencias con las respuestas se darán cuenta rápidamente de lo que hace cada uno, por ello creo que no sea necesario tanta explicación.
–1) FECHA ACTUAL
SELECT CURRENT_DATE; --> Rpta: "2009-11-06" SELECT CURRENT_TIME; --> Rpta: "04:51:26.0625-05" SELECT CURRENT_TIMESTAMP; --> Rpta: "2009-11-06 04:51:56.015625-05" SELECT CURRENT_TIME (1); --> Rpta: "04:52:11.70-05" ( precision ) SELECT CURRENT_TIMESTAMP (2); --> Rpta: "2009-11-06 04:52:35.66-05" ( precision ) SELECT LOCALTIME; --> Rpta: "04:52:54.890625" SELECT LOCALTIMESTAMP; --> Rpta: "2009-11-06 04:53:04.640625" SELECT LOCALTIME (2); --> Rpta: "04:53:11.92" ( precision ) SELECT LOCALTIMESTAMP (2); --> Rpta: "2009-11-06 04:53:24.03" ( precision ) SELECT now(); --> Rpta: "2009-11-06 04:53:30.515625-05" SELECT timeofday(); --> Rpta: "Fri Nov 06 04:53:34.921875 2009 COT" SELECT TIMESTAMP 'now'; --> Rpta: "2009-11-06 04:53:40.203125" SELECT TIME 'now'; --> Rpta: "04:53:51.703125"
–2) WITHOUT/WITH TIME ZONE
SELECT TIMESTAMP WITHOUT TIME ZONE 'now'; --> Rpta: "2009-11-06 04:55:05.84375" SELECT TIMESTAMP WITH TIME ZONE 'now'; --> Rpta: "2009-11-06 04:55:13.9375-05"
–3) AT TIME ZONE: CONVERSIÓN A DIFERENTES ZONAS HORARIAS
SELECT TIMESTAMP WITH TIME ZONE '2009-11-06 17:05:01' AT TIME ZONE 'UTC-6'; --> Rpta: "2009-11-07 04:05:01" SELECT TIMESTAMP WITH TIME ZONE '2009-11-06 17:05:01' AT TIME ZONE 'UTC-7'; --> Rpta: "2009-11-07 05:05:01" SELECT TIMESTAMP WITH TIME ZONE '2009-11-06 17:05:01' AT TIME ZONE 'UTC-8'; --> Rpta: "2009-11-07 06:05:01" SELECT TIMESTAMP WITH TIME ZONE '2009-11-06 17:05:01' AT TIME ZONE 'MST'; --> Rpta: "2009-11-06 15:05:01" SELECT TIMESTAMP WITH TIME ZONE '2009-11-06 17:05:01' AT TIME ZONE 'PST'; --> Rpta: "2009-11-06 14:05:01"
–4) EXTRACT
SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: 3 (MILENIO) SELECT EXTRACT(CENTURY FROM TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: 21 (SIGLO) SELECT EXTRACT(DECADE FROM TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: 200 (DÉCADA) SELECT EXTRACT(YEAR FROM TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: 2009 (AÑO) SELECT EXTRACT(QUARTER FROM TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: 4 (TRIMESTRE(1-4)) SELECT EXTRACT(MONTH FROM TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: 11 (MES(1-12)) SELECT EXTRACT(WEEK FROM TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: 45 (NUM. SEMANA) SELECT EXTRACT(DAY FROM TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: 6 (DIA) SELECT EXTRACT(HOUR FROM TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: 17 (HORA(0 - 23)) SELECT EXTRACT(MINUTE FROM TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: 5 (MINUTO) SELECT EXTRACT(SECOND FROM TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: 1 (SEGUNDO) SELECT EXTRACT(MICROSECONDS FROM TIMESTAMP '2009-11-06 17:05:01<strong>.</strong>5'); --> Rpta: 1500000 (MICROSEGUNDO) SELECT EXTRACT(MILLISECONDS FROM TIMESTAMP '2009-11-06 17:05:01.5'); --> Rpta: 1500 (MILISEGUNDO) SELECT EXTRACT(EPOCH FROM TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: 1257545101 (SEGUNDOS DESDE 1970-01-01 00:00:00-00) SELECT EXTRACT(DOW FROM TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: 5 (DIA DE LA SEMANA(0-6)(0=Domingo)) SELECT EXTRACT(DOY FROM TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: 310 (DIA DEL AÑO(1 - 365/366))
–5) DATE_PART
SELECT date_part('YEAR', TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: 2009 (AÑO)
SELECT date_part('MONTH', TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: 11 (MES(1-12))
SELECT date_part('DAY', TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: 6 (DIA)
SELECT date_part('HOUR', TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: 17 (HORA(0 - 23))
SELECT date_part('MINUTE', TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: 5 (MINUTO)
SELECT date_part('SECOND', TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: 1 (SEGUNDO)
–6) DATE TRUNC
SELECT date_trunc('YEAR', TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: "2009-01-01 00:00:00" (AÑO)
SELECT date_trunc('MONTH', TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: "2009-11-01 00:00:00" (MES(1-12))
SELECT date_trunc('DAY', TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: "2009-11-06 00:00:00" (DIA)
SELECT date_trunc('HOUR', TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: "2009-11-06 17:00:00" (HORA(0 - 23))
SELECT date_trunc('MINUTE', TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: "2009-11-06 17:05:00" (MINUTO)
SELECT date_trunc('SECOND', TIMESTAMP '2009-11-06 17:05:01'); --> Rpta: "2009-11-06 17:05:01" (SEGUNDO)
–7) INTERVAL
SELECT EXTRACT(YEAR FROM INTERVAL '2009 years 24 months'); --> Rpta: 2011
SELECT EXTRACT(MONTH FROM INTERVAL '2009 years 25 months'); --> Rpta: 1 (MES(0 - 11))
SELECT date_part('HOUR', INTERVAL '5 hours 3 minutes'); --> Rpta: 5
SELECT date_trunc('HOUR', INTERVAL '5 hours 3 minutes'); --> Rpta: "05:00:00"
–8) OPERACIONES CON FECHAS
SELECT date '2009-11-06 17:05:01' + integer '10'; --> Rpta: "2009-11-16 17:05:01" SELECT date '2009-11-06 17:05:01' + interval '1 hour'; --> Rpta: "2009-11-06 18:05:01" SELECT interval '24 hours'; --> Rpta: "24:00:00" SELECT interval '12 hours 5 minutes 10 seconds'; --> Rpta: "12:05:10" SELECT time '01:00' + interval '3 hours'; --> Rpta: "04:00:00" SELECT timestamp '2009-11-06 17:05:01' - timestamp '2009-10-06 17:05:01'; --> Rpta: "31 days" SELECT time '2009-11-06 17:05:01' - time '2009-11-06 16:05:01'; --> Rpta: "01:00:00"
CONCLUSIONES:
–CURRENT_TIME y CURRENT_TIMESTAMP: Entregan valores con TIME ZONE.
–LOCALTIME y LOCALTIMESTAMP: Entregan values sin TIME ZONE.
–now() es similar a CURRENT_TIMESTAMP
Menéame Delicious
PC-BSD Blog
oye compa tengo una duda con respecto a tus aportaciones estan chidisimas… no se si podras ayudarme a aguardar las fechas existen en la PC al hacer un registro en la base de datos..
no se si me entiendas…. LO QUE QUIERO ES TOMAR LA FECHA Y LA HORA QUE CONTIENE LA MAQUINA Y AGUARDARLA EN LA BASE DE DATOS EN EL MOMENTO DE QUE SE REALIZE EL REGISTRO….
Hola jonathan, eso depende del tipo de datos que tengas que registrar, generalmente se usa un tipo date para almacenar solo fecha o un timestamp para la fecha y hora, el cual es el mas recomendable desde mi punto de vista.
Un ejemplo de una tabla son sus respectivos tipos de datos:
CREATE TABLE test
(
id integer,
fecha date,
fecha_hora timestamp without time zone
)
Para registrar la fecha y hora actual sería suficiente con la función now()
INSERT INTO test (id, fecha, fecha_hora) VALUES (’1′,now(),now())
obtendrás un registro similar a este:
1 | “2012-05-29″ | “2012-05-29 12:17:26.000000″
como verás, la función now() es válida tanto para insertar en un campo date y timestamp
Saludos!
Buen dia
quiero obtener la fecha de vencimiento a partir de un campo de fecha y un campo de dias plazo pero al parecer las fechas solo operan con etiquetas se puede???
myfecha date
myvencimiento integer
select myfecha + myvencimiento from documento –nofunciona
select myfecha + interval myvencimiento from documento –nofunciona
select myfecha + cast(myvencimiento date) from documento –nofunciona
select myfecha + myvencimiento days from documento –nofunciona
select myfecha + myvencimiento * ’1days’ from documento –nofunciona
parece q no puedo involucrar otro campo en la suma
Hola jorge.
Con la primera forma debería funcionar correctamente
select myfecha + myvencimiento from documento
fíjate que todos las filas de tu columna myfecha estén llenas, porque es posible que el error se de por esa razón.
también el tipo exacto de tu campo myfecha (date) posiblemente es un timestamp,
–podrías pegar el resultado que te muestra el log error para analizarlo.
Saludos
Una consulta MASTER, quisiera fijar una fecha 2011-02-03 y concatenarla a la hora actual como podria hacerlo porfavor amigo o algun alma caritativa pueda darme una mano.
Hola, como estas, apenas vi tu comentario disculpa la demoramira, eso depende de que quieres hacer, hay muchas formas:
SELECT '2011-02-03' || ' ' || LOCALTIME --"2011-02-03 01:14:20.480927"
SELECT '2011-02-03' || ' ' || LOCALTIME (0) --"2011-02-03 01:12:34"
SELECT '2011-02-03' || ' ' || LOCALTIME (2) --"2011-02-03 01:13:03.32"
SELECT '2011-02-03' || ' ' || CURRENT_TIME(0) --"2011-02-03 01:06:54.357665-05"
SELECT '2011-02-03' || ' ' || CURRENT_TIME(2) --"2011-02-03 01:06:54.357665-05"
ahora si quieres devolverlo todo como una tipo de datos especial, como datetime, timestamp, etc, tendrias que hacer un cast, porque lo anterior te devuelve un tipo TEXT
ahora mismo solo se me ocurre un pequeño artilugio que debería funcionar
/*vemos como sumando un interval de 0 horas se convierte en timestamp sin cambiar los datos*/
SELECT date '2011-02-03' + interval '0 hours';
SELECT LOCALTIME (0) + interval '0 hours';
/*entonces ambos lo sumamos, tu fecha, mas la hora actual y ya no resulta un simple TEXT, si no un TIMESTAMP*/
SELECT date '2011-02-03' + interval '0 hours' + LOCALTIME (0) + interval '0 hours';
También puedes tratar jugando con:
SELECT timeofday();
SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now';
Saludos, y gracias por leerme, a pesar de que mi blog está paralizado ya por mucho tiempo.
BeAsTiEuX sos grande papa !!!, gracias a tu ayuda pude resolver mi conflicto lógico. Te agradezco un monton y sigue adelante te deseo muchos éxitos!!!
UNA CONSULTA MASTER!!!
Tengo una bd con un campo Timestamp with time zone y necesito hacer una consulta donde pregunte las registros dentro dde un periodo de tiempo.
por ejemplo :
saber que registro hay en la tabla desde el 01/10/2011 al 01/11/2011
Saludos!!
Has probado haciendo una simple comparación?
SELECT col1, col2,… FROM tu_tabla
WHERE fecha_inicial > ’2011-10-01 00:00:00′ AND fecha_final < '2011-11-01 00:00:00'
o incluso con un simple:
SELECT * FROM tabla WHERE fecha1>’01-10-2011′ AND fecha2<'01-11-2011'
si no, puede que sea necesario que conviertas tu fecha a comparar a un time zone, que tiene el formato siguiente:
SELECT TIMESTAMP WITH TIME ZONE 'now'; Rpta: "2009-11-06 04:55:13.9375-05"
Muy Buen Post Amigo una ayudai quiero saber el intervalo de la semana?? porque estoy haciendo un contador de visita y me estan pidiendo los datos que estan abajo ya teng las 3 primera pero me falta saber como saco con postgresql el resto
Hoy:
Ayer:
Antes de Ayer:
Esta Semana:
Ultima Semana:
Hace 2 Semanas:
Este Mes:
Ultimo Mes:
Hace 2 Meses:
Te agradeceria la ayuda que me puedas dar
Pingback: PostgreSQL: Base de Datos « The::Beastieux
gracias y espero me ayuden todo esto de postgresql ya que tengo tiempo de querer aprenderlo, y los cursos estan caros para mi bolsillo no me alcanza, muchas felicidades por su blog, me interesaria saber todo lo que se pueda de postgresql para poder montar mapas en internet osea aprender de postgis, relacionarlo con php, postgresql, postgis y mapserver o la tecnologia libre sobre esto LES AGRADESCO MUCHO TOO ESTO Y EN LO QUE ME PUEDAN AYUDAR, por ultimo, me gustaria realizar un grupo de postgresql aqui en mexico, ya que hasta donde he buscado no lo hay o son de lucro, apoyenme, mil gracias
Con respecto a este post no es necesario un script, podrias hacer copy & paste a cada linea de codigo mostrado para de esa manera saber cual es el funcionamiento de cada uno…
Saludos Juan, me da mucho gusto que existe gente que cada vez se va interesando mas en postgres!!
nuevamnte no estan los sql pero creo que en este los puedo copiar pegar, eso espero sino por favor los podrias subir, mil gracias
Interesante tu post..pero te recumiendo q juegues con herramientas mas serias como Oracle o DB2.
puta que eres weon
Excelente aporte :D