Hologres:Funções de data e hora

Funções de conversão de tipo

Funções aritméticas de data/hora

Extração e truncamento de campos de data/hora

Funções de data/hora atual

Outras funções

SET hg_experimental_functions_use_pg_implementation = 'to_char';
SET hg_experimental_functions_use_pg_implementation = 'to_date';
SET hg_experimental_functions_use_pg_implementation = 'to_timestamp';
-- Or enable all three at once:
SET hg_experimental_functions_use_pg_implementation = 'to_char,to_date,to_timestamp';

TO_CHAR

Converte um timestamp, inteiro ou número em uma string.

Sintaxe

TO_CHAR(TIMESTAMP | TIMESTAMPTZ, TEXT)
TO_CHAR(INT, TEXT)
TO_CHAR(DOUBLE PRECISION, TEXT)

Tipo de retorno: TEXT

Notas de uso

• Oferece suporte aos formatos de 24 horas (HH24) e 12 horas (HH12). O padrão é 12 horas.

• Especificadores de formato comuns: YYYY (ano), MM (mês), DD (dia), HH (hora), MI (minuto), SS (segundo).

• Para timestamps com fuso horário (TIMESTAMPTZ): se nenhum fuso horário for especificado, o valor será convertido para o fuso horário do sistema (UTC+8 por padrão) antes da formatação. Use AT TIME ZONE para converter para um fuso horário diferente.

• Para estender o intervalo de tempo suportado além de 1925–2282, consulte Suporte a intervalo de tempo estendido.

Exemplos

Converta um timestamp para o formato de 24 horas:

-- Returns: 13:48:30
SELECT TO_CHAR(current_timestamp, 'HH24:MI:SS');

-- Returns: 2024-08-05
SELECT TO_CHAR(current_timestamp, 'YYYY-MM-DD');

Converta um timestamp para o formato de 12 horas:

-- Returns: 01:50:42 PM
SELECT TO_CHAR(current_timestamp, 'HH12:MI:SS AM');

-- Returns: 12:30:00 AM
SELECT TO_CHAR(time '00:30:00', 'HH12:MI:SS AM');

Converta uma coluna TIMESTAMPTZ — o valor é convertido para o fuso horário do sistema (UTC+8) por padrão:

CREATE TABLE time_test(a TEXT, b TIMESTAMPTZ);
INSERT INTO time_test VALUES ('2001-09-28 03:00:00', '2004-10-19 10:23:54+08');

-- Returns: 10:23:54
SELECT TO_CHAR(b, 'HH24:MI:SS') FROM time_test;

Converta uma coluna TIMESTAMPTZ para um fuso horário específico usando AT TIME ZONE:

CREATE TABLE timestamptz_test(a TIMESTAMPTZ);
INSERT INTO timestamptz_test VALUES ('2023-03-21 10:23:54+02');

-- Without a time zone override: converted to system time zone (UTC+8). Returns: 2023-03-21 16:23:54
SELECT TO_CHAR(a, 'YYYY-MM-DD HH24:MI:SS') FROM timestamptz_test;

-- With the US/Eastern time zone. Returns: 2023-03-21 04:23:54
SELECT TO_CHAR(a AT TIME ZONE 'US/Eastern', 'YYYY-MM-DD HH24:MI:SS') FROM timestamptz_test;

Converta um inteiro em uma string:

-- Returns: 125
SELECT TO_CHAR(125, '999');

Converta um número de precisão dupla em uma string:

-- Returns: 125.8
SELECT TO_CHAR(125.8::real, '999D9');

TO_DATE

Converte uma string em uma data. Intervalo de tempo padrão: 1925–2282.

Sintaxe

TO_DATE(<text_date> TEXT, <format_mask> TEXT)

Parâmetros

Tipo de retorno: DATE

Notas de uso

Para estender o intervalo de tempo suportado além de 1925–2282, consulte Suporte a intervalo de tempo estendido.

Exemplos

Converta uma string em uma data:

-- Returns: 2000-12-05
SELECT TO_DATE('05 Dec 2000', 'DD Mon YYYY');

-- Returns: 2001-03-24
SELECT TO_DATE('2001 03 24', 'YYYY-MM-DD');

Converta uma coluna TEXT em uma data:

CREATE TABLE time_test(a TEXT);
INSERT INTO time_test VALUES ('2001-09-28 03:00:00');
SELECT TO_DATE(a, 'YYYY-MM-DD') FROM time_test;

Resultado:

  to_date
------------
 2001-09-28

TO_TIMESTAMP

Converte uma string ou um tempo de época Unix em um valor TIMESTAMPTZ. Intervalo de tempo padrão: 1925–2282.

Sintaxe

-- Convert a string to a timestamp
TO_TIMESTAMP(<text_date> TEXT, <format_mask> TEXT)

-- Convert a Unix epoch time (seconds since 1970-01-01 00:00:00 UTC) to a timestamp
TO_TIMESTAMP(DOUBLE PRECISION)

Tipo de retorno: TIMESTAMPTZ

Notas de uso

• O resultado inclui o deslocamento do fuso horário (por exemplo, +08).

• Para estender o intervalo de tempo suportado além de 1925–2282, consulte Suporte a intervalo de tempo estendido.

Exemplos

Converta uma string em um timestamp:

SELECT TO_TIMESTAMP('05 Dec 2000', 'DD Mon YYYY');

Resultado:

      to_timestamp
------------------------
 2000-12-05 00:00:00+08

Converta uma coluna TEXT em um timestamp:

CREATE TABLE time_test(a TEXT);
INSERT INTO time_test VALUES ('2001-09-28 03:00:00');
SELECT TO_TIMESTAMP(a, 'YYYY-MM-DD') FROM time_test;

Resultado:

      to_timestamp
------------------------
 2001-09-28 00:00:00+08

Converta um tempo de época Unix em segundos:

-- Returns: 1975-03-06 03:38:16+08
SELECT TO_TIMESTAMP(163280296);

Converta um tempo de época Unix em milissegundos (divida por 1000 primeiro):

-- Returns: 2021-09-28 12:22:41+08
SELECT TO_TIMESTAMP(1632802961000 / 1000);

MAKE_DATE

Cria uma data a partir de valores inteiros de ano, mês e dia. Intervalo de tempo padrão: 1925–2282.

Sintaxe

MAKE_DATE(<year> INT, <month> INT, <day> INT)

Tipo de retorno: DATE

Notas de uso

Disponível no Hologres V2.0.29 e versões posteriores. Em operações de escrita, os argumentos não podem ser todos constantes.

Exemplo

-- Returns: 2013-07-15
SELECT MAKE_DATE(2013, 7, 15);

Resultado:

 make_date
------------
 2013-07-15

DATEADD

Adiciona ou subtrai um intervalo de tempo de uma data. Intervalo de tempo padrão: 1925–2282.

Sintaxe

DATEADD(<d> DATE | TIMESTAMP | TIMESTAMPTZ, <num> BIGINT, <str> TEXT)

Parâmetros

Tipo de retorno: Igual ao tipo de entrada (DATE, TIMESTAMP ou TIMESTAMPTZ).

Notas de uso

• Disponível no Hologres V2.0.31–V2.1.0 e V2.1.13 e versões posteriores.

• Em operações de escrita, os argumentos não podem ser todos constantes.

Exemplo

Adicione um mês a uma coluna de timestamp:

CREATE TABLE test_dateadd (a TIMESTAMP);
INSERT INTO test_dateadd VALUES ('2005-02-28 00:00:00');

SELECT DATEADD(a, 1, 'mm') FROM test_dateadd;

Resultado:

       dateadd
---------------------
 2005-03-28 00:00:00

ADD_MONTHS

Adiciona um número especificado de meses a uma data. Esta é uma função compatível com Oracle que requer a extensão orafce. Para obter mais informações, consulte Funções Oracle suportadas.

Sintaxe

ADD_MONTHS(<d> DATE, <month> INT)

Parâmetros

Tipo de retorno: DATE

Exemplo

SELECT ADD_MONTHS(current_date, 2);

Resultado:

 add_months
------------
 2024-10-05

DATEDIFF

Retorna a diferença entre duas datas ou timestamps em uma unidade especificada. Intervalo de tempo padrão: 1925–2282.

Sintaxe

DATEDIFF(<d1> DATE | TIMESTAMP | TIMESTAMPTZ, <d2> DATE | TIMESTAMP | TIMESTAMPTZ, <str> TEXT)

Parâmetros

Tipo de retorno: BIGINT

Notas de uso

• Disponível no Hologres V2.0.31–V2.1.0 e V2.1.13 e versões posteriores.

• Em operações de escrita, os argumentos não podem ser todos constantes.

• A função utiliza truncamento de unidade completa, não arredondamento. Se a diferença for menor que uma unidade completa, ela retorna 0. Por exemplo, a diferença de anos entre 2023-12-31 e 2024-01-01 é 0.

• Para contar cruzamentos de limites (retornando 1 no exemplo anterior), execute a seguinte instrução antes da consulta:

  SET hg_experimental_datediff_use_presto_impl = off;

Exemplo

Calcule a diferença em minutos entre dois timestamps:

CREATE TABLE test_datediff (a TIMESTAMP);
INSERT INTO test_datediff VALUES ('2005-02-28 00:00:00');

SELECT DATEDIFF(a, '2005-03-02 00:00:00', 'mi') FROM test_datediff;

Resultado:

 datediff
----------
    -2880

MONTHS_BETWEEN

Retorna o número de meses entre duas datas. Esta é uma função compatível com Oracle que requer a extensão orafce. Para obter mais informações, consulte Funções Oracle suportadas.

Sintaxe

MONTHS_BETWEEN(DATE, DATE)

Tipo de retorno: INT

Exemplos

-- Returns: 2
SELECT MONTHS_BETWEEN('2022-01-01', '2021-11-01');

-- Returns: -2
SELECT MONTHS_BETWEEN('2021-11-01', '2022-01-01');

NEXT_DAY

Retorna a data do primeiro dia da semana especificado que segue uma determinada data. Esta é uma função compatível com Oracle que requer a extensão orafce. Para obter mais informações, consulte Funções Oracle suportadas.

Sintaxe

NEXT_DAY(<d> DATE, <str> TEXT | INT)

Parâmetros

Tipo de retorno: DATE

Exemplos

-- Returns: 2022-05-06
SELECT NEXT_DAY('2022-05-01', 'FRIDAY');

-- Returns: 2022-05-06
SELECT NEXT_DAY('2022-05-01', 5);

Operadores aritméticos

Os operadores a seguir funcionam com valores de data e hora.

EXTRACT

Extrai um subcampo (como ano, mês ou dia) de uma expressão de timestamp. EXTRACT equivale a DATE_PART.

Sintaxe

EXTRACT(field FROM TIMESTAMP)

Tipo de retorno: DOUBLE PRECISION

Notas de uso

Valores válidos para field: century, day, decade, dow (dia da semana, 0=Domingo), isodow (dia da semana, 7=Domingo), doy (dia do ano), epoch, hour, minute, month, quarter, second, week, year.

Exemplos

Extraia a hora de um timestamp:

-- Returns: 20
SELECT EXTRACT(hour FROM timestamp '2001-02-16 20:38:40');

Extraia o minuto da hora atual:

-- Returns: 12
SELECT EXTRACT(minute FROM NOW());

Obtenha o tempo de época Unix (segundos desde 1970-01-01 00:00:00 UTC):

CREATE TABLE time_test(a TEXT);
INSERT INTO time_test VALUES ('2001-09-28 03:00:00');
SELECT EXTRACT(epoch FROM to_timestamp(a, 'YYYY-MM-DD')) FROM time_test;

Resultado:

  date_part
------------
 1001606400

Funções de extração otimizadas para desempenho

A partir do Hologres V4.0, há suporte às seguintes funções para melhor compatibilidade com ClickHouse e Doris. Elas têm a mesma semântica de EXTRACT(<field> FROM timestamp), mas oferecem melhor desempenho. Essas funções não aceitam argumentos compostos apenas por constantes.

DATE_PART

Extrai um subcampo de um timestamp. DATE_PART equivale a EXTRACT.

Sintaxe

DATE_PART(<str> TEXT, <d> TIMESTAMP)

Parâmetros

Tipo de retorno: DOUBLE PRECISION

Exemplos

Obtenha a hora de um timestamp:

SELECT DATE_PART('hour', timestamp '2001-02-16 16:38:40');

Resultado:

 date_part
-----------
        16

Obtenha o número da semana ISO para uma data:

SELECT DATE_PART('week', TO_DATE('2022-10-11', 'YYYY-MM-DD'));

Resultado:

 date_part
-----------
        41

Obtenha o número do mês para uma data:

SELECT DATE_PART('month', TO_DATE('2022-10-11', 'YYYY-MM-DD'));

Resultado:

 date_part
-----------
        10

DATE_TRUNC

Trunca um valor de data ou hora para uma precisão especificada.

Sintaxe

DATE_TRUNC(<str> TEXT, <d> TIME | TIMESTAMP | TIMESTAMPTZ)

Parâmetros

Tipo de retorno: TIMESTAMP ou TIMESTAMPTZ (corresponde ao tipo de entrada).

Exemplos

Trunque uma hora para a hora cheia:

SELECT DATE_TRUNC('hour', time '12:38:40');

Resultado:

 date_trunc
------------
 12:00:00

Trunque um timestamp para o dia:

SELECT DATE_TRUNC('day', timestamptz '2001-02-16 20:38:40+08');

Resultado:

       date_trunc
------------------------
 2001-02-16 00:00:00+08

Trunque um timestamp para o mês:

SELECT DATE_TRUNC('month', timestamp '2001-02-16 18:38:40');

Resultado:

     date_trunc
---------------------
 2001-02-01 00:00:00

Obtenha o primeiro dia do mês atual às 12:00:

SELECT DATE_TRUNC('month', now()) + interval '12h';

Resultado:

       ?column?
---------------------
 2024-08-01 12:00:00+08

Obtenha 09:00 do dia atual:

SELECT DATE_TRUNC('day', now()) + interval '9h';

Resultado:

       ?column?
------------------------
 2024-08-08 09:00:00+08

Obtenha o início do mesmo dia da semana na semana seguinte:

SELECT DATE_TRUNC('day', now()) + interval '7d';

Resultado:

       ?column?
------------------------
 2024-08-15 00:00:00+08

LAST_DAY

Retorna o último dia do mês para uma data especificada. Intervalo de tempo padrão: 1925–2282.

Sintaxe

LAST_DAY(DATE | TIMESTAMP | TIMESTAMPTZ)

Tipo de retorno: DATE

Notas de uso

• Disponível no Hologres V2.0.31–V2.1.0 e V2.1.13 e versões posteriores.

• Em operações de escrita, os argumentos não podem ser todos constantes.

Exemplo

CREATE TABLE test_last_day (a TIMESTAMP);
INSERT INTO test_last_day VALUES ('2004-02-28 00:00:00');

SELECT LAST_DAY(a) FROM test_last_day;

Resultado:

  last_day
------------
 2004-02-29

ORACLE_LAST_DAY

Retorna o último dia do mês para uma data especificada. Esta é uma função compatível com Oracle que requer a extensão orafce. Intervalo de tempo padrão: 1925–2282. Para obter mais informações, consulte Funções Oracle suportadas.

Sintaxe

ORACLE_LAST_DAY(DATE)

Tipo de retorno: DATE

Exemplo

SELECT ORACLE_LAST_DAY('2022-05-01');

Resultado:

 oracle_last_day
-----------------
 2022-05-31

TRUNC

Trunca uma data ou timestamp para uma precisão especificada. Esta é uma função compatível com Oracle que requer a extensão orafce. Para obter mais informações, consulte Funções Oracle suportadas.

Sintaxe

TRUNC(<d> DATE | TIMESTAMP [, <str> TEXT])

Parâmetros

Tipo de retorno: DATE ou TIMESTAMP

Exemplos

Trunque uma data para o início do ano:

SELECT TRUNC('2022-05-22'::date, 'Y');

Resultado:

   trunc
------------
 2022-01-01

Trunque um timestamp para o início do ano:

SELECT TRUNC('2022-05-22 13:11:22'::timestamp, 'Y');

Resultado:

        trunc
---------------------
 2022-01-01 00:00:00

Trunque um timestamp para o início do trimestre:

SELECT TRUNC('2022-05-22 13:11:22'::timestamp, 'Q');

Resultado:

        trunc
---------------------
 2022-04-01 00:00:00

Trunque um timestamp para o início do dia (padrão):

SELECT TRUNC('2022-05-22 13:11:22'::timestamp);

Resultado:

        trunc
---------------------
 2022-05-22 00:00:00

ROUND

Arredonda uma data ou timestamp para a unidade de tempo mais próxima. Esta é uma função compatível com Oracle que requer a extensão orafce. Para obter mais informações, consulte Funções Oracle suportadas.

Sintaxe

ROUND(<d> DATE | TIMESTAMPTZ [, <str> TEXT])

Parâmetros

Tipo de retorno: DATE ou TIMESTAMP

Exemplos

Arredonde uma data para o ano mais próximo (antes de 1º de julho — arredonda para baixo):

SELECT ROUND('2022-05-22'::date, 'Y');

Resultado:

   round
------------
 2022-01-01

Arredonde uma data para o ano mais próximo (após 1º de julho — arredonda para cima):

SELECT ROUND('2022-07-22'::date, 'Y');

Resultado:

   round
------------
 2023-01-01

Arredonde um timestamp para o ano mais próximo:

SELECT ROUND('2022-07-22 13:11:22'::timestamp, 'Y');

Resultado:

        round
---------------------
 2023-01-01 00:00:00

Arredonde um timestamp para o dia mais próximo (padrão):

SELECT ROUND('2022-02-22 13:11:22'::timestamp);

Resultado:

        round
---------------------
 2022-02-23 00:00:00

CURRENT_DATE

Retorna a data atual no início da transação.

Sintaxe

CURRENT_DATE

Tipo de retorno: DATE

Exemplo

SELECT CURRENT_DATE;

Resultado:

 current_date
--------------
 2024-08-08

CURRENT_TIMESTAMP

Retorna o timestamp no início da transação atual. Equivalente a TRANSACTION_TIMESTAMP() e NOW().

Sintaxe

CURRENT_TIMESTAMP

O valor retornado não muda dentro de uma única transação.

Tipo de retorno: TIMESTAMPTZ

Exemplo

SELECT CURRENT_TIMESTAMP;

Resultado:

       current_timestamp
-------------------------------
 2024-08-08 14:55:11.006068+08

CLOCK_TIMESTAMP

Retorna a hora atual real.

Sintaxe

clock_timestamp()

Ao contrário de CURRENT_TIMESTAMP e NOW() , o valor retornado muda a cada chamada, mesmo dentro de uma única instrução SQL.

Tipo de retorno: TIMESTAMPTZ

Exemplo

SELECT clock_timestamp();

Resultado:

        clock_timestamp
-------------------------------
 2024-08-08 14:57:43.569109+08

LOCALTIMESTAMP

Retorna o timestamp no início da transação atual, sem informações de fuso horário.

Sintaxe

LOCALTIMESTAMP

Tipo de retorno: TIMESTAMP

Exemplo

SELECT LOCALTIMESTAMP;

Resultado:

      localtimestamp
---------------------------
 2024-08-08 15:00:59.13245

NOW

Retorna o timestamp no início da transação atual. Equivalente a CURRENT_TIMESTAMP e TRANSACTION_TIMESTAMP().

Sintaxe

NOW()

O valor retornado não muda dentro de uma única transação.

Tipo de retorno: TIMESTAMPTZ

Exemplo

SELECT NOW();

Resultado:

              now
-------------------------------
 2024-08-08 15:02:50.270501+08

STATEMENT_TIMESTAMP

Retorna o timestamp no início da instrução atual.

Sintaxe

STATEMENT_TIMESTAMP()

O valor é consistente dentro de uma única instrução, mas difere entre instruções na mesma transação.

Tipo de retorno: TIMESTAMPTZ

Exemplo

SELECT STATEMENT_TIMESTAMP();

Resultado:

      statement_timestamp
-------------------------------
 2024-08-08 15:06:14.772939+08

TIMEOFDAY

Retorna a hora atual real como uma string de texto formatada. Semelhante a CLOCK_TIMESTAMP(), mas retorna um valor TEXT em vez de TIMESTAMPTZ.

Sintaxe

TIMEOFDAY()

Tipo de retorno: TEXT

Exemplo

SELECT TIMEOFDAY();

Resultado:

              timeofday
-------------------------------------
 Thu Aug 08 15:08:16.599369 2024 CST

TRANSACTION_TIMESTAMP

Retorna o timestamp no início da transação atual. Equivalente a CURRENT_TIMESTAMP e NOW().

Sintaxe

TRANSACTION_TIMESTAMP()

O valor retornado não muda dentro de uma única transação.

Tipo de retorno: TIMESTAMPTZ

Exemplo

SELECT TRANSACTION_TIMESTAMP();

Resultado:

     transaction_timestamp
-------------------------------
 2024-08-08 15:11:10.329005+08

ISFINITE

Testa se uma data ou timestamp é finito (não é infinity ou -infinity).

Sintaxe

ISFINITE(DATE)
ISFINITE(TIMESTAMP)

Tipo de retorno: BOOLEAN — retorna t se finito, f se infinito.

Exemplos

SELECT ISFINITE(date '2001-02-16');

Resultado:

 isfinite
----------
 t

SELECT ISFINITE(timestamp '2001-02-16 21:28:30');

Resultado:

 isfinite
----------
 t

SELECT NOW() + interval '2 hour';

           ?column?
-------------------------------
 2022-12-29 13:43:58.321104+08

SELECT EXTRACT(epoch FROM current_timestamp);

     date_part
---------------------
 1672285506.296279

CREATE TABLE date_test1(a DATE, b INT);
INSERT INTO date_test1 VALUES ('2021-09-28', '12');

SELECT a + (b || ' month')::interval FROM date_test1;

       ?column?
--------------------
 2022-09-28 00:00:00

SELECT TO_TIMESTAMP(TO_CHAR(20211027172045, '9999-99-99 99:99:99'), 'YYYY-MM-DD HH24:MI:SS');

     to_timestamp
----------------------
 2021-10-27 17:20:45+08

SELECT EXTRACT(mon FROM now());

 date_part
-----------
        12

-- Integer division discards the remainder: 10/3 = 3
-- Cast to float to get a decimal result
SELECT 10 / 3::float;

       ?column?
---------
 3.3333333333333335