The::Beastieux

Sangre de Bestia + Corazón de Pingüino | Un blog acerca de Linux, *BSD, Open Source, Software Libre, Programación …

PostgreSQL: Fechas y Horas


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'); --&gt; Rpta: 1500000 (MICROSEGUNDO)
SELECT EXTRACT(MILLISECONDS FROM TIMESTAMP '2009-11-06 17:05:01.5'); --&gt; Rpta: 1500 (MILISEGUNDO)
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2009-11-06 17:05:01'); --&gt; Rpta: 1257545101 (SEGUNDOS DESDE 1970-01-01 00:00:00-00)
SELECT EXTRACT(DOW FROM TIMESTAMP '2009-11-06 17:05:01'); --&gt; Rpta: 5 (DIA DE LA SEMANA(0-6)(0=Domingo))
SELECT EXTRACT(DOY FROM TIMESTAMP '2009-11-06 17:05:01'); --&gt; Rpta: 310 (DIA DEL AÑO(1 - 365/366))

–5) DATE_PART

SELECT date_part('YEAR', TIMESTAMP '2009-11-06 17:05:01'); --&gt; Rpta: 2009 (AÑO)
SELECT date_part('MONTH', TIMESTAMP '2009-11-06 17:05:01'); --&gt; Rpta: 11 (MES(1-12))
SELECT date_part('DAY', TIMESTAMP '2009-11-06 17:05:01'); --&gt; Rpta: 6 (DIA)
SELECT date_part('HOUR', TIMESTAMP '2009-11-06 17:05:01'); --&gt; Rpta: 17 (HORA(0 - 23))
SELECT date_part('MINUTE', TIMESTAMP '2009-11-06 17:05:01'); --&gt; Rpta: 5 (MINUTO)
SELECT date_part('SECOND', TIMESTAMP '2009-11-06 17:05:01'); --&gt; Rpta: 1 (SEGUNDO)

–6) DATE TRUNC

SELECT date_trunc('YEAR', TIMESTAMP '2009-11-06 17:05:01'); --&gt; Rpta: "2009-01-01 00:00:00" (AÑO)
SELECT date_trunc('MONTH', TIMESTAMP '2009-11-06 17:05:01'); --&gt; Rpta: "2009-11-01 00:00:00" (MES(1-12))
SELECT date_trunc('DAY', TIMESTAMP '2009-11-06 17:05:01'); --&gt; Rpta: "2009-11-06 00:00:00" (DIA)
SELECT date_trunc('HOUR', TIMESTAMP '2009-11-06 17:05:01'); --&gt; Rpta: "2009-11-06 17:00:00" (HORA(0 - 23))
SELECT date_trunc('MINUTE', TIMESTAMP '2009-11-06 17:05:01'); --&gt; Rpta: "2009-11-06 17:05:00" (MINUTO)
SELECT date_trunc('SECOND', TIMESTAMP '2009-11-06 17:05:01'); --&gt; Rpta: "2009-11-06 17:05:01" (SEGUNDO)

–7) INTERVAL

SELECT EXTRACT(YEAR FROM INTERVAL '2009 years 24 months'); --&gt; Rpta: 2011
SELECT EXTRACT(MONTH FROM INTERVAL '2009 years 25 months'); --&gt; Rpta: 1 (MES(0 - 11))
SELECT date_part('HOUR', INTERVAL '5 hours 3 minutes'); --&gt; Rpta: 5
SELECT date_trunc('HOUR', INTERVAL '5 hours 3 minutes'); --&gt; Rpta: "05:00:00"

–8) OPERACIONES CON FECHAS

SELECT date '2009-11-06 17:05:01' + integer '10'; --&gt; Rpta: "2009-11-16 17:05:01"
SELECT date '2009-11-06 17:05:01' + interval '1 hour'; --&gt; Rpta: "2009-11-06 18:05:01"
SELECT interval '24 hours'; --&gt; Rpta: "24:00:00"
SELECT interval '12 hours 5 minutes 10 seconds'; --&gt; Rpta: "12:05:10"
SELECT time '01:00' + interval '3 hours'; --&gt; Rpta: "04:00:00"
SELECT timestamp '2009-11-06 17:05:01' - timestamp '2009-10-06 17:05:01'; --&gt; Rpta: "31 days"
SELECT time '2009-11-06 17:05:01' - time '2009-11-06 16:05:01'; --&gt; 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

TOPIC:MILLENNIUM, CENTURY, DECADE, YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MICROSECONDS, MILLISECONDS, EPOCH, DOW, DOY
About these ads

20 Respuestas a “PostgreSQL: Fechas y Horas

  1. Anónimo noviembre 27, 2013 en 11:41 am

    Hola amigos muy bueno el post, pero como hago para hacer una resta de fechas o de horas, como en el caso de un control de llegadas y salidas, quiero saber cuanto tiempo de retraso tengo cuando un empleado llega tarde al trabajo o cuando sale despues de finalizada la jornada de trabajo

  2. German Morales enero 6, 2013 en 5:22 pm

    Hola buenos dias, saludos y gracias por el blog, es de ayuda! Mira tengo una funcion, lo que deseo es saber el dia de la semana a partir de una fecha pasada por paramentro, si es sabado, restar dos dias a la fecha y agregarla a una nueva variable de tipo fecha para trabajar con ella dentro de la funcion. Esto es lo que tengo:
    …. soy nuevo en esto de postgres…..!!!!!

    CREATE OR REPLACE FUNCTION fncDias(dtFecha date)
    RETURNS date AS

    $BODY$

    declare

    dtFechaAnterior date;
    pFecha date;

    BEGIN

    select (dtFechaDeReporte) :: date into pFecha;

    select (
    case when to_char(pFecha ,’d’) = ‘2’ then –DIA LUNES

    –restar fecha dos días hacia atrás a partir del lunes
    select (pFecha – integer (2)) :: date into dtFechaAnterior;
    return dtFechaAnterior;

    end

    else

    case when to_char(pFecha , ‘ d ‘ ) = ‘7’ then –DIA SABADO

    –restar fecha dos días hacia atrás a partir del lunes
    select (pFecha – integer (2)) :: date into dtFechaAnterior;
    return dtFechaAnterior;

    /*
    –restar fecha dos días hacia atrás a partir del sábado
    –return pfecha – integer ”2”;
    dtFechaAnterior:= pfecha – integer ”2”;
    return dtFechaAnterior;
    */

    end

    )

    END;

    $BODY$
    LANGUAGE plpgsql

  3. Alex V septiembre 6, 2012 en 1:26 pm

    Hola, está muy bueno el post, queria preguntar si es posible ver el numero del día respecto del año, es decir si fuera ‘2012-02-01′, verlo como el día 32 del año.

    Saludos y muchas gracias

  4. Jonathan mayo 29, 2012 en 11:43 am

    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….

    • BeAsTiEuX mayo 29, 2012 en 12:23 pm

      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!

  5. jorge abril 3, 2012 en 9:15 am

    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

    • BeAsTiEuX abril 3, 2012 en 3:27 pm

      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

  6. D3N febrero 15, 2012 en 3:21 pm

    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.

    • BeAsTiEuX febrero 16, 2012 en 1:45 am

      Hola, como estas, apenas vi tu comentario disculpa la demora

      mira, 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.

      • D3N febrero 16, 2012 en 10:07 am

        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!!!

  7. David Montalva octubre 22, 2011 en 11:41 am

    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!!

    • BeAsTiEuX octubre 22, 2011 en 12:43 pm

      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"

  8. alvaro marzo 2, 2011 en 10:19 am

    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

  9. Pingback:PostgreSQL: Base de Datos « The::Beastieux

  10. juan jaimes febrero 28, 2010 en 6:50 pm

    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

  11. sAfOrAs febrero 28, 2010 en 12:42 pm

    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!!

  12. juan jaimes febrero 28, 2010 en 11:23 am

    nuevamnte no estan los sql pero creo que en este los puedo copiar pegar, eso espero sino por favor los podrias subir, mil gracias

  13. Anónimo noviembre 27, 2009 en 1:24 pm

    Interesante tu post..pero te recumiendo q juegues con herramientas mas serias como Oracle o DB2.

    • Anónimo diciembre 7, 2011 en 9:49 am

      puta que eres weon

  14. migovilo noviembre 14, 2009 en 11:10 pm

    Excelente aporte :D

No te quedes callado. Pregunta, Comenta, Comparte, Sígueme, Suscríbete, Déjame tu opinión... Soy Beastieux Zeroo...

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Seguir

Recibe cada nueva publicación en tu buzón de correo electrónico.

Únete a otros 532 seguidores

%d personas les gusta esto: