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.
