实时数仓Hologres：时间和日期转换函数

时间日期转换函数

to_char(timestamp/timestamptz, text)

• 返回类型：TEXT

• 描述：

  将时间戳转换为字符串，默认支持时间范围为1925~2282年。

  • YYYY对应年，MM对应月，DD对应日，HH对应时，MI对应分，SS对应秒。

  • 可以使用to_char函数进行24小时制和12小时制的转换，HH24对应24小时制，HH12对应12小时制，默认为12小时制。

  • 从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%的损失。

• 使用示例：

  示例场景	示例请求	返回结果
  将时间戳转换为24小时制	SELECT to_char(current_timestamp, 'HH24:MI:SS'); SELECT to_char(current_timestamp, 'YYYY-MM-DD');	18:26:33 2022-12-08
  将时间戳转换为12小时制	SELECT to_char(current_timestamp, 'HH12:MI:SS AM'); SELECT to_char(time '00:30:00', 'HH12:MI:SS AM');	18:26:33 PM 12:30:00 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'); SELECT to_char(b, 'HH24:MI:SS') FROM time_test; --将text的字段转换为日期 SELECT to_char(to_timestamp(a, 'YYYY-MM-DD'),'YYYY-MM-DD')FROM time_test;	10:23:54 2001-09-28
  跨时区的时间戳转换	CREATE TABLE timestamptz_test( a TIMESTAMPTZ); INSERT INTO timestamptz_test VALUES ('2023-03-21 10:23:54+02'); --不指定时区，则默认将a转为系统时区（东八区）后转换为字符串 SELECT to_char(a, 'YYYY-MM-DD HH24:MI:SS') FROM timestamptz_test; --指定时区为美东时区后转换为字符串 SELECT to_char(a at time zone 'US/Eastern', 'YYYY-MM-DD HH24:MI:SS') FROM timestamptz_test;	2023-03-21 16:23:54 2023-03-21 04:23:54

to_char(int, text)

• 返回类型：TEXT

• 描述：

  将整数转换为字符串。

• 使用示例

  请求命令	返回结果
  SELECT to_char(125, '999');	125

to_char(double precision, text)

• 返回类型：TEXT

• 描述：

  将实数或双精度数转换为字符串。

• 使用示例

  请求命令	返回结果
  SELECT to_char(125.8::real, '999D9');	125.8

to_date(text, text)

• 返回类型：DATE

• 描述：

  将字符串转换为日期，默认支持时间范围为1925~2282年。

  从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%的损失。

• 使用示例：

  示例场景	示例请求	返回结果
  字符串转换为日期	SELECT to_date('05 Dec 2000', 'DD Mon YYYY'); SELECT to_date('2001 03 24', 'YYYY-MM-DD');	2000-12-05 2001-03-24
  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;	2001-09-28

to_timestamp(text, text)

• 返回类型：TIMESTAMPTZ

• 描述：

  将字符串转换为时间戳，默认支持时间范围为1925~2282年。

  • 执行结果包含+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%的损失。

• 使用示例：

  示例场景	示例请求	返回结果
  字符串转换为时间	SELECT to_timestamp('05 Dec 2000', 'DD Mon YYYY');	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;	2001-01-09 00:00:00+08

to_timestamp(double precision)

• 返回类型：TIMESTAMPTZ

• 描述：

  将时间戳转换为日期。

  说明

  从1970-01-01 00:00:00+00的秒数开始转换。

• 使用示例：

  示例场景	示例请求	返回结果
  时间戳为秒的转换	SELECT to_timestamp(163280296);	1975-03-06 03:38:16+08
  时间戳为毫秒的转换	SELECT to_timestamp(1632802961000/1000);	2021-09-28 12:22:41+08

时间日期加减乘除函数

按指定的单位和幅度计算日期/时间

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;

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;

日期/时间相加：+

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

当前日期/时间

SELECT current_date;

SELECT current_timestamp;

SELECT clock_timestamp();

SELECT localtimestamp;

SELECT now();

SELECT statement_timestamp();

SELECT timeofday();

SELECT transaction_timestamp();

时间日期截取函数

last_day(date/timestamp/timestamptz)

• 返回类型：DATE

• 描述：

  返回该日期所在月份的最后一天日期。

  • 仅支持1925-2282范围内的时间。

  • Hologres V2.0.31及以上版本支持该函数，暂不支持常量入参。

• 使用示例

  示例场景	请求示例	返回结果
  获取2004-02-28 00:00:00所在月份的最后一天日期	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;	2004-02-29

extract(field FROM timestamp)

• 返回类型：DOUBLE PRECISION

• 描述：

  从时间戳中获取子字段。

  说明

  输入的field常量值包括century、day、decade、dow（一周中的第几天，周日为0）、isodow（一周中的第几天，周日为7）、doy（一年中的第几天）、epoch、hour、minute、month、quarter、second、week、year等。

• 使用示例

  示例场景	请求示例	返回结果
  获取指定日期的小时数	SELECT extract(hour FROM timestamp '2001-02-16 20:38:40');	20
  获取当前时间的分钟数	SELECT extract(minute FROM now());	55
  带表字段的计算	CREATE TABLE time_test(a text); INSERT INTO time_test values ('2001-09-28 03:00:00'); --1970年到字段值时间的秒数 SELECT extract(epoch FROM to_timestamp(a, 'YYYY-MM-DD')) FROM time_test;	978969600

date_part(text, timestamp)

• 返回类型：DOUBLE PRECISION

• 描述：

  从时间戳中获取子字段，等效于extract(field FROM timestamp)。

  说明

  输入的field常量值包括century、day、decade、dow（一周中的第几天，周日为0）、isodow（一周中的第几天，周日为7）、doy（一年中的第几天）、epoch、hour、minute、month、quarter、second、week、year等。

• 使用示例

  示例场景	请求示例	返回结果
  获取指定日期的小时数	SELECT date_part('hour', timestamp '2001-02-16 16:38:40');	16
  从1月1日到指定日期的周数	SELECT date_part('week', to_date('2022-10-11', 'YYYY-MM-DD'));	41
  从1月1日到指定日期的月数	SELECT date_part('month', to_date('2022-10-11', 'YYYY-MM-DD'));	10

date_trunc(text,time/timestamp/timestamptz)

• 返回类型：TIMESTAMP/TIMESTAMPTZ

• 描述：

  截断时间戳到指定精度。

  说明

  输入的text常量值包括century、decade、year、quarter、month、week、day、hour、minute、second等。

• 使用示例

  示例场景	请求示例	返回结果
  截断指定时间戳到小时	SELECT date_trunc('hour', time '12:38:40');	0 years 0 mons 0 days 12 hours 0 mins 0.0 secs
  截断指定时间戳到天	SELECT date_trunc('day', timestamptz'2001-02-16 20:38:40+08');	2001-02-16 00:00:00+08
  截断指定时间戳到月	SELECT date_trunc('month', timestamp '2001-02-16 18:38:40');	2001-02-01 00:00:00
  每月1号 12点	SELECT date_trunc('month',now()) +interval '12h';	2022-12-01 12:00:00+08
  每天9点	SELECT date_trunc('day',now()) + interval '9h';	2022-12-09 09:00:00+08
  每周的今天	SELECT date_trunc('day',now()) + interval '7d';	2022-12-16 00:00:00+08
  每30分钟	SELECT date_trunc('minute',now()) + interval '30minute 30second';	2022-12-09 11:55:30+08

isfinite(date)

• 返回类型：BOOLEAN

• 描述

  判断日期是否为有限值，有限值返回true，无限值返回false。

• 使用示例

  请求示例	返回结果
  SELECT isfinite(date '2001-02-16');	true

isfinite(timestamp)

• 返回类型：BOOLEAN

• 描述

  判断时间戳是否为有限值，有限值返回true，无限值返回false。

• 使用示例

  请求示例	返回结果
  SELECT isfinite(timestamp '2001-02-16 21:28:30');	true

make_date(year int, month int, day int)

• 仅支持1925-2282范围内的时间

• Hologres V2.0.29起支持该函数，暂不支持常量入参

• 返回类型：DATE

• 描述

  使用年、月、日创建日期。

• 使用示例：

  请求示例	返回结果
  SELECT make_date(2013, 7, 15);	2013-07-15

常用SQL示例

• N小时内的计算。

  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

• 时间戳转换timestamp。

  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

• 整数相除场景

  两个整数相除有余数的场景中，Hologres会返回整数舍弃余数。示例：10/3，结果是3，如果需要显示余数，需要显式做类型转换，转成float再计算，如下所示：

  SELECT 10/3::float;

  运行结果：

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