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'); --> 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

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

22 thoughts on “PostgreSQL: Fechas y Horas

  1. Hola, sera que alguien me podria ayudar a crear una funcion en postgre que me capture la fecha actual y la fecha de caducidad ingresada para que me permita calcular ¿ Cuantos dias faltan para alcanzar la fecha de caducidad, desde la fecha actual ? y que se ejecute al consultar, registrar o modificar

  2. My final results: I’ve missing six lbs and i am in week 4 (perfectly meant to become almost finished with week four; didn’t start out the 7 days off w/ a fantastic night’s rest, so I have been tousled for that last five times). In any case, I started off 20lbs more than the common botanical slimming soft gels for my age, gender and peak and my overall body fat proportion was 26-27%. I’d weigh myself every day first thing in the morning and the scale would transfer up and down too. I did my research and realized which i had put my system into hunger manner. I enhanced my calories to 1780, keeping the macronutrient ratio, and the botanical slimming soft gels and inches commenced to return off. Even on the days I don’t work out, I however eliminate inches and don’t get botanical slimming soft gels. The program works, we just really need to adhere to it towards the T (no pun supposed).

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

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

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

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

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

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

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

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

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

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

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

    1. 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"

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

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

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

Deja un comentario