[HN Gopher] PostgreSQL's Missing DateDiff Function
       ___________________________________________________________________
        
       PostgreSQL's Missing DateDiff Function
        
       Author : cedricd
       Score  : 11 points
       Date   : 2021-12-21 21:49 UTC (1 hours ago)
        
 (HTM) web link (www.narrator.ai)
 (TXT) w3m dump (www.narrator.ai)
        
       | Izkata wrote:
       | Isn't this what EXTRACT does? (Note the first paragraph that says
       | it works on "interval" types)
       | 
       | https://www.postgresql.org/docs/9.3/functions-datetime.html#...
       | 
       | Just did a search and got it from here, which shows it being used
       | on the difference between two dates:
       | https://stackoverflow.com/questions/24929735/how-to-calculat...
        
         | cldellow wrote:
         | No, for example, the datediff in years for New Year's Eve and
         | New Year's Day should be 1 (because it spans a year boundary),
         | but EXTRACT on the difference would give you 0:
         | select extract(year from '2021-01-01'::timestamptz -
         | '2020-12-31'::timestamptz);
        
           | cedricd wrote:
           | Yep. That's exactly why this function isn't trivial to write.
           | Boundaries are not obvious and native pg functions (insofar
           | as I'm aware of them) don't do these kinds of diffs.
           | 
           | Semantically the code has to diff days (for example) but be
           | aware that you crossed a year boundary (or any other).
        
         | cedricd wrote:
         | Those postgres docs are usually so dry but this is great
         | 
         | > The first century starts at 0001-01-01 00:00:00 AD, although
         | they did not know it at the time
        
         | Nullabillity wrote:
         | Doesn't look like that works correctly for smaller units. For
         | example:                   teo=> select extract(minutes from
         | interval '70 minutes');          date_part          -----------
         | 10         (1 row)
         | 
         | For a sum this should return 70 (since there are obviously 70
         | minutes in total). Instead, it gets normalized to 1 hour and 10
         | minutes, and returns the minute component only.
        
       | [deleted]
        
       | xupybd wrote:
       | This seems like a big yet simple feature that Postgresql is
       | missing. I've used this many times in SQL server.
       | 
       | I'm surprised there is not native function. Does anyone know why?
        
         | cedricd wrote:
         | I've always wondered myself. Maybe it's because it's mostly
         | useful for analytical workloads instead of operational ones.
         | 
         | Redshift, famously based off Postgres, chose to implement it
        
       ___________________________________________________________________
       (page generated 2021-12-21 23:00 UTC)