• PostgreSQL 的时间差DATEDIFF

    PostgreSQL-DATEDIFF-日期时间差，以秒，天，月，周等为单位

    您可以使用各种日期时间表达式或用户定义的 DATEDIFF 函数（UDF）在 PostgreSQL 中计算两个日期时间值之间的差，以秒，分钟，小时，天，周，月和年为单位。

    总览

    PostgreSQL 不提供类似于 SQL Server DATEDIFF 的[2] DATEDIFF 函数，但是您可以使用各种表达式或 UDF 来获得相同的结果。

    SQL Server
    and Sybase    		PostgreSQL
    YearsDATEDIFF(yy, start, end)DATE_PART('year', end) - DATE_PART('year', start)
    MonthsDATEDIFF(mm, start, end)years_diff * 12 + (DATE_PART('month', end) - DATE_PART('month', start))
    DaysDATEDIFF(dd, start, end)DATE_PART('day', end - start)
    WeeksDATEDIFF(wk, start, end)TRUNC(DATE_PART('day', end - start)/7)
    HoursDATEDIFF(hh, start, end)days_diff * 24 + DATE_PART('hour', end - start )
    MinutesDATEDIFF(mi, start, end)hours_diff * 60 + DATE_PART('minute', end - start )
    SecondsDATEDIFF(ss, start, end)minutes_diff * 60 + DATE_PART('minute', end - start )

    PostgreSQL-年中的日期差异

    考虑使用 SQL Server 函数来计算以年为单位的两个日期之间的差：

    SQL Server：

    1.   -- Difference between Oct 02, 2011 and Jan 01, 2012 in years
    2.   SELECT DATEDIFF(year, '2011-10-02', '2012-01-01');
    3.   -- Result: 1

    请注意，SQL Server DATEDIFF 函数返回 1 年，尽管日期之间只有 3 个月。

    SQL Server 不计算日期之间经过的整年，它仅计算年份之间的差异。

    在 PostgreSQL 中，您可以从日期中获取年份部分并将其减去。

    PostgreSQL：

    1.  -- Difference between Oct 02, 2011 and Jan 01, 2012 in years
    2.   SELECT DATE_PART('year', '2012-01-01'::date) - DATE_PART('year', '2011-10-02'::date);
    3.   -- Result: 1

    PostgreSQL-月中的日期差异

    考虑使用 SQL Server 函数来计算两个日期（以月为单位）之间的差额：

    SQL Server：

    1.   -- Difference between Oct 02, 2011 and Jan 01, 2012 in months
    2.   SELECT DATEDIFF(month, '2011-10-02', '2012-01-01');
    3.   -- Result: 3

    在 PostgreSQL 中，您可以将年份之间的差值乘以 12，然后将月份部分之间的差值相加（可以为负）。

    PostgreSQL：

    1. -- Difference between Oct 02, 2011 and Jan 01, 2012 in months
    2.   SELECT (DATE_PART('year', '2012-01-01'::date) - DATE_PART('year', '2011-10-02'::date)) * 12 +
    3.               (DATE_PART('month', '2012-01-01'::date) - DATE_PART('month', '2011-10-02'::date));
    4.   -- Result: 3

    PostgreSQL-日期的天数差异

    考虑使用 SQL Server 函数来计算两天之间的日期差：

    SQL Server：

    1.  -- Difference between Dec 29, 2011 23:00 and Dec 31, 2011 01:00 in days
    2.   SELECT DATEDIFF(day, '2011-12-29 23:00:00', '2011-12-31 01:00:00');
    3.   -- Result: 2

    请注意，DATEDIFF 返回了 2 天，尽管 datetime 值之间只有 1 天 2 小时。

    在 PostgreSQL 中，如果您从另一个中减去一个日期时间值（TIMESTAMP，DATE 或 TIME 数据类型），则将获得一个 INTERVAL 值，格式为“ ddd days hh：mi：ss ”。

    1.    SELECT '2011-12-31 01:00:00'::timestamp - '2011-12-29 23:00:00'::timestamp;
    2.     -- Result: "1 day 02:00:00"
    3.  
    4.     SELECT '2011-12-31 01:00:00'::timestamp - '2010-09-17 23:00:00'::timestamp;
    5.     -- Result: "469 days 02:00:00"

    所以，你可以使用 date_part 数函数 extact 的天数，但它返回的数量充分的日期之间的天数。

    PostgreSQL：

    1.  -- Difference between Dec 29, 2011 23:00 and Dec 31, 2011 01:00 in days
    2.   SELECT DATE_PART('day', '2011-12-31 01:00:00'::timestamp - '2011-12-29 23:00:00'::timestamp);
    3.   -- Result: 1

    PostgreSQL-周中的日期差异

    考虑使用 SQL Server 函数来计算两周中两个日期之间的差额：

    SQL Server：

    1. -- Difference between Dec 22, 2011 and Dec 31, 2011 in weeks
    2.   SELECT DATEDIFF(week, '2011-12-22', '2011-12-31');
    3.   -- Result: 1

    DATEDIFF 返回日期时间值之间的整周数。

    在 PostgreSQL 中，您可以使用表达式定义天数（请参见上文）并将其除以 7。需要 TRUNC 才能删除除后的小数部分。

    PostgreSQL：

    1.   -- Difference between Dec 22, 2011 and Dec 31, 2011 in weeks
    2.   SELECT TRUNC(DATE_PART('day', '2011-12-31'::timestamp - '2011-12-22'::timestamp)/7);
    3.   -- Result: 1

    PostgreSQL-日期时间的小时差异

    考虑使用 SQL Server 函数来计算两个 datetime 值之间的时差，以小时为单位：

    SQL Server：

    1.   -- Difference between Dec 30, 2011 08:55 and Dec 30, 2011 9:05 in weeks
    2.   SELECT DATEDIFF(hour, '2011-12-30 08:55', '2011-12-30 09:05');
    3.   -- Result: 1

    请注意，尽管 datetime 值之间只有 10 分钟的差异，但 DATEDIFF 返回了 1 小时。

    在 PostgreSQL 中，您可以使用表达式来定义天数（请参见上文），乘以 24 并乘以小时。

    PostgreSQL：

    1.  -- Difference between Dec 30, 2011 08:55 and Dec 30, 2011 9:05 in weeks
    2.   SELECT DATE_PART('day', '2011-12-30 08:55'::timestamp - '2011-12-30 09:05'::timestamp) * 24 +
    3.               DATE_PART('hour', '2011-12-30 08:55'::timestamp - '2011-12-30 09:05'::timestamp);
    4.   -- Result: 0

    请注意，此 PostreSQL 表达式返回在 datetime 值之间传递的完整小时数。

    PostgreSQL-分钟中的日期时间差异

    考虑使用 SQL Server 函数以分钟为单位计算两个日期时间值之间的差：

    SQL Server：

    1.   -- Difference between Dec 30, 2011 08:54:55 and  Dec 30, 2011 08:56:10 in minutes
    2.   SELECT DATEDIFF(minute, '2011-12-30 08:54:55', '2011-12-30 08:56:10');
    3.   -- Result: 2
    4.  
    5.   -- Time only
    6.   SELECT DATEDIFF(minute, '08:54:55', '08:56:10');
    7.   -- Result: 2

    请注意，尽管 datetime 值之间只有 1 分 15 秒，但 DATEDIFF 返回了 2 分钟。

    在 PostgreSQL 中，您可以使用一个表达式来定义小时数（请参阅上文），乘以 60 并乘以分钟。

    PostgreSQL：

    1. -- Difference between Dec 30, 2011 08:54:55 and  Dec 30, 2011 08:56:10 in minutes
    2.   SELECT (DATE_PART('day', '2011-12-30 08:56:10'::timestamp - '2011-12-30 08:54:55'::timestamp) * 24 +
    3.                DATE_PART('hour', '2011-12-30 08:56:10'::timestamp - '2011-12-30 08:54:55'::timestamp)) * 60 +
    4.                DATE_PART('minute', '2011-12-30 08:56:10'::timestamp - '2011-12-30 08:54:55'::timestamp);
    5.   -- Result: 1
    6.  
    7.   -- Time only
    8.   SELECT DATE_PART('hour', '08:56:10'::time - '08:54:55'::time) * 60 +
    9.               DATE_PART('minute', '08:56:10'::time - '08:54:55'::time);
    10.   -- Result: 1

    请注意，这些 PostreSQL 表达式返回在 datetime 值之间传递的完整分钟数。

    PostgreSQL-日期时间差（以秒为单位）

    考虑使用 SQL Server 函数以秒为单位计算两个日期时间值之间的差：

    SQL Server：

    1.   -- Difference between Dec 30, 2011 08:54:55 and  Dec 30, 2011 08:56:10 in seconds
    2.   SELECT DATEDIFF(second, '2011-12-30 08:54:55', '2011-12-30 08:56:10');
    3.   -- Result: 75
    4.  
    5.   -- Time only
    6.   SELECT DATEDIFF(second, '08:54:55', '08:56:10');
    7.   -- Result: 75

    在 PostgreSQL 中，您可以使用表达式定义分钟数（请参见上文），乘以 60 并乘以秒。

    PostgreSQL：

    1. -- Difference between Dec 30, 2011 08:54:55 and  Dec 30, 2011 08:56:10 in seconds
    2.   SELECT ((DATE_PART('day', '2011-12-30 08:56:10'::timestamp - '2011-12-30 08:54:55'::timestamp) * 24 +
    3.                 DATE_PART('hour', '2011-12-30 08:56:10'::timestamp - '2011-12-30 08:54:55'::timestamp)) * 60 +
    4.                 DATE_PART('minute', '2011-12-30 08:56:10'::timestamp - '2011-12-30 08:54:55'::timestamp)) * 60 +
    5.                 DATE_PART('second', '2011-12-30 08:56:10'::timestamp - '2011-12-30 08:54:55'::timestamp);
    6.   -- Result: 75
    7.  
    8.   -- Time only
    9.   SELECT (DATE_PART('hour', '08:56:10'::time - '08:54:55'::time) * 60 +
    10.                DATE_PART('minute', '08:56:10'::time - '08:54:55'::time)) * 60 +
    11.                DATE_PART('second', '08:56:10'::time - '08:54:55'::time);
    12.   -- Result: 75

    PostgreSQL DATEDIFF-用户定义函数（UDF）

    除了使用单独的表达式来计算每个时间单位的日期时间差之外，还可以使用类似于 SQL Server DATEDIFF 函数的函数。

    PostgreSQL：

    1. CREATE OR REPLACE FUNCTION DateDiff (units VARCHAR(30), start_t TIMESTAMP, end_t TIMESTAMP)
    2.      RETURNS INT AS $$
    3.    DECLARE
    4.      diff_interval INTERVAL;
    5.      diff INT = 0;
    6.      years_diff INT = 0;
    7.    BEGIN
    8.      IF units IN ('yy', 'yyyy', 'year', 'mm', 'm', 'month') THEN
    9.        years_diff = DATE_PART('year', end_t) - DATE_PART('year', start_t);
    10.  
    11.        IF units IN ('yy', 'yyyy', 'year') THEN
    12.          -- SQL Server does not count full years passed (only difference between year parts)
    13.          RETURN years_diff;
    14.        ELSE
    15.          -- If end month is less than start month it will subtracted
    16.          RETURN years_diff * 12 + (DATE_PART('month', end_t) - DATE_PART('month', start_t));
    17.        END IF;
    18.      END IF;
    19.  
    20.      -- Minus operator returns interval 'DDD days HH:MI:SS'
    21.      diff_interval = end_t - start_t;
    22.  
    23.      diff = diff + DATE_PART('day', diff_interval);
    24.  
    25.      IF units IN ('wk', 'ww', 'week') THEN
    26.        diff = diff/7;
    27.        RETURN diff;
    28.      END IF;
    29.  
    30.      IF units IN ('dd', 'd', 'day') THEN
    31.        RETURN diff;
    32.      END IF;
    33.  
    34.      diff = diff * 24 + DATE_PART('hour', diff_interval);
    35.  
    36.      IF units IN ('hh', 'hour') THEN
    37.         RETURN diff;
    38.      END IF;
    39.  
    40.      diff = diff * 60 + DATE_PART('minute', diff_interval);
    41.  
    42.      IF units IN ('mi', 'n', 'minute') THEN
    43.         RETURN diff;
    44.      END IF;
    45.  
    46.      diff = diff * 60 + DATE_PART('second', diff_interval);
    47.  
    48.      RETURN diff;
    49.    END;
    50.    $$ LANGUAGE plpgsql;

    如何使用 PostgreSQL DATEDIFF 函数

    语法与 SQL Server DATEDIFF 相似，但是您必须在 PostgreSQL 中将时间单位（秒，分钟等及其缩写）指定为字符串文字，例如：

    1.    -- Difference between Dec 30, 2011 08:54:55 and  Dec 30, 2011 08:56:10 in seconds
    2.   SELECT DATEDIFF('second', '2011-12-30 08:54:55'::timestamp, '2011-12-30 08:56:10'::timestamp);
    3.   -- Result: 75

    PostgreSQL DATEDIFF 函数仅适用于 TIME

    您可以具有另一个仅对时间数据类型起作用的函数。PostgreSQL 支持具有相同名称但参数数据类型不同的重载函数：

    1. CREATE OR REPLACE FUNCTION DateDiff (units VARCHAR(30), start_t TIME, end_t TIME)
    2.      RETURNS INT AS $$
    3.    DECLARE
    4.      diff_interval INTERVAL;
    5.      diff INT = 0;
    6.    BEGIN
    7.      -- Minus operator for TIME returns interval 'HH:MI:SS'
    8.      diff_interval = end_t - start_t;
    9.  
    10.      diff = DATE_PART('hour', diff_interval);
    11.  
    12.      IF units IN ('hh', 'hour') THEN
    13.        RETURN diff;
    14.      END IF;
    15.  
    16.      diff = diff * 60 + DATE_PART('minute', diff_interval);
    17.  
    18.      IF units IN ('mi', 'n', 'minute') THEN
    19.         RETURN diff;
    20.      END IF;
    21.  
    22.      diff = diff * 60 + DATE_PART('second', diff_interval);
    23.  
    24.      RETURN diff;
    25.    END;
    26.    $$ LANGUAGE plpgsql;

    登录后复制

    例如，可以将此函数调用为：

    1.   -- Difference between 08:54:55 and 08:56:10 in seconds
    2.   SELECT DATEDIFF('second', '08:54:55'::time, '08:56:10'::time);
    3.   -- Result: 75
  • 相关阅读:
    spring是如何解决循环依赖的？
    2023年智能轨道，交通与运输工程国际会议(ICSTTE 2023)
    【linux】自定义nameserver
    Redisson批量操作类RBuckets和管道利器RBatch
    常见的100道java面试题及答案【java学习+面试指南】（二）
    【Mac】Listen 1 for Mac（最强的音乐搜索工具）软件介绍
    Java的Stream和Python的Pandas的对比
    中国大陆IP段（含港澳）【2024-04-25】APNIC lasted 2024-04-25
    Linux C : select简介和epoll 实现
    十年开发老手，深度解析企业用人标准为何越来越高？！
  • 原文地址：https://blog.csdn.net/qq_35222843/article/details/127927404