添加链接 注册    登录
link管理
链接快照平台
  • 输入网页链接，自动生成快照
  • 标签化管理网页链接
相关文章推荐
跑龙套的牛肉面  ·  一款强大的子域名收集工具OneForAll_ ...·  5 月前    · 
活泼的牛肉面  ·  金立游戏开发者平台·  6 月前    · 
豪气的瀑布  ·  Document migration ...·  6 月前    · 
温暖的凉茶  ·  《企业战士》第01集在线观看_高清全集无修_ ...·  6 月前    · 
玩命的炒粉  ·  苹果2020年第四财季营收高于预期 ...·  1 年前    · 
link管理  ›  PostgreSQL - DATEDIFF - Datetime Difference in Seconds, Days, Months, Weeks etc - SQLines Tools
date postgresql select 时间戳
https://www.sqlines.com/postgresql/how-to/datediff
想表白的金鱼
10 月前
Months DATEDIFF(mm, start, end) years_diff * 12 + (DATE_PART('month', end) - DATE_PART('month', start)) Days DATEDIFF(dd, start, end) DATE_PART('day', end - start) Weeks DATEDIFF(wk, start, end) TRUNC(DATE_PART('day', end - start)/7) Hours DATEDIFF(hh, start, end) days_diff * 24 + DATE_PART('hour', end - start ) Minutes DATEDIFF(mi, start, end) hours_diff * 60 + DATE_PART('minute', end - start ) Seconds DATEDIFF(ss, start, end) minutes_diff * 60 + DATE_PART('minute', end - start ) 
  -- Difference between Oct 02, 2011 and Jan 01, 2012 in years




    

  SELECT DATEDIFF(year, '2011-10-02', '2012-01-01');
  -- Result: 1
Note that SQL Server DATEDIFF function returned 1 year although there are only 3 months between dates. SQL Server does not count full years passed between the dates, it calculates the difference between the year parts only. In PostgreSQL, you can get the year parts from the dates and subtract them. 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 - Date Difference in Months 

  -- Difference between Oct 02, 2011 and Jan 01, 2012 in months
  SELECT DATEDIFF(month, '2011-10-02', '2012-01-01');
  -- Result: 3
In PostgreSQL, you can take the difference in years, multiply by 12 and add the difference between month parts that can be negative. 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 - Date Difference in Days 

  -- 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
Note that DATEDIFF returned 2 days, although there is only 1 day and 2 hours between the datetime values. In PostgreSQL, if you subtract one datetime value (TIMESTAMP, DATE or TIME data type) from another, you will get an INTERVAL value in the form ” 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"
So you can use DATE_PART function to extact the number of days, but it returns the number of full days between the dates. 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 - Date Difference in Weeks 

  -- Difference between Dec 22, 2011 and Dec 31, 2011 in weeks
  SELECT DATEDIFF(week, '2011-12-22', '2011-12-31');
  -- Result: 1
DATEDIFF returnes the number of full weeks between the datetime values. In PostgreSQL, you can use an expression to define the number of days (see above) and divide it by 7. TRUNC is required to remove the decimal part after the division. 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 - Datetime Difference in Hours 

  -- 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
Note that DATEDIFF returned 1 hour although there is just 10 minutes difference between the datetime values. In PostgreSQL, you can use an expression to define the number of days (see above), multiple by 24 and add the difference is hours. 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
Note that this PostreSQL expression returns the number of full hours passed between the datetime values. 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
Note that DATEDIFF returned 2 minutes although there is just 1 minute and 15 seconds between the datetime values. In PostgreSQL, you can use an expression to define the number of hours (see above), multiple by 60 and add the difference is minutes. 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
Note that these PostreSQL expressions return the number of full minutes passed between the datetime values. 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
In PostgreSQL, you can use an expression to define the number of minutes (see above), multiple by 60 and add the difference is seconds. 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 - User-Defined Function (UDF)

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

How to Use PostgreSQL DATEDIFF Function

The syntax is similar to SQL Server DATEDIFF, but you have to specify a time unit (second, minute etc. and their abbreviations) as a string literal in PostgreSQL, for example: 
    -- 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 Function for TIME Only

You can have another function that operates on time data types only. PostgreSQL supports overloaded functions having the same name, but different data types of parameters: 
   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;
For example, you can call this function as: 
  -- Difference between 08:54:55 and 08:56:10 in seconds
  SELECT DATEDIFF('second', '08:54:55'::time, '08:56:10'::time);
  -- Result: 75
For more information, see SQL Server to PostgreSQL Migration .
 
推荐文章
跑龙套的牛肉面  ·  一款强大的子域名收集工具OneForAll_oneforall工具-CSDN博客
5 月前
活泼的牛肉面  ·  金立游戏开发者平台
6 月前
豪气的瀑布  ·  Document migration process for SPA projects · Issue #44600 · dotnet/aspnetcore · GitHub
6 月前
温暖的凉茶  ·  《企业战士》第01集在线观看_高清全集无修_NT动漫
6 月前
玩命的炒粉  ·  苹果2020年第四财季营收高于预期 有望在下一季度创下历史新高_新浪财经_新浪网
1 年前
Link管理   ·   51好读   ·   Sov5搜索   ·   小百科
link管理 - 链接快照平台