Timestamp Difference In Hours for PostgreSQL

by

The first things popping up

EXTRACT(EPOCH FROM current_timestamp-somedate)/3600

May not be pretty, but unblocks the road. Could be prettier if division of interval by interval was defined.

Edit: if you want it greater than zero either use abs or greatest(…,0). Whichever suits your intention.

Edit++: the reason why I didn’t use age is that age with a single argument, to quote the documentation: Subtract from current_date (at midnight). Meaning you don’t get an accurate “age” unless running at midnight. Right now it’s almost 1am here:

select age(current_timestamp);
       age        
------------------
 -00:52:40.826309
(1 row)

More Related Contents:

  1. Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL
  2. Discard millisecond part from timestamp
  3. PostgreSQL “Column does not exist” but it actually does
  4. Any downsides of using data type “text” for storing strings?
  5. Query for array elements inside JSON type
  6. Calculating Cumulative Sum in PostgreSQL
  7. What is the expected behaviour for multiple set-returning functions in SELECT clause?
  8. Is it possible to make a recursive SQL query?
  9. Change PostgreSQL columns used in views
  10. Way to try multiple SELECTs till a result is available?
  11. How to set auto increment primary key in PostgreSQL?
  12. List columns with indexes in PostgreSQL
  13. PostgreSQL IF statement
  14. Split given string and prepare case statement
  15. Are there any way to execute a query inside the string value (like eval) in PostgreSQL?
  16. Get top row(s) with highest value, with ties
  17. Delete parent if it’s not referenced by any other child
  18. Window Functions: last_value(ORDER BY … ASC) same as last_value(ORDER BY … DESC)
  19. PostgreSQL wildcard LIKE for any of a list of words
  20. Query that ignore the spaces
  21. I keep getting the error “relation [TABLE] does not exist”
  22. SQL query to get most recent row for each instance of a given key
  23. PostgreSQL Where count condition
  24. Postgres function returning table not returning data in columns
  25. PostgreSQL column ‘foo’ does not exist
  26. SELECT DISTINCT is slower than expected on my table in PostgreSQL
  27. Return rows from INSERT with ON CONFLICT without needing to update
  28. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  29. Oracle Convert TIMESTAMP with Timezone to DATE
  30. Fetch records that are non zero after the decimal point in PostgreSQL

Leave a Comment