Timestamp Difference In Hours for PostgreSQL

Question

Is there a TIMESTAMPDIFF   equivalent for PostgreSQL     I know I can subtract two timestamps to get a postgresql INTERVAL   I just want the difference between the two timestamps in in hours represented by an INT     I can do this in MySQL like this   TIMESTAMPDIFF HOUR  links created  NOW      I just need the difference between two timestamps in hours represented as an integer   Solution works for me   SELECT  links link   created    links link   title    EXTRACT EPOCH FROM current timestamp -  links link   created   3600   Integer AS  age   FROM  links link

User · Answer

Michael Krelin s answer is close is not entirely safe  since it can be wrong in rare situations  The problem is that intervals in PostgreSQL do not have context with regards to things like daylight savings  Intervals store things internally as months  days  and seconds  Months aren t an issue in this case since subtracting two timestamps just use days and seconds but  days  can be a problem   If your subtraction involves daylight savings change-overs  a particular day might be considered 23 or 25 hours respectively  The interval will take that into account  which is useful for knowing the amount of days that passed in the symbolic sense but it would give an incorrect number of the actual hours that passed  Epoch on the interval will just multiply all days by 24 hours   For example  if a full  short  day passes and an additional hour of the next day  the interval will be recorded as one day and one hour  Which converted to epoch 3600 is 25 hours  But in reality 23 hours   1 hour should be a total of 24 hours   So the safer method is    EXTRACT EPOCH FROM current timestamp  - EXTRACT EPOCH FROM somedate   3600   As Michael mentioned in his follow-up comment  you ll also probably want to use floor   or round   to get the result as an integer value

User · Answer

You can use the  extract  or  date part  functions on intervals as well as timestamps  but I don t think that does what you want  For example  it gives 3 for an interval of  2 days  3 hours   However  you can convert an interval to a number of seconds by specifying  epoch  as the time element you want  extract epoch from  2 days  3 hours   interval  returns 183600  which you then divide by 3600 to convert seconds to hours    So  putting this all together  you get basically Michael s answer  extract epoch from timestamp1 - timestamp2  3600  Since you don t seem to care about which timestamp precedes which  you probably want to wrap that in abs   SELECT abs extract epoch from timestamp1 - timestamp2  3600

User · Answer

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

User · Answer

This might sound crazy to a lot of developers who like to take advantage of database functions   But after exhaustive problems thinking  creating and bugfixing applications for mysql and postgrsql with php comparing date functions  I ve come to the conclusion  for myself   that the easiest way  that is the simplest with less SQL headaches is not to take advantage of any of them   Why  because if you are developing in a middleware language like PHP  PHP has all of these functions  and they are easier to implement in the application ode as comparing integers  PostgreSQL timestamp is NOT    UNIX TIMESTAMP and MySQL s UNIX TIMESTAMP is NOT PostgresQL s or Oracles timestamp   it gets harder to port if you use database timestamps    so just use an integer  not a timestamp   as the number of seconds since january 1st 1970 midnight  and never mind database timestamps    and use gmdate   and store everything as gmt time to avoid timezone issues   if you need to search  sort or compare the day from other data  or the month or the year or the day of the week  or anything  in your application  and INTEGER datatype for time day  time hour  time seconds   or whatever you wnat to index to be searched will make for smoother and more portable databases  you can just use one field  in most instances  INTEGER time created NOT NULL    more fields in your database row is the only drawback to this solution that i have found  and that doesnt cause as many headaches  or cups of coffee     php s date functions are outstanding to compare dates  but in mysql or postgresql  comparing dates   nah   use integer sql comparisons  i realize it may SEEM easier to use CURRENT TIMESTAMP on an insert function  HA  don t be fooled    You cant do DELETE FROM SESSION TABLE WHERE time-initialized  lt   2 days  if time-intitialized is a postgresql timestamp  but you CAN do    DELETE FROM SESSION TABLE WHERE time initialized  lt    yesterday   As long as you set  yesterday in php as the integer of seconds since 1970 that yesterday was   This is easier housekeeping of session records than comparing timestamps in postgresql select statements   SELECT age    SELECT extract    and asbtime are headaches in an of themselves  this is just my opinion    you can do addition  substraction   lt      all with php date objects    peter sysko U4EA Networks  Inc

User · Answer

extract hour from age now   links created   gives you a floor-rounded count of the hour difference

User · Answer

Get fields where a timestamp is greater than date in postgresql   SELECT   from yourtable  WHERE your timestamp field  gt  to date  05 Dec 2000    DD Mon YYYY      Subtract minutes from timestamp in postgresql   SELECT   from yourtable  WHERE your timestamp field  gt  current timestamp - interval  5 minutes    Subtract hours from timestamp in postgresql   SELECT   from yourtable  WHERE your timestamp field  gt  current timestamp - interval  5 hours

User · Answer

postgresql get seconds difference between timestamps  SELECT        extract  epoch from            2012-01-01 18 25 00   timestamp -  2012-01-01 18 25 02   timestamp                                                    integer   which prints   -2   Because the timestamps are two seconds apart   Take the number and divide by 60 to get minutes  divide by 60 again to get hours

[sql] Timestamp Difference In Hours for PostgreSQL

Examples related to sql

Examples related to postgresql

Examples related to timestamp