Hologres：日期和時間函數

TO_CHAR

• 描述

  • 將時間戳記轉換為字串，預設支援的時間範圍為1925~2282年。

    TO_CHAR(TIMESTAMP|TIMESTAMPTZ, TEXT)

    使用說明：

    • 支援24小時制和12小時制的轉換。其中，HH24對應24小時制，HH12對應12小時制，預設為12小時制。

    • YYYY對應年，MM對應月，DD對應日，HH對應時，MI對應分，SS對應秒。

    • 自Hologres V1.1.31版本開始，在SQL前執行set hg_experimental_functions_use_pg_implementation = 'to_char';或者set hg_experimental_functions_use_pg_implementation = 'to_char,to_date,to_timestamp';可支援所有時間。

      說明

      使用該GUC參數後，查詢效能約有50%的損失，升級至Hologres V1.1.42及以上版本後，約有20%的損失。

  • 將整數轉換為字串。

    TO_CHAR(INT, TEXT)

  • 將實數和雙精確度數轉換為字串。

    TO_CHAR(DOUBLE PRECISION, TEXT)

• 傳回值說明

  返回TEXT類型。

• 樣本

  • 將時間戳記轉換為24小時制。

    --返回結果：13:48:30
    SELECT TO_CHAR(current_timestamp, 'HH24:MI:SS');
    
    --返回結果：2024-08-05
    SELECT TO_CHAR(current_timestamp, 'YYYY-MM-DD');

  • 將時間戳記轉換為12小時制。

    --返回結果：01:50:42 PM
    SELECT TO_CHAR(current_timestamp, 'HH12:MI:SS AM');
    
    --返回結果：12:30:00 AM
    SELECT TO_CHAR(time '00:30:00', 'HH12:MI:SS AM');

  • 時間戳記欄位的轉換。

    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');
    
    --返回結果：10:23:54
    SELECT TO_CHAR(b, 'HH24:MI:SS') FROM time_test;
    
    --將text的欄位轉換為日期，返回結果：2001-09-28
    SELECT TO_CHAR(to_timestamp(a, 'YYYY-MM-DD'),'YYYY-MM-DD')FROM time_test;

  • 跨時區的時間戳記轉換。

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

    • 不指定時區，則預設將a列轉為系統時區（東八區）後轉換為字串。

      --返回結果：2023-03-21 16:23:54
      SELECT TO_CHAR(a, 'YYYY-MM-DD HH24:MI:SS') FROM timestamptz_test;

    • 指定時區為美東時區後，將a列其轉換為字串。

      --返回結果：2023-03-21 04:23:54
      SELECT TO_CHAR(a at time zone 'US/Eastern', 'YYYY-MM-DD HH24:MI:SS') FROM timestamptz_test;

  • 整數轉換為字串。

    --返回結果： 125
    SELECT TO_CHAR(125, '999');

  • 雙精確度數轉換為字串。

    --返回結果： 125.8
    SELECT TO_CHAR(125.8::real, '999D9');

TO_DATE

• 描述：將字串轉換為日期，預設支援的時間範圍為1925~2282年。

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

• 使用說明

  自Hologres V1.1.31版本開始，在SQL前執行set hg_experimental_functions_use_pg_implementation = 'to_date';或者set hg_experimental_functions_use_pg_implementation = 'to_char,to_date,to_timestamp';可支援所有時間。

  說明

  使用該GUC參數後，查詢效能約有50%的損失，升級至Hologres V1.1.42及以上版本後，約有20%的損失。

• 參數說明

  • text_date：必填，需要轉換的字串。

  • format_mask：必填，轉換為日期的格式。

• 傳回值說明

  返回TEXT類型。

• 樣本

  • 字串轉換為日期。

    --返回結果：2000-12-05
    SELECT TO_DATE('05 Dec 2000', 'DD Mon YYYY');
    
    --返回結果：2001-03-24
    SELECT TO_DATE('2001 03 24', 'YYYY-MM-DD');

  • TEXT類型的表欄位轉換為日期。

    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;

    返回結果如下。

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

TO_TIMESTAMP

• 描述：

  • 將字串轉換為時間戳記，預設支援時間範圍為1925~2282年。

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

    • 使用說明

      • 執行結果中包含+08時。

      • 自Hologres V1.1.31版本開始，在SQL前執行set hg_experimental_functions_use_pg_implementation = 'to_timestamp';或者set hg_experimental_functions_use_pg_implementation = 'to_char,to_date,to_timestamp';可支援所有時間。

        說明

        使用該GUC參數後，查詢效能約有50%的損失，升級至Hologres V1.1.42及以上版本後，約有20%的損失。

    • 參數說明

      • text_date：必填，需要轉換成時間戳記的字串。

      • format_mask：必填，轉換成時間戳記的格式。

  • 將時間戳記轉換為日期。

    TO_TIMESTAMP(DOUBLE PRECISION)

    說明

    從1970-01-01 00:00:00+00的秒數開始轉換。

• 傳回值說明

  返回TIMESTAMPTZ類型。

• 使用樣本：

  • 字串轉換為時間。

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

    返回結果如下。

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

  • 將字串類型轉換為時間戳記。

    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;

    返回結果如下。

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

  • 秒級時間戳記的轉換。

    --返回結果：1975-03-06 03:38:16+08
    SELECT TO_TIMESTAMP(163280296);

  • 毫秒級時間戳記的轉換。

    --返回結果：2021-09-28 12:22:41+08
    SELECT TO_TIMESTAMP(1632802961000/1000);

MAKE_DATE

• 描述：使用年、月、日建立日期，預設支援的時間範圍為1925~2282年。

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

• 使用說明

  Hologres V2.0.29起支援該函數，暫不支援常量入參。

• 傳回值說明

  返回DATE類型。

• 樣本

  --返回結果：2013-07-15
  SELECT MAKE_DATE(2013, 7, 15);

  返回結果如下。

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

DATEADD

• 描述：根據指定的單位和數值，進行日期的增減運算，預設支援的時間範圍為1925~2282年。

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

• 使用說明

  目前如下Hologres執行個體版本支援使用該函數：

  • V2.0.31至V2.1.0版本。

  • V2.1.13及以上版本。

  說明

  且暫不支援常量入參寫入。

• 參數說明

  • d：必填，原始的日期或時間值。

  • num：必填，增加或減少的數量。

  • str：必填，指定的時間單位，包括：年（yyyy、year）、月（mm、month、mon）、日（dd、day）、時（hh、hour）、分（mi）、秒（ss）。

• 傳回值說明

  返回DATE、TIMESTAMP、TIMESTAMPTZ類型。

• 樣本

  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;

  返回結果如下。

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

ADD_MONTHS

• 描述：為指定日期增加月份。該函數是Oracle相容函數，需要先安裝orafce擴充，具體請參見Oracle相容函數。

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

• 參數說明

  • d：必填，原始日期。

  • month：必填，要添加到原始日期的整數。

• 傳回值說明

  返回DATE類型。

• 樣本

  SELECT ADD_MONTHS(current_date, 2);

  返回結果如下。

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

DATEDIFF

• 描述：計算兩個日期或時間戳記之間的時間差，預設支援的時間範圍為1925~2282年。

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

• 使用說明

  目前如下Hologres執行個體版本支援使用該函數：

  • V2.0.31至V2.1.0版本。

  • V2.1.13及以上版本。

  說明

  不支援全部入參均為常量。

• 參數說明

  • d1：必填，第一個日期或時間戳記值。

  • d2：必填，第二個日期或時間戳記值。

  • str：必填，指定計算時間差的時間單位，包括：年（yyyy、year）、月（mm、month、mon）、日（dd、day）、時（hh、hour）、分（mi）、秒（ss）。

• 傳回值說明

  返回BIGINT類型，在指定的單位下時間差值不足1時，預設返回為0。

  例如：2023-12-31與2024-01-01在年（year）單位下差值為0。

  說明

  若需上述情境返回1，可在SQL前執行set hg_experimental_datediff_use_presto_impl = off;關閉該GUC參數。

• 樣本

  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;

  返回結果如下。

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

MONTHS_BETWEEN

• 描述：計算兩個日期之間相差的月數。該函數是Oracle相容函數，需要先安裝orafce擴充，具體請參見Oracle相容函數。

  MONTHS_BETWEEN(DATE, DATE)

• 傳回值說明

  返回INT類型。

• 樣本

  • 樣本1

    --返回結果：2
    SELECT MONTHS_BETWEEN('2022-01-01', '2021-11-01');

  • 樣本2

    --返回結果：-2
    SELECT MONTHS_BETWEEN('2021-11-01', '2022-01-01');

NEXT_DAY

• 描述：計算指定日期後的下一個特定星期幾所在的日期。該函數是Oracle相容函數，需要先安裝orafce擴充，具體請參見Oracle相容函數。

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

• 參數說明

  • d：必填，指定日期。

  • str：必填，表示星期幾的英文字串，如Friday。也可以是表示星期幾的數字，取值範圍為1~7，其中1代表星期日，2代表星期一，以此類推。

• 傳回值說明

  返回DATE類型。

• 樣本

  • 樣本1

    --返回結果：2022-05-06
    SELECT NEXT_DAY('2022-05-01', 'FRIDAY');

  • 樣本2

    --返回結果：2022-05-06
    SELECT NEXT_DAY('2022-05-01', 5);

時間日期相加：+

SELECT date '2001-09-28' + integer '7';

SELECT current_date+ integer '3 ';

SELECT to_char(current_date+ interval '1 day','yyyy-mm-dd');

SELECT date '2001-09-28' + time '03:00';

SELECT date '2001-09-28' + interval '1 hour';

SELECT now()+interval '1 day';

SELECT now()+interval '1 month';

 SELECT now()+interval '2 year';

時間日期相減：-

SELECT date '2001-10-01' - date '2001-09-28';

SELECT date '2001-10-01' - integer '7';

SELECT date '2001-09-28' - time '03:00';

 SELECT date '2001-09-28' - interval '1 hour';

SELECT now()-interval '2 day';

時間日期相乘：*

SELECT 21 * interval '3 day';

時間日期相除：/

SELECT interval '1 hour' / double precision '1.5';

時間日期截取函數

CURRENT_DATE

• 描述：擷取當前日期。

  CURRENT_DATE

• 傳回值說明

  返回DATE類型。

• 樣本

  SELECT CURRENT_DATE;

  返回結果如下。

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

CURRENT_TIMESTAMP

• 描述：擷取當前事務的開始時刻，等效於TRANSACTION_TIMESTAMP和NOW。

  CURRENT_TIMESTAMP

  說明

  在事務的整個運行周期內不改變。

• 傳回值說明

  返回TIMESTAMPTZ類型。

• 樣本

  SELECT CURRENT_TIMESTAMP;

  返回結果如下。

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

CLOCK_TIMESTAMP

• 描述：擷取當前時刻。

  clock_timestamp()

  說明

  在同一條命令中也會發生變化。

• 傳回值說明

  返回TIMESTAMPTZ類型。

• 樣本

  SELECT clock_timestamp();

  返回結果如下。

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

LOCALTIMESTAMP

• 描述：擷取不包含時區的目前時間。

  LOCALTIMESTAMP

• 傳回值說明

  返回TIMESTAMPTZ類型。

• 樣本

  SELECT LOCALTIMESTAMP;

  返回結果如下。

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

NOW

• 描述：擷取當前事務的開始時刻，等效於TRANSACTION_TIMESTAMP和CURRENT_TIMESTAMP。

  NOW()

  說明

  在事務的整個運行周期內不改變。

• 傳回值說明

  返回TIMESTAMPTZ類型。

• 樣本

  SELECT NOW();

  返回結果如下。

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

STATEMENT_TIMESTAMP

• 描述：擷取當前語句的開始時刻。

  STATEMENT_TIMESTAMP()

  說明

  在事務的不同命令中傳回值不同。

• 傳回值說明

  返回TIMESTAMPTZ類型。

• 樣本

  SELECT STATEMENT_TIMESTAMP();

  返回結果如下。

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

TIMEOFDAY

• 描述：擷取當前時刻，類似於CLOCK_TIMESTAMP。但TIMEOFDAY函數返回的時間是以格式化文本字串的形式呈現。

  TIMEOFDAY()

• 傳回值說明

  返回TEXT類型。

• 樣本

  SELECT TIMEOFDAY();

  返回結果如下。

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

TRANSACTION_TIMESTAMP

• 描述：擷取當前事務的開始時刻，等效於CURRENT_TIMESTAMP和NOW。

  TRANSACTION_TIMESTAMP()

  說明

  在事務的整個運行周期內不改變。

• 傳回值說明

  返回TIMESTAMPTZ類型。

• 樣本

  SELECT TRANSACTION_TIMESTAMP();

  返回結果如下。

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

ISFINITE

• 描述：

  • 判斷DATE類型的日期是否為有限值，即非無窮大或非無窮小。

    ISFINITE(DATE)

  • 判斷時間戳記是否為有限值。

    ISFINITE(TIMESTAMP)

• 傳回值說明

  返回BOOLEAN類型。如果是有限值，則返回true（t），如果是無限值，則返回false（f）。

• 使用樣本

  • 樣本1

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

    返回結果如下。

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

  • 樣本2

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

    返回結果如下。

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