likes
comments
collection
share

PostgreSQL技术问答44 - Date&Time 日期和时间-下本文是Postgres时间功能特性相关讨论的下半

作者站长头像
站长
· 阅读数 34

本文是《PostgreSQL技术问答》系列文章中的一篇。关于这个系列的由来，可以参阅开篇文章：

文章的编号只是一个标识，在系列中没有明确的逻辑顺序和意义。读者进行阅读时，不用太关注这个方面。

本文是系列文章中，《PostgreSQL技术问答44 - Date&Time 日期和时间》 的下半部分。上半部分主要讨论了Postgres日期时间相关操作的基础内容，下半部分主要是一些扩展和高级的内容。

如何对时间进行格式化

所谓格式，就是将一个时间类型的数据，用一种规范的字符串形式表示出来，通常用于信息的呈现，让人类更容易理解。在Postgres中，有很多功能特性都可以实现这个需求。前面的extract是一种方式，我们可以将extract提取出来的不同的属性结果组合起来按照要求来呈现。还有一种比较常用的方式，就是to_char函数，它可以直接使用一个模板字符串来转换时间，下面是一些简单的示例代码：

-- 完整字符串
defaultdb=> SELECT TO_CHAR(current_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS formatted_time;
   formatted_time    
---------------------
 2024-09-03 02:10:21
(1 row)

-- 常用格式
defaultdb=> SELECT 
TO_CHAR(current_timestamp, 'YY-MM-DD') tdate,
TO_CHAR(current_timestamp, 'HH24:MI') AS ttime;
  tdate   | ttime 
----------+-------
 24-09-03 | 02:12
(1 row)

-- 错误信息
defaultdb=> SELECT TO_CHAR(TIMESTAMP '2023-10-32 14:30:00', 'HH24:MI') AS ttime;
ERROR:  date/time field value out of range: "2023-10-32 14:30:00"
LINE 1: SELECT TO_CHAR(TIMESTAMP '2023-10-32 14:30:00', 'HH24:MI') A...
                                 ^
HINT:  Perhaps you need a different "datestyle" setting.

这里需要注意，如果to_char或者to_timestamp，使用的时间戳字符串，是一个错误的时间，操作会抛出执行错误(value out of range)，而不是返回一个空值。

可以看到，to_char，其实就是前面提到的to_timestemp函数的逆运算，它们使用相同的模板字符串规范定义。常用的模板字符串定义如下：

  • YYYY：四位年份
  • YY：两位年份
  • MM：两位月份
  • DD：两位日期
  • HH24：24小时制小时
  • MI：两位分钟
  • SS：两位秒
  • AM/PM：上午或下午
  • Dy：星期缩写，如Tue，Sun等
  • Day：星期完整名称，如Tuesday，Sunday等
  • Mon：月份缩写名称，如Sep，Nov等

如何判断一个字符串是有效的时间

这也是一个常见的应用需求，就是在处理之前，需要先判断某种字符串形式的信息，确实是一个有效的时间表示方式，才能够进入下一步的处理。

遗憾的是，通过查询相关技术文档，Postgres似乎没有一种简单的方式，可以很直接的判断一个字符串形式的时间表示是有效的。当然开发者可以自行编写代码，利用转换操作时的错误抛出机制，来实现这一需求。下面是其参考实现：

-- 代码块
DO $$
BEGIN
    PERFORM TO_TIMESTAMP('2023-10-02 14:30:00', 'YYYY-MM-DD HH24:MI:SS');
    RAISE NOTICE 'The timestamp is valid.';
EXCEPTION
    WHEN others THEN
        RAISE NOTICE 'The timestamp is invalid.';
END $$;

-- 函数
CREATE OR REPLACE FUNCTION is_valid_timestamp(input_string TEXT, format TEXT)
RETURNS BOOLEAN AS $$
BEGIN
    PERFORM TO_TIMESTAMP(input_string, format);
    RETURN TRUE;
EXCEPTION
    WHEN others THEN RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

-- 函数调用

SELECT is_valid_timestamp('2023-10-02 14:30:00', 'YYYY-MM-DD HH24:MI:SS') AS is_valid;

什么是时间截断(Truncate)

时间截断，就是对时间在某一个维度上，进行向下的取整，就是所谓对离散的时间，进行“归一化”。这其实是一个非常常见的需求。我们先来看一些例子就更好理解了：

defaultdb=> with T(t) as (values ('2001-02-16 20:38:40+00'))
select date_trunc('day',t::timestamp) from T
union all 
select date_trunc('day',t::timestamptz, 'Australia/Sydney') from T
union all 
select date_trunc('year',t::timestamp) from T
union all 
select date_trunc('month',t::timestamp) from T
union all 
select date_trunc('hour',t::timestamp) from T
union all 
select date_trunc('minute',t::timestamp) from T
;
       date_trunc       
------------------------
 2001-02-16 00:00:00+00
 2001-02-16 13:00:00+00
 2001-01-01 00:00:00+00
 2001-02-01 00:00:00+00
 2001-02-16 20:00:00+00
 2001-02-16 20:38:00+00
(6 rows)

在示例中，我们可以看到，对时间进行截断，使用的是date_trunc这个函数。它可以将某个时间戳，截断并转换到一个“整”的时间点，而且这个维度是可以选择的，如年、月、日，甚至时、分等等，这些功能在对数据进行时间维度的分析的时候，是非常有用的。

什么是时间分组

在查阅Postgres技术文档的过程中，笔者又发现了一个时间处理函数data_bin。笔者以前也没有接触和使用过这个函数，从名字和直观上看，对于中国人而言，这个名字取得真是不知所以。技术文档和文档中給的例子，也不是很清晰易懂。直到请教了Mistral后，笔者才发现，这绝对是一个被低估的函数。先看看它给的示例(笔者稍作了改进)：

defaultdb=> with T(e) as ( VALUES
    ('2023-10-01 08:30:00+00'),
    ('2023-10-01 09:15:00+00'),
    ('2023-10-01 09:45:00+00'),
    ('2023-10-01 10:00:00+00'),
    ('2023-10-01 10:13:00+00'),
    ('2023-10-01 10:42:00+00')
)SELECT date_bin('1 hour', e::timestamp, '2023-10-01 00:30:00+00') AS hour_bin, COUNT(*)
FROM T GROUP BY 1 ORDER BY 1;
      hour_bin       | count 
---------------------+-------
 2023-10-01 08:30:00 |     2
 2023-10-01 09:30:00 |     3
 2023-10-01 10:30:00 |     1
(3 rows)

原来，它是date_tunc的增强版本！ 它可以将一个时间戳，基于起始时间和间隔，进行分组归一化。由于可以随意定义起始计算时间点，和间隔方式，它能够提供比时间截断更灵活和强大的归一方式。

在理解了这些之后，我们应该能够了解到，这里的bin，其实就是“桶”的意思，这个概念在数据领域也是挺常见的，就是将数据进行分组来处理。

Postgres时间操作为什么可以支持乘法和除法？

是的，Postgres中，对于时间的操作，是可以使用乘法(*)和除法(/)操作符的。但容易理解的是，它主要支持的是interval的类型，因为它本质上还是一个数值。这个功能特性，使SQL语句的编写更加容易理解和人格化。下面是一些简单例子：

defaultdb=> select Interval '1 month' / 3 ,  3 * Interval '2 week' ;
 ?column? | ?column? 
----------+----------
 10 days  | 42 days
(1 row)

如何判断两个时间的区间是否重叠

判断时间区间重叠，也是比较常见的时间应用的需求。Postgres提供了OVERLAPS语句，来执行这个操作，下面是一个简单的示例：

defaultdb=> SELECT 
(DATE '2001-02-16', DATE '2001-12-21') OVERLAPS (DATE '2001-10-30', DATE '2002-10-30'),
(DATE '2001-02-16', DATE '2001-08-21') OVERLAPS (DATE '2001-10-30', DATE '2002-10-30'),
(DATE '2001-02-16', INTERVAL '100 days') OVERLAPS (DATE '2001-10-30', DATE '2002-10-30'),
(DATE '2001-02-16', INTERVAL '300 days') OVERLAPS (DATE '2001-10-30', DATE '2002-10-30');
 overlaps | overlaps | overlaps | overlaps 
----------+----------+----------+----------
 t        | f        | f        | t

这里的要点如下：

  • OVERLAPS子句，前后分别有一个时间区段的参数
  • 时间区段，可以使用起始时间和终结时间来表示
  • 也可以使用起始时间，加时间间隔来表示
  • 计算和返回值是真或者假，表示这两个时间区段是否有重叠
  • 理论上两个参数位置可以互换，不影响判断结果

isfinite这个函数是做什么用的

坦率的说，笔者也不是特别理解。我们先来看看它应用的例子：

defaultdb=>  SELECT 
isfinite(date '2002-09-17'), 
isfinite(timestamp '2002-09-17 19:27:45'),
isfinite(interval '7 hours');
 isfinite | isfinite | isfinite 
----------+----------+----------
 t        | t        | t
(1 row)

finite是“有限”的意思，那么isfinite就是判断一个数值是否有限。按照技术文档的说法，它可以用于判断数据是否是Infinity、-Infinity或者非数值NaN。但笔者不是很理解它用于时间处理的意义，所有的有效时间，不都是可以转换为一个有限的数值吗？ 但如果是无效的时间戳字符串，处理时就会抛出错误，而不是返回false。

在Postgres中，如何利用时间来控制操作和执行

Postgres提供了pg_sleep()和相关函数，可以在SQL代码(通常是函数或者过程中)，延迟代码的执行。实际上就是提供了一种基于时间的控制机制，来操作程序的执行。

-- 休眠，单位为秒
SELECT pg_sleep(1.5);

-- 休眠，字符串intervel
SELECT pg_sleep_for('5 minutes');

-- 休眠，截至时间
SELECT pg_sleep_until('tomorrow 03:00');

笔者想了一下，大概有下面几个场景，需要使用这些控制机制：

  • 定期执行

pg_sleep配合loop，可以达成定期执行的效果。主要问题是这个任务如何启动，并且维持这个会话。维持会话，是否会带来一些负面的影响，如进程或者事务阻塞，或者过多资源占用等等。

  • 将来执行

pg_sleep_until，可以确保无论何时调用，实际的执行，都是在一个特定的时间点之后。

  • 模拟随机操作

通过使用随机的休眠时间参数来调用pg_sleep，可以模拟一种随机操作的场景。

  • 节流

使用pg_sleep，强行降低代码执行的节奏，实现节流操作的效果。

小结

本文是Postgres时间功能特性相关讨论的下半部分。涉及时间处理的相关扩展和高级操作，如格式化和格式模板，时间截断，对时间进行分组，判断时间区间是否重叠，和使用时间控制执行等方面的内容。

转载自:https://juejin.cn/post/7411168518753239081
评论
请登录