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'); --> 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"
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'); --> 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"
Valora esta pregunta
1