Hologres:Date and time functions

Fonctions de conversion de type

Fonctions arithmétiques de date/heure

Extraction et troncature de champs de date/heure

Fonctions de date/heure actuelles

Autres fonctions

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

Convertit un horodatage, un entier ou un nombre en chaîne.

Syntaxe

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

Type de retour : TEXT

Notes d'utilisation

• Prend en charge les formats 24 heures (HH24) et 12 heures (HH12). Le format par défaut est 12 heures.

• Spécificateurs de format courants : YYYY (année), MM (mois), DD (jour), HH (heure), MI (minute), SS (seconde).

• Pour les horodatages avec fuseau horaire (TIMESTAMPTZ) : si aucun fuseau horaire n'est spécifié, la valeur est convertie vers le fuseau horaire du système (UTC+8 par défaut) avant le formatage. Utilisez AT TIME ZONE pour convertir vers un autre fuseau horaire.

• Pour étendre la plage horaire prise en charge au-delà de 1925–2282, consultez la section Prise en charge étendue de la plage horaire.

Exemples

Convertir un horodatage au format 24 heures :

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

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

Convertir un horodatage au format 12 heures :

-- 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');

Convertir une colonne TIMESTAMPTZ — la valeur est convertie vers le fuseau horaire du système (UTC+8) par défaut :

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;

Convertir une colonne TIMESTAMPTZ vers un fuseau horaire spécifique à l'aide de 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;

Convertir un entier en chaîne :

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

Convertir un nombre à double précision en chaîne :

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

TO_DATE

Convertit une chaîne en date. Plage horaire par défaut : 1925–2282.

Syntaxe

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

Paramètres

Type de retour : DATE

Notes d'utilisation

Pour étendre la plage horaire prise en charge au-delà de 1925–2282, consultez la section Prise en charge étendue de la plage horaire.

Exemples

Convertir une chaîne en date :

-- 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');

Convertir une colonne TEXT en date :

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;

Résultat :

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

TO_TIMESTAMP

Convertit une chaîne ou une heure Unix (epoch) en valeur TIMESTAMPTZ. Plage horaire par défaut : 1925–2282.

Syntaxe

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

Type de retour : TIMESTAMPTZ

Notes d'utilisation

• Le résultat inclut le décalage de fuseau horaire (par exemple, +08).

• Pour étendre la plage horaire prise en charge au-delà de 1925–2282, consultez la section Prise en charge étendue de la plage horaire.

Exemples

Convertir une chaîne en horodatage :

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

Résultat :

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

Convertir une colonne TEXT en horodatage :

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;

Résultat :

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

Convertir une heure Unix en secondes :

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

Convertir une heure Unix en millisecondes (divisez d'abord par 1000) :

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

MAKE_DATE

Crée une date à partir de valeurs entières d'année, de mois et de jour. Plage horaire par défaut : 1925–2282.

Syntaxe

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

Type de retour : DATE

Notes d'utilisation

Disponible dans Hologres V2.0.29 et versions ultérieures. Dans les opérations d'écriture, les arguments ne peuvent pas tous être des constantes.

Exemple

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

Résultat :

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

DATEADD

Ajoute ou soustrait un intervalle de temps à une date. Plage horaire par défaut : 1925–2282.

Syntaxe

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

Paramètres

Type de retour : Identique au type d'entrée (DATE, TIMESTAMP ou TIMESTAMPTZ).

Notes d'utilisation

• Disponible dans les versions Hologres V2.0.31–V2.1.0 et V2.1.13 et ultérieures.

• Dans les opérations d'écriture, les arguments ne peuvent pas tous être des constantes.

Exemple

Ajouter un mois à une colonne d'horodatage :

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;

Résultat :

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

ADD_MONTHS

Ajoute un nombre spécifié de mois à une date. Il s'agit d'une fonction compatible Oracle qui nécessite l'extension orafce. Pour plus d'informations, consultez la rubrique Fonctions Oracle prises en charge.

Syntaxe

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

Paramètres

Type de retour : DATE

Exemple

SELECT ADD_MONTHS(current_date, 2);

Résultat :

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

DATEDIFF

Renvoie la différence entre deux dates ou horodatages dans une unité spécifiée. Plage horaire par défaut : 1925–2282.

Syntaxe

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

Paramètres

Type de retour : BIGINT

Notes d'utilisation

• Disponible dans les versions Hologres V2.0.31–V2.1.0 et V2.1.13 et ultérieures.

• Dans les opérations d'écriture, les arguments ne peuvent pas tous être des constantes.

• La fonction utilise une troncature par unité complète, et non un arrondi. Si la différence est inférieure à une unité complète, elle renvoie 0. Par exemple, la différence d'années entre 2023-12-31 et 2024-01-01 est 0.

• Pour compter les franchissements de limites (renvoyant 1 dans l'exemple précédent), exécutez l'instruction suivante avant votre requête :

  SET hg_experimental_datediff_use_presto_impl = off;

Exemple

Calculer la différence en minutes entre deux horodatages :

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;

Résultat :

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

MONTHS_BETWEEN

Renvoie le nombre de mois entre deux dates. Il s'agit d'une fonction compatible Oracle qui nécessite l'extension orafce. Pour plus d'informations, consultez la rubrique Fonctions Oracle prises en charge.

Syntaxe

MONTHS_BETWEEN(DATE, DATE)

Type de retour : INT

Exemples

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

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

NEXT_DAY

Renvoie la date du premier jour de la semaine spécifié qui suit une date donnée. Il s'agit d'une fonction compatible Oracle qui nécessite l'extension orafce. Pour plus d'informations, consultez la rubrique Fonctions Oracle prises en charge.

Syntaxe

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

Paramètres

Type de retour : DATE

Exemples

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

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

Opérateurs arithmétiques

Les opérateurs suivants fonctionnent avec les valeurs de date et d'heure.

EXTRACT

Extrait un sous-champ (tel que l'année, le mois ou le jour) d'une expression d'horodatage. EXTRACT est équivalent à DATE_PART.

Syntaxe

EXTRACT(field FROM TIMESTAMP)

Type de retour : DOUBLE PRECISION

Notes d'utilisation

Valeurs valides pour field : century, day, decade, dow (jour de la semaine, 0=dimanche), isodow (jour de la semaine, 7=dimanche), doy (jour de l'année), epoch, hour, minute, month, quarter, second, week, year.

Exemples

Extraire l'heure d'un horodatage :

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

Extraire la minute de l'heure actuelle :

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

Obtenir l'heure Unix (secondes depuis 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;

Résultat :

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

Fonctions d'extraction optimisées pour les performances

À partir de Hologres V4.0, les fonctions suivantes sont prises en charge pour une meilleure compatibilité avec ClickHouse et Doris. Elles ont la même sémantique que EXTRACT(<field> FROM timestamp) mais offrent de meilleures performances. Ces fonctions ne prennent pas en charge les arguments composés uniquement de constantes.

DATE_PART

Extrait un sous-champ d'un horodatage. DATE_PART est équivalent à EXTRACT.

Syntaxe

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

Paramètres

Type de retour : DOUBLE PRECISION

Exemples

Obtenir l'heure d'un horodatage :

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

Résultat :

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

Obtenir le numéro de semaine ISO pour une date :

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

Résultat :

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

Obtenir le numéro de mois pour une date :

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

Résultat :

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

DATE_TRUNC

Tronque une valeur de date ou d'heure à une précision spécifiée.

Syntaxe

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

Paramètres

Type de retour : TIMESTAMP ou TIMESTAMPTZ (correspond au type d'entrée).

Exemples

Tronquer une heure à l'heure près :

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

Résultat :

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

Tronquer un horodatage au jour près :

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

Résultat :

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

Tronquer un horodatage au mois près :

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

Résultat :

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

Obtenir le premier jour du mois actuel à 12 h 00 :

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

Résultat :

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

Obtenir 09 h 00 le jour actuel :

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

Résultat :

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

Obtenir le début du même jour de la semaine suivante :

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

Résultat :

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

LAST_DAY

Renvoie le dernier jour du mois pour une date spécifiée. Plage horaire par défaut : 1925–2282.

Syntaxe

LAST_DAY(DATE | TIMESTAMP | TIMESTAMPTZ)

Type de retour : DATE

Notes d'utilisation

• Disponible dans les versions Hologres V2.0.31–V2.1.0 et V2.1.13 et ultérieures.

• Dans les opérations d'écriture, les arguments ne peuvent pas tous être des constantes.

Exemple

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;

Résultat :

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

ORACLE_LAST_DAY

Renvoie le dernier jour du mois pour une date spécifiée. Il s'agit d'une fonction compatible Oracle qui nécessite l'extension orafce. Plage horaire par défaut : 1925–2282. Pour plus d'informations, consultez la rubrique Fonctions Oracle prises en charge.

Syntaxe

ORACLE_LAST_DAY(DATE)

Type de retour : DATE

Exemple

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

Résultat :

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

TRUNC

Tronque une date ou un horodatage à une précision spécifiée. Il s'agit d'une fonction compatible Oracle qui nécessite l'extension orafce. Pour plus d'informations, consultez la rubrique Fonctions Oracle prises en charge.

Syntaxe

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

Paramètres

Type de retour : DATE ou TIMESTAMP

Exemples

Tronquer une date au début de l'année :

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

Résultat :

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

Tronquer un horodatage au début de l'année :

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

Résultat :

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

Tronquer un horodatage au début du trimestre :

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

Résultat :

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

Tronquer un horodatage au début du jour (par défaut) :

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

Résultat :

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

ROUND

Arrondit une date ou un horodatage à l'unité de temps la plus proche. Il s'agit d'une fonction compatible Oracle qui nécessite l'extension orafce. Pour plus d'informations, consultez la rubrique Fonctions Oracle prises en charge.

Syntaxe

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

Paramètres

Type de retour : DATE ou TIMESTAMP

Exemples

Arrondir une date à l'année la plus proche (avant le 1er juillet — arrondi vers le bas) :

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

Résultat :

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

Arrondir une date à l'année la plus proche (après le 1er juillet — arrondi vers le haut) :

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

Résultat :

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

Arrondir un horodatage à l'année la plus proche :

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

Résultat :

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

Arrondir un horodatage au jour le plus proche (par défaut) :

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

Résultat :

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

CURRENT_DATE

Renvoie la date actuelle au début de la transaction.

Syntaxe

CURRENT_DATE

Type de retour : DATE

Exemple

SELECT CURRENT_DATE;

Résultat :

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

CURRENT_TIMESTAMP

Renvoie l'horodatage au début de la transaction actuelle. Équivalent à TRANSACTION_TIMESTAMP() et NOW().

Syntaxe

CURRENT_TIMESTAMP

La valeur renvoyée ne change pas au sein d'une seule transaction.

Type de retour : TIMESTAMPTZ

Exemple

SELECT CURRENT_TIMESTAMP;

Résultat :

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

CLOCK_TIMESTAMP

Renvoie l'heure réelle actuelle.

Syntaxe

clock_timestamp()

Contrairement à CURRENT_TIMESTAMP et NOW() , la valeur renvoyée change à chaque appel, même au sein d'une seule instruction SQL.

Type de retour : TIMESTAMPTZ

Exemple

SELECT clock_timestamp();

Résultat :

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

LOCALTIMESTAMP

Renvoie l'horodatage au début de la transaction actuelle, sans information de fuseau horaire.

Syntaxe

LOCALTIMESTAMP

Type de retour : TIMESTAMP

Exemple

SELECT LOCALTIMESTAMP;

Résultat :

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

NOW

Renvoie l'horodatage au début de la transaction actuelle. Équivalent à CURRENT_TIMESTAMP et TRANSACTION_TIMESTAMP().

Syntaxe

NOW()

La valeur renvoyée ne change pas au sein d'une seule transaction.

Type de retour : TIMESTAMPTZ

Exemple

SELECT NOW();

Résultat :

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

STATEMENT_TIMESTAMP

Renvoie l'horodatage au début de l'instruction actuelle.

Syntaxe

STATEMENT_TIMESTAMP()

La valeur est cohérente au sein d'une seule instruction, mais diffère selon les instructions au sein de la même transaction.

Type de retour : TIMESTAMPTZ

Exemple

SELECT STATEMENT_TIMESTAMP();

Résultat :

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

TIMEOFDAY

Renvoie l'heure réelle actuelle sous forme de chaîne de texte formatée. Similaire à CLOCK_TIMESTAMP(), mais renvoie une valeur TEXT au lieu de TIMESTAMPTZ.

Syntaxe

TIMEOFDAY()

Type de retour : TEXT

Exemple

SELECT TIMEOFDAY();

Résultat :

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

TRANSACTION_TIMESTAMP

Renvoie l'horodatage au début de la transaction actuelle. Équivalent à CURRENT_TIMESTAMP et NOW().

Syntaxe

TRANSACTION_TIMESTAMP()

La valeur renvoyée ne change pas au sein d'une seule transaction.

Type de retour : TIMESTAMPTZ

Exemple

SELECT TRANSACTION_TIMESTAMP();

Résultat :

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

ISFINITE

Vérifie si une date ou un horodatage est fini (ni infinity ni -infinity).

Syntaxe

ISFINITE(DATE)
ISFINITE(TIMESTAMP)

Type de retour : BOOLEAN — renvoie t si fini, f si infini.

Exemples

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

Résultat :

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

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

Résultat :

 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