实时数仓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