«

PostgreSQL的日期时间差DATEDIFF怎么使用

时间：2024-7-13 14:22     作者：韩俊     分类： Mysql

本文小编为大家详细介绍“PostgreSQL的日期时间差DATEDIFF怎么使用”，内容详细，步骤清晰，细节处理妥当，希望这篇“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：

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

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

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

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

    PostgreSQL：

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

    PostgreSQL-月中的日期差异

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

    SQL Server：

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

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

    PostgreSQL：

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

    PostgreSQL-日期的天数差异

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

    SQL Server：

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

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

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

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

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

    PostgreSQL：

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

    PostgreSQL-周中的日期差异

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

    SQL Server：

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

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

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

    PostgreSQL：

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

    PostgreSQL-日期时间的小时差异

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

    SQL Server：

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

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

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

    PostgreSQL：

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

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

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

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

    SQL Server：

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

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

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

    PostgreSQL：

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

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

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

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

    SQL Server：

      -- Difference between Dec 30, 2011 08:54:55 and  Dec 30, 2011 08:56:10 in seconds
  SELECT DATEDIFF(second, '2011-12-30 08:54:55', '2011-12-30 08:56:10');
  -- Result: 75
 
  -- Time only
  SELECT DATEDIFF(second, '08:54:55', '08:56:10');
  -- Result: 75

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

    PostgreSQL：

    -- Difference between Dec 30, 2011 08:54:55 and  Dec 30, 2011 08:56:10 in seconds
  SELECT ((DATE_PART('day', '2011-12-30 08:56:10'::timestamp - '2011-12-30 08:54:55'::timestamp) * 24 +
                DATE_PART('hour', '2011-12-30 08:56:10'::timestamp - '2011-12-30 08:54:55'::timestamp)) * 60 +
                DATE_PART('minute', '2011-12-30 08:56:10'::timestamp - '2011-12-30 08:54:55'::timestamp)) * 60 +
                DATE_PART('second', '2011-12-30 08:56:10'::timestamp - '2011-12-30 08:54:55'::timestamp);
  -- Result: 75
 
  -- Time only
  SELECT (DATE_PART('hour', '08:56:10'::time - '08:54:55'::time) * 60 +
               DATE_PART('minute', '08:56:10'::time - '08:54:55'::time)) * 60 +
               DATE_PART('second', '08:56:10'::time - '08:54:55'::time);
  -- Result: 75

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

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

    PostgreSQL：

    CREATE OR REPLACE FUNCTION DateDiff (units VARCHAR(30), start_t TIMESTAMP, end_t TIMESTAMP)
     RETURNS INT AS $$
   DECLARE
     diff_interval INTERVAL;
     diff INT = 0;
     years_diff INT = 0;
   BEGIN
     IF units IN ('yy', 'yyyy', 'year', 'mm', 'm', 'month') THEN
       years_diff = DATE_PART('year', end_t) - DATE_PART('year', start_t);
 
       IF units IN ('yy', 'yyyy', 'year') THEN
         -- SQL Server does not count full years passed (only difference between year parts)
         RETURN years_diff;
       ELSE
         -- If end month is less than start month it will subtracted
         RETURN years_diff * 12 + (DATE_PART('month', end_t) - DATE_PART('month', start_t));
       END IF;
     END IF;
 
     -- Minus operator returns interval 'DDD days HH:MI:SS'
     diff_interval = end_t - start_t;
 
     diff = diff + DATE_PART('day', diff_interval);
 
     IF units IN ('wk', 'ww', 'week') THEN
       diff = diff/7;
       RETURN diff;
     END IF;
 
     IF units IN ('dd', 'd', 'day') THEN
       RETURN diff;
     END IF;
 
     diff = diff * 24 + DATE_PART('hour', diff_interval);
 
     IF units IN ('hh', 'hour') THEN
        RETURN diff;
     END IF;
 
     diff = diff * 60 + DATE_PART('minute', diff_interval);
 
     IF units IN ('mi', 'n', 'minute') THEN
        RETURN diff;
     END IF;
 
     diff = diff * 60 + DATE_PART('second', diff_interval);
 
     RETURN diff;
   END;
   $$ LANGUAGE plpgsql;

    如何使用 PostgreSQL DATEDIFF 函数

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

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

    PostgreSQL DATEDIFF 函数仅适用于 TIME

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

    CREATE OR REPLACE FUNCTION DateDiff (units VARCHAR(30), start_t TIME, end_t TIME)
     RETURNS INT AS $$
   DECLARE
     diff_interval INTERVAL;
     diff INT = 0;
   BEGIN
     -- Minus operator for TIME returns interval 'HH:MI:SS'
     diff_interval = end_t - start_t;
 
     diff = DATE_PART('hour', diff_interval);
 
     IF units IN ('hh', 'hour') THEN
       RETURN diff;
     END IF;
 
     diff = diff * 60 + DATE_PART('minute', diff_interval);
 
     IF units IN ('mi', 'n', 'minute') THEN
        RETURN diff;
     END IF;
 
     diff = diff * 60 + DATE_PART('second', diff_interval);
 
     RETURN diff;
   END;
   $$ LANGUAGE plpgsql;

    登录后复制

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

      -- Difference between 08:54:55 and 08:56:10 in seconds
  SELECT DATEDIFF('second', '08:54:55'::time, '08:56:10'::time);
  -- Result: 75

    标签: mysql PostgreSQL

    热门推荐

    1 mysql存储过程中的异常处理解析
    2 ASP.NET程序中的MySQL连接池使用及优化技巧
    3 node.js连接MongoDB数据库的2种方法教程
    4 PHP+MySQL之Insert Into数据插入用法分析
    5 如何使用MySQL的连接池优化数据库连接的性能
    6 Node.js与MySQL交互操作及其注意事项
    7 MongoDB性能篇之创建索引，组合索引，唯一索引，删除索引和explain执行计划
    8 navicat连接mysql时出现1045错误的解决方法
    9 MySql安装与配置方法(MySQL添加用户、删除用户与授权)
    10 MySQL中预处理语句prepare、execute与deallocate的使用教程

    日历

    标签

    hbase maven eclipse sybase PostgreSQL spring emcache oracle mongodb redis nginx golang java vue android ios mysql linux 微信小程序 css html php教程 javascript python php

    搜索

    最新文章

    热门文章