Get seconds of day in Postgres In Java we use Joda-Time to get the seconds of a day as an int value (despite the date):

date: 10-10-2014 00:00:30 -&gt; second 30 of day
date: 11-10-2014 00:01:30 -&gt; second 90 of day
date: 12-10-2014 00:02:00 -&gt; second 120 of day


Is there a way to do the same in PostgresSQL?
extract() the epoch from the time component after casting to time (effectively removing the date component):

SELECT extract(epoch FROM ts::time) AS seconds_of_day


You get the "number of seconds", including fractional seconds if there are any.
Very short and fast.

Test (with timestamps in unambiguous ISO format):

SELECT extract(epoch FROM ts::time) AS seconds_of_day
FROM  (
   VALUES 
     ('2014-10-12 00:00:30'::timestamp)   --    30
   , ('2014-10-12 00:01:30')              --    90
   , ('2014-10-12 00:02:00')              --   120
   , ('2014-10-12 12:00:00')              -- 43200
   , ('1999-12-23 23:59:59')              -- 86399
   , ('1999-12-23 23:59:59.123456')       -- 86399.123456
  ) t(ts);


db&lt;>fiddle here