First, the dates need to be values of timestamp
type (so append ::timestamp
if you’re just specifying them as string literals).
If you subtract two timestamps, the result is of interval
type, which describes a duration of time (in hours, minutes, seconds etc.) You can use extract(epoch from interval_value)
to convert the interval into an absolute number of seconds.
So, putting that all together:
select extract(epoch from ('2011-12-30 09:55:56'::timestamp - '2011-12-30 08:54:55'::timestamp));
Remember that the ::timestamp
is only needed to convert the string literal to a timestamp: you don’t need it if you’re using the value of a timestamp column, for example.