PostgreSQL - manejo de fechas

   
Vista:
Imágen de perfil de outrera

manejo de fechas

Publicado por outrera (35 intervenciones) el 01/04/2014 19:15:27
fuente: http://saforas.wordpress.com/2009/11/12/postgresql-fechas-y-horas/


Publicado por BeAsTiEuX en noviembre 12, 2009


El manejo de fechas suele ser útil, sobretodo cuando se tiene configurado zonas horarias diferentes en el servidor de base de datos, 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"
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
1
Responder