PostgreSQL: Fechas y Horas


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

Libros de PostgreSQL
Para que ste blog siga creciendo:


Visitenos en:

Instagram