A Tabela 6-18 mostra as funções disponíveis para o processamento do valor da data e da hora. Os detalhes são mostrados nas próximas subseções. A Tabela 6-17 ilustra o comportamento dos operadores aritméticos básicos (+, *, etc.). Para as funções de formatação consulte a Seção 6.7. Deve-se estar familiarizado com os os tipos de dado para data e hora (veja a Seção 5.5).
Todas as funções e operadores descritos abaixo, que recebem os tipos time ou timestamp como entrada, estão presentes em duas formas: uma que recebe time ou timestamp com zona horária, e outra que recebe time ou timestamp sem zona horária. Para abreviar, estas formas não são mostradas em separado.
Tabela 6-17. Operadores de data e hora
Nome | Exemplo | Resultado |
---|---|---|
+ | timestamp '2001-09-28 01:00' + interval '23 hours' | timestamp '2001-09-29 00:00' |
+ | date '2001-09-28' + interval '1 hour' | timestamp '2001-09-28 01:00' |
+ | time '01:00' + interval '3 hours' | time '04:00' |
- | timestamp '2001-09-28 23:00' - interval '23 hours' | timestamp '2001-09-28' |
- | date '2001-09-28' - interval '1 hour' | timestamp '2001-09-27 23:00' |
- | time '05:00' - interval '2 hours' | time '03:00' |
- | interval '2 hours' - time '05:00' | time '03:00:00' |
* | interval '1 hour' * int '3' | interval '03:00' |
/ | interval '1 hour' / int '3' | interval '00:20' |
Tabela 6-18. Funções de data e hora
Nome | Tipo retornado | Descrição | Exemplo | Resultado |
---|---|---|---|---|
age (timestamp) | interval | Subtrai de hoje | age(timestamp '1957-06-13') | 43 years 8 mons 3 days |
age (timestamp, timestamp) | interval | Subtrai os argumentos | age('2001-04-10', timestamp '1957-06-13') | 43 years 9 mons 27 days |
current_date | date | Data de hoje; veja Seção 6.8.4 | ||
current_time | time with time zone | Hora do dia; veja Seção 6.8.4 | ||
current_timestamp | timestamp with time zone | Data e hora; veja Seção 6.8.4 | ||
date_part (text, timestamp) | double precision | Obter subcampo (equivale ao
extract ); veja também abaixo
| date_part('hour', timestamp '2001-02-16 20:38:40') | 20 |
date_part (text, interval) | double precision | Obter subcampo (equivale ao
extract ); veja também abaixo
| date_part('month', interval '2 years 3 months') | 3 |
date_trunc (text, timestamp) | timestamp | Truncar na precisão especificada; veja também Seção 6.8.2 | date_trunc('hour', timestamp '2001-02-16 20:38:40') | 2001-02-16 20:00:00+00 |
extract (field from
timestamp) | double precision | Obter subcampo; veja também Seção 6.8.1 | extract(hour from timestamp '2001-02-16 20:38:40') | 20 |
extract (field from
interval) | double precision | Obter subcampo; veja também Seção 6.8.1 | extract(month from interval '2 years 3 months') | 3 |
isfinite (timestamp) | boolean | Testar carimbo de hora finito (nem inválido nem infinito) | isfinite(timestamp '2001-02-16 21:28:30') | true |
isfinite (interval) | boolean | Testar intervalo finito | isfinite(interval '4 hours') | true |
localtime | time | Hora do dia; veja Seção 6.8.4 | ||
localtimestamp | timestamp | Data e hora; veja Seção 6.8.4 | ||
now () | timestamp with time zone | Data e hora corrente (equivale ao
current_timestamp ); veja Seção 6.8.4
| ||
timeofday() | text | Data e hora corrente; veja Seção 6.8.4 | timeofday() | Wed Feb 21 17:01:13.000126 2001 EST |
EXTRACT
, date_part
EXTRACT (campo FROM fonte)
A função extract
obtém subcampos dos valores
de data e hora, como o ano ou a hora.
A fonte é uma expressão de valor
avaliada aos tipos timestamp ou interval.
(As expressões do tipo date e time são
transformadas em timestamp, possibilitando utilizá-las da
mesma forma). O campo é um identificador,
ou uma cadeia de caracteres, que seleciona qual campo será extraído do valor fonte.
A função extract
retorna valores do tipo
double precision.
Abaixo são mostrados valores válidos:
O campo do ano dividido por 100
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40'); Resultado: 20
Observe que o resultado para o campo século é simplesmente o campo ano dividido por 100, e não a definição habitual que coloca a maior parte dos anos de 1900 no século vinte.
O campo do dia (do mês) (1 - 31)
SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40'); Resultado: 16
O campo do ano dividido por 10
SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40'); Resultado: 200
O dia da semana (0 - 6; Domingo é 0) (para os valores de timestamp apenas)
SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40'); Resultado: 5
O dia do ano (1 - 365/366) (para os valores de timestamp apenas)
SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40'); Resultado: 47
Para os valores de date e timestamp, o número de segundos desde 1970-01-01 00:00:00-00 (pode ser negativo); para valores de interval, o número total de segundos do intervalo
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40'); Resultado: 982352320 SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours'); Resultado: 442800
O campo das horas (0 - 23)
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40'); Resultado: 20
O campo dos segundos, incluindo a parte fracionária, multiplicado por 1 000 000. Observe que inclui todos os segundos.
SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5'); Resultado: 28500000
O ano dividido por 1 000
SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40'); Resultado: 2
Observe que o resultado para o campo milênio é simplesmente o campo ano dividido por 1000, e não a definição habitual que coloca a maior parte dos anos de 1900 no segundo milênio.
O campo de segundos, incluindo a parte fracionária, multiplicado por 1000. Observe que inclui todos os segundos.
SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5'); Resultado: 28500
O campo dos minutos (0 - 59)
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40'); Resultado: 38
Para os valores timestamp, o número do mês do ano (1 - 12); para valores de interval o número de meses, módulo 12 (0 - 11)
SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40'); Resultado: 2 SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months'); Resultado: 3 SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months'); Resultado: 1
O trimestre do ano (1 - 4) onde o dia se encontra (para os valores de timestamp apenas)
SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40'); Resultado: 1
O campo dos segundos, incluindo a parte fracionária (0 - 59[1])
SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40'); Resultado: 40 SELECT EXTRACT(SECOND FROM TIME '17:12:28.5'); Resultado: 28.5
O componente hora do deslocamento da zona horária
O componente minuto do deslocamento da zona horária
No valor de timestamp, calcula o número da semana do ano onde o dia se encontra. Por definição (ISO 8601), a primeira semana do ano contém o dia 4 de janeiro deste ano (A semana ISO começa na segunda-feira). Em outras palavras, a primeira quinta-feira está na primeira semana do ano.
SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40'); Resultado: 7
O campo do ano
SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40'); Resultado: 2001
A função extract
é voltada principalmente para o
processamento computacional. Para formatar valores de data e hora para
exibição, veja a Seção 6.7.
A função date_part
está modelada sobre o
equivalente no Ingres à função
extract
do padrão SQL:
date_part('campo', fonte)
Observe que neste caso o parâmetro campo precisa ser um
uma cadeia de caracteres, e não um nome. Os valores válidos para
campo em date_part
são os mesmos da função extract
.
SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40'); Resultado: 16 SELECT date_part('hour', INTERVAL '4 hours 3 minutes'); Resultado: 4
date_trunc
A função date_trunc
é conceitualmente
similar à função trunc
para números.
date_trunc('campo', fonte)
fonte é uma expressão de valor do tipo timestamp (valores do tipo date e time são transformados automaticamente). campo seleciona a precisão a ser utilizada para truncar o valor do carimbo de tempo. O valor retornado é do tipo timestamp, com todos os campos inferiores ao valor selecionado tornados zero (ou um, para o dia do mês).
Os valores válidos para campo são:
microseconds |
milliseconds |
second |
minute |
hour |
day |
month |
year |
decade |
century |
millennium |
Exemplos:
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40'); Resultado: 2001-02-16 20:00:00+00 SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40'); Resultado: 2001-01-01 00:00:00+00
AT TIME ZONE
A construção AT TIME ZONE
permite a conversão do
carimbo de tempo para uma zona horária diferente.
Tabela 6-19. Variantes de AT TIME ZONE
Expressão | Retorna | Descrição |
---|---|---|
timestamp without time zone AT TIME ZONE zone | timestamp with time zone | Converte hora local de uma determinada zona horária para UTC |
timestamp with time zone AT TIME ZONE zone | timestamp without time zone | Converte de UTC para a hora local em uma determinada zona horária |
time with time zone AT TIME ZONE zona | time with time zone | Converte hora local entre zonas horárias |
Nestas expressões, a zona horária desejada pode ser especificada tanto por meio de um texto em uma cadeia de caracteres (por exemplo, 'PST') quanto por um intervalo (por exemplo, INTERVAL '-08:00').
Exemplos (suponha que a TimeZone seja PST8PDT):
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST'; Resultado: 2001-02-16 19:38:40-08 SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST'; Resultado: 2001-02-16 18:38:40
O primeiro exemplo aceita um carimbo de tempo sem zona horária e o interpreta como hora MST (GMT-7) para produzir um carimbo de tempo UTC, o qual é então rotacionado para PST (GMT-8) para ser exibido. O segundo exemplo aceita um carimbo de tempo especificado em EST (GMT-5) e converte para hora local MST (GMT-7).
A função timezone
(zona,
carimbo_de_tempo) equivale à construção em conformidade
com o padrão SQL carimbo_de_tempo AT TIME ZONE
zona.
As seguintes funções estão disponíveis para obtenção da data e hora corrente:
CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_TIME ( precisão ) CURRENT_TIMESTAMP ( precisão ) LOCALTIME LOCALTIMESTAMP LOCALTIME ( precisão ) LOCALTIMESTAMP ( precisão )
CURRENT_TIME
e
CURRENT_TIMESTAMP
fornecem valores com zona horária;
LOCALTIME
e
LOCALTIMESTAMP
fornecem valores sem zona horária.
CURRENT_TIME
,
CURRENT_TIMESTAMP
,
LOCALTIME
e
LOCALTIMESTAMP
podem, opcionalmente, receber um
parâmetro com a precisão fazendo o resultado ser arredondado
nesta quantidade de dígitos fracionários. Sem o parâmetro de precisão,
o resultado é fornecido com toda a precisão disponível.
Nota: Antes do PostgreSQL 7.2, os parâmetros de precisão não estavam implementados, e o resultado era sempre fornecido em segundos inteiros.
Alguns exemplos:
SELECT CURRENT_TIME; 14:39:53.662522-05 SELECT CURRENT_DATE; 2001-12-23 SELECT CURRENT_TIMESTAMP; 2001-12-23 14:39:53.662522-05 SELECT CURRENT_TIMESTAMP(2); 2001-12-23 14:39:53.66-05 SELECT LOCALTIMESTAMP; 2001-12-23 14:39:53.662522
A função now()
é o equivalente tradicional do
PostgreSQL para
CURRENT_TIMESTAMP
.
Também existe a função timeofday()
, que por motivos históricos
retorna uma cadeia de caracteres e não um valor do tipo timestamp:
SELECT timeofday(); Sat Feb 17 19:07:32.000126 2001 EST
É importante perceber que
CURRENT_TIMESTAMP
e as funções relacionadas retornam
o tempo do começo da transação corrente; seus valores não mudam durante a
transação. A função timeofday()
retorna a hora corrente, avançando durante as transações.
Nota: Muitos outros sistemas de banco de dados avançam estes valores mais freqüentemente.
Todos os tipos de dado para data e hora também aceitam o valor literal especial now para especificar a data e hora corrente. Portanto, os três comandos abaixo retornam o mesmo resultado:
SELECT CURRENT_TIMESTAMP; SELECT now(); SELECT TIMESTAMP 'now';
Nota: Não será desejado utilizar a terceira forma ao especificar a cláusula DEFAULT na criação da tabela. O sistema converte now em timestamp tão logo a constante é analisada e, portanto, quando o valor padrão for usado a hora da criação da tabela será utilizada! As duas primeiras formas não são processadas até que o valor padrão seja utilizado, porque são chamadas de funções. Assim sendo, as duas primeiras formas fornecem o comportamento desejado quando o padrão for a hora de inserção da linha.
[1] | 60 se os segundos saltados (leap) são implementados pelo sistema operacional |