PostgreSQL: Principais funções
Série Tudo sobre Postgres: Principais funções do Postgres (trim, upper, current_date, age, etc.)
Esse é um artigo de uma super série que estou escrevendo sobre o Postgres. Nesse artigo, vou listar todos as principais funções do Postgres, para que servem e como utilizar com exemplos práticos.
Eu separei as funções por tipo para facilitar a consulta.
📖 Índice
Funções de Data
Como não podia ser, trabalhar com datas e horários é sempre um desafio para os programadores, independente da linguagem ou banco da dados. Me fala aqui quem nunca teve um problema sequer com timezone, hehehe.
Função AGE
O objetivo da função age é retornar a diferença entre duas datas mas em um formato de ano, mês e dia. Por exemplo, a diferença entre uma determinada data e outra é 1 ano, 2 meses e 3 dias.
Embora na maioria dos projetos esse calculo seja feito por parte da aplicação, é possível também fazer no Postgres.
Sintaxe da função AGE
age([data1],data2)
Onde a função calcula a date1 - data2 . Quando não é informado um valor data a date1 a operação é feita com a datehora_atual - data2 .
Exemplos da função AGE
/* Exemplo informando apenas data2 onde a data atual é 26/08/2023 */
SELECT age(timestamp '2022-07-25')
/* output */
1 year 1 mon 1 day
/* Exemplo informando data1 e data2 */
SELECT age(timestamp '2022-02-02', timestamp '2021-01-01');
/* output */
1 year 1 mon 1 day
/* Exemplo informando data1 como sendo a data atual e data2 */
SELECT age(current_date, timestamp '2021-01-01');
/* output */
2 years 7 mons 26 daysFunção CURRENT_DATE
O objetivo da função current_date é, obviamente retornar a data atual. Não tem muito o que dizer.
Sintaxe da função CURRENT_DATE
current_date
Simples assim, utilize a função e o retorno será a data atual do banco de dados.
Exemplos da função CURRENT_DATE
/* Exemplo básico de uso */
SELECT current_date
/* output */
2023-08-27Exemplos de operações com a função CURRENT_DATE
/* Exemplo básico de uso */
SELECT current_date
/* output */
2023-08-27Função CURRENT_TIME
O objetivo da função current_time é, obviamente retornar a hora atual com o timezone.
Sintaxe da função CURRENT_TIME
current_time( [precisão] )
Simples assim, utilize a função e o retorno será a hora com timezone atual do banco de dados.
O parâmetro de [precisão] é opcional e serve para definir o número de dígitos dos segundos a ser retornado.
Exemplos da função CURRENT_TIME
/* Exemplo básico de uso */
SELECT current_time
/* output */
05:35:13.961907-03:00
/* Exemplo current_time retornar apenas HH:MM:SS e timezone */
select current_time(0)
/* output */
05:35:13-03:00Função CURRENT_TIMESTAMP
O objetivo da função current_timestamp é retornar um timestamp (car com o timezone. O timestamp é retornado com data e hora.
Sintaxe da função CURRENT_TIMESTAMP
current_timestamp( [precisão] )
Simples assim, utilize a função e o retorno será data/hora com timezone atual do banco de dados.
O parâmetro de [precisão] é opcional e serve para definir o número de dígitos dos segundos a ser retornado.
Exemplos da função CURRENT_TIMESTAMP
/* Exemplo básico de uso */
SELECT current_timestamp
/* output */
2023-12-25 05:39:35.730554-03
/* Exemplo current_timestamp retornar apenas AAAA-MM-DD HH:MM:SS e timezone */
select current_timestamp(0)
/* output */
2023-12-25 05:39:35-03Função DATE_PART
O objetivo da função date_part é extrair partes de uma data/hora (datetime) do Postgres, como por exemplo, o mês, o dia da semana, o epoch, etc.
Essa função é semelhante a função extract, as únicas diferenças estão na sintaxe, que NÃO possui um "from" entre o que você quer extrair e o valor a ser analisado. E o tipo do retorno, que retorna float8 ao invés de numérico. Confira o nosso artigo explicando todas as diferenças: https://200ok.com.br/qual-e-a-diferenca-entre-date_part-e-extract-no-postgres/
Sintaxe da função DATE_PART
date_part( 'unidade', data )
A função date_part possui dois parâmetros obrigatórios.
O parâmetro de 'unidade' é o que define o que você quer extrair da data, seja o dia, o dia do mês, dia da semana, trimestre, segundo, timezone, epoch, etc. Abaixo deixei uma tabela completa com todas as partes possíveis de extrair de um datetime.
Já o parâmetro 'data' é a data, ou data/hora (datetime) ou hora que queremos trabalhar. Você sempre precisa informar o tipo do valor, se é date, time, timestamp e interval e sempre utilizar o padrão do Postgres.
Exemplos da função DATE_PART
/* Exemplo básico de uso extraindo o dia do ano de uma data */
SELECT date_part('doy', date '2023-12-25');
/* output */
359
/* Exemplo básico de uso extraindo o epoch de um timestamp */
SELECT date_part('epoch', timestamp '2023-12-25 05:39:35-03');
/* output */
1703482775
/* Exemplo básico de uso extraindo o dia de um intervalo */
SELECT date_part('day', interval '10 days 3 hours');
/* output */
10
A função date_part é muito poderosa e pode ser utilizada para extrair qualquer um dessas partes de uma data abaixo:
| Unidade para extrair | Para que serve |
|---|---|
| century | Retorna o numero do século |
| day | Dia do mês de 1 até 31 |
| decade | Retorna a década, basicamente o ano dividido por 10 |
| dow | Dia da semana (Day of the week) onde 0 é domingo, 1 é segunda, etc |
| doy | Dia do ano (Day of the year) onde 1 é o primeiro dia |
| epoch | Epoch do Linux. Quantidade de segundos desde '1970-01-01 00:00:00 UTC' |
| hour | Hora de 0 até 23 |
| isodow | Dia da semana (Day of the week) onde 1 é segunda, etc |
| isoyear | Ano no formato ISO 8601 |
| microseconds | Micro segundos |
| millennium | Milênio |
| milliseconds | Milisegundos |
| minute | Minuto de 0 até 59 |
| month | Mês de 1 até 12, se for uma data. Quantidade de meses de 0 até 11 se for um intervalo |
| quarter | Trimestre de 1 até 4 |
| second | Segundos |
| timezone | Timezone |
| timezone_hour | A hora do timezone |
| timezone_minute | O minuto do timezone |
| week | Numero da semana do ano no formato ISO 8601 |
| year | Ano |
Função EXTRACT
O objetivo da função extract é extrair partes de uma data/hora do Postgres, como por exemplo, o mês, o dia da semana, o epoch, etc.
Essa função é semelhante a função date_part, as únicas diferenças estão na sintaxe, que possui um "from" entre o que você quer extrair e o valor a ser analisado. E o tipo do retorno, que retorna numérico ao invés de float8. Outro ponto é que a a função extract está no padrão SQL. Confira o nosso artigo explicando todas as diferenças: https://200ok.com.br/qual-e-a-diferenca-entre-date_part-e-extract-no-postgres/
Sintaxe da função EXTRACT
extract( 'unidade' from data )
A função extract possui dois parâmetros obrigatórios.
O parâmetro de 'unidade' é o que define o que você quer extrair da data, seja o dia, o dia do mês, dia da semana, trimestre, segundo, timezone, epoch, etc. Abaixo deixei uma tabela completa com todas as partes possíveis de extrair de um datetime.
Já o parâmetro 'data' é a data, ou data/hora (datetime) ou hora que queremos trabalhar. Você sempre precisa informar o tipo do valor, se é date, time, timestamp e interval e sempre utilizar o padrão do Postgres.
Exemplos da função EXTRACT
/* Exemplo básico de uso extraindo o dia do ano de uma data */
SELECT extract('doy' from date '2023-12-25');
/* output */
359
/* Exemplo básico de uso extraindo o epoch de um timestamp */
SELECT extract('doy' from timestamp '2023-12-25 05:39:35-03');
/* output */
1703482775.000000
/* Exemplo básico de uso extraindo o dia de um intervalo */
SELECT extract('day' from interval '10 days 3 hours');
/* output */
10
A função extract é muito poderosa e pode ser utilizada para extrair qualquer um dessas partes de uma data abaixo:
| Parte para extrair | Para que serve |
|---|---|
| century | Retorna o numero do século |
| day | Dia do mês de 1 até 31 |
| decade | Retorna a década, basicamente o ano dividido por 10 |
| dow | Dia da semana (Day of the week) onde 0 é domingo, 1 é segunda, etc |
| doy | Dia do ano (Day of the year) onde 1 é o primeiro dia |
| epoch | Epoch do Linux. Quantidade de segundos desde '1970-01-01 00:00:00 UTC' |
| hour | Hora de 0 até 23 |
| isodow | Dia da semana (Day of the week) onde 1 é segunda, etc |
| isoyear | Ano no formato ISO 8601 |
| microseconds | Micro segundos |
| millennium | Milênio |
| milliseconds | Milisegundos |
| minute | Minuto de 0 até 59 |
| month | Mês de 1 até 12, se for uma data. Quantidade de meses de 0 até 11 se for um intervalo |
| quarter | Trimestre de 1 até 4 |
| second | Segundos |
| timezone | Timezone |
| timezone_hour | A hora do timezone |
| timezone_minute | O minuto do timezone |
| week | Numero da semana do ano no formato ISO 8601 |
| year | Ano |
Função LOCALTIME
O objetivo da função localtime é retornar a hora local sem o timezone.
Sintaxe da função LOCALTIME
localtime( [precisão] )
Simples assim, utilize a função e o retorno será a hora local sem timezone. É importante destacar que a hora é capturada no início da transação e ela não muda durante a execução da transação.
O parâmetro de [precisão] é opcional e serve para definir o número de dígitos dos segundos a ser retornado.
Exemplos da função LOCALTIME
/* Exemplo básico de uso */
SELECT localtime
/* output */
15:19:04.810937
/* Exemplo localtime retornar apenas HH:MM:SS e timezone */
SELECT localtime(0)
/* output */
15:19:04
/* Exemplo localtime dentro de uma transação */
BEGIN;
SELECT localtime;
SELECT pg_sleep(1);
SELECT localtime;
COMMIT;
/* ou então */
SELECT localtime, pg_sleep(1), localtime;
/* output */
15:19:04.810937
15:19:04.810937Função LOCALTIMESTAMP
O objetivo da função localtimestamp é retornar a data/hora (timestamp) local sem o timezone.
Sintaxe da função LOCALTIMESTAMP
localtimestamp( [precisão] )
Simples assim, utilize a função e o retorno será a data/hora (timestamp) local sem timezone. É importante destacar que a hora é capturada no início da transação e ela não muda durante a execução da transação.
O parâmetro de [precisão] é opcional e serve para definir o número de dígitos dos segundos a ser retornado.
Exemplos da função LOCALTIMESTAMP
/* Exemplo básico de uso */
SELECT localtimestamp
/* output */
2023-02-20 06:37:19.541318
/* Exemplo localtimestamp retornar apenas HH:MM:SS e timezone */
SELECT localtimestamp(0)
/* output */
2023-02-20 06:37:19
/* Exemplo localtimestamp dentro de uma transação */
BEGIN;
SELECT localtimestamp;
SELECT pg_sleep(1);
SELECT localtimestamp;
COMMIT;
/* ou então */
SELECT localtimestamp, pg_sleep(1), localtimestamp;
/* output */
06:37:19.541318
06:37:19.541318Função NOW
O objetivo da função now é retornar a data/hora (timestamp) com o timezone.
Sintaxe da função NOW
now()
Simples assim, utilize a função e o retorno será a data/hora (timestamp) com timezone.
Ao contrário das funções localtime e localtimestamp , a função now não tem nenhum parâmetro.
Exemplos da função NOW
/* Exemplo básico de uso */
SELECT now()
/* output */
2023-02-20 06:42:03.674088-03Funções de Conversão
As funções de conversão possuem um papel muito importante na manipulação e na transformação de dados. É quase impossível fazer alguma coisa sem, em algum momento, converter algum tipo de dado para outro. O Postgres possui funções simples e diretas para converter os principais tipos de dados, desde os mais primitivos como texto para número até operações complexas de data e hora.
Função TO_CHAR
O objetivo da função to_char é converter um número ou um data para string.
Sintaxe da função TO_CHAR
to_char( valor, máscara)
A função to_char possui dois parâmetros obrigatórios, o valor que será convertido em string e a máscara que será utilizada.
O parâmetro valor pode ser um número ou uma data (data, hora ou data/hora).
Já o parâmetro máscara é diferente de acordo do que está sendo convertido. Ou seja, um número utiliza alguns tipos de máscara e uma data utiliza outros.
Abaixo deixei uma tabela completa com todas as máscaras possíveis para conversão na to_char .
Exemplos da função TO_CHAR
/* Exemplo básico de uso usando números */
SELECT to_char(1970, '9999.99');
/* output */
1970.00