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 -> second 30 of day
date: 11-10-2014 00:01:30 -> second 90 of day
date: 12-10-2014 00:02:00 -> 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);

fiddle

Old sqlfiddle

StackExchange Database Administrators Q#81035, answer score: 10