Hologres兼容PostgreSQL，支持使用标准的PostgreSQL语法进行开发。本文为您介绍Hologres已支持的时间和日期函数列表及使用用例。

Hologres已支持的时间和日期函数列表如下。当前Hologres版本支持的函数是PostgreSQL的一个子集，函数的使用方法请参见 时间和日期函数 和 时间格式化函数 。

时间日期转换函数

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
20221208
    将时间戳转换为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 valuse ('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

时间日期加减乘除函数

日期/时间相加： +

返回类型 用例 结果
DATE 从指定日期开始向前加7天，返回日期类型 
select date '2001-09-28' + integer '7';
 2001-10-05
当前日期向前加3天，返回日期类型 
select current_date+ integer '3 ';
 2022-12-10
当前时间向前加1天，返回日期类型 
select to_char(current_date+ interval '1 day','yyyy-mm-dd');
 2022-12-09
TIMESTAMP 从指定日期开始向前加3个小时（0点开始计算），返回timestamp类型 
select date '2001-09-28' + time '03:00';
 2001-09-28 03:00:00
从指定日期开始向前加1个小时（0点开始计算），返回timestamp类型 
select date '2001-09-28' + interval '1 hour';
 2001-09-28 01:00:00
TIMESTAMPTZ 当前时间向前加1天，返回timestamptz类型 
select now()+interval '1 day';
 2022-12-08 20:09:19.388465+08
当前时间向前加1个月，返回timestamptz类型 
select now()+interval '1 month';
 2023-01-08 20:21:50.993481+08
当前时间向前加2年，返回timestamptz类型 
 select now()+interval '2 year';
 2024-12-08 20:22:49.416343+08

日期/时间相减： -

返回类型 用例 结果
INTEGER 指定日期相减，返回integer类型 
select date '2001-10-01' - date '2001-09-28';
 3
DATE 指定日期减7天，返回日期类型 
select date '2001-10-01' - integer '7';
 2001-09-24
TIMESTAMP 指定日期减3个小时 
select date '2001-09-28' - time '03:00';
 2001-09-27 21:00:00
指定日期减1个小时 
 select date '2001-09-28' - interval '1 hour';
 2001-09-27 23:00:00
当前时间减2天 
select now()-interval '2 day';
 2022-12-06 20:27:21.094258+08

日期/时间相乘： *

返回类型 用例 结果
INTERVAL 时间相乘 
select 21 * interval '3 day';
 0 years 0 mons 63 days 0 hours 0 mins 0.0 secs

日期/时间相除： /

返回类型 用例 结果
INTERVAL 时间相除 
select interval '1 hour' / double precision '1.5';
 0 years 0 mons 0 days 0 hours 40 mins 0.0 secs

当前日期/时间

函数名 返回类型 描述 用例 结果
current_date DATE 获取当前日期。 
select current_date;
 2020-05-03
current_timestamp TIMESTAMPTZ 获取当前事务的开始时刻。
说明 在事务的整个运行周期内不改变。 
select current_timestamp;
 2020-05-03 06:33:36.113682+08
clock_timestamp() TIMESTAMPTZ 获取当前时刻。
说明 在同一条命令中也会发生变化。 
select clock_timestamp();
 2020-05-03 06:32:28.814918+08
localtimestamp TIMESTAMP 获取不包含时区的当前时间。 
select localtimestamp;
 2020-08-21 12:02:21.178031
now() TIMESTAMPTZ 获取当前事务的开始时刻，等效于 transaction_timestamp() 。
说明 在事务的整个运行周期内不改变。 
select now();
 2020-05-03 06:38:48.492168+08
statement_timestamp() TIMESTAMPTZ 获取当前语句的开始时刻。
说明 在事务的不同命令中返回值不同。 
select statement_timestamp();
 2020-05-05 06:39:11.125957+08
timeofday() TEXT 获取当前时刻。
说明 与 clock_timestamp() 类似，但时间使用格式化文本字符串格式。 
select timeofday();
 Tue May 03 06:39:43.195368 2020 CST
transaction_timestamp() TIMESTAMPTZ 获取当前事务的开始时刻，等效于 current_timestamp 。
说明 在事务的整个运行周期内不改变。 
select transaction_timestamp();
 2020-05-03 06:40:08.023623+08

时间日期截取函数

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('20221011', 'YYYY-MM-DD'));
    		 
    41
    从1月1日到指定日期的月数 
    select date_part('month', to_date('20221011', '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)

  • 返回类型：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