Hot answers tagged postgresql
psql generally doesn't need much memory when playing large SQL files, since it doesn't buffer the whole file, only one query at a time, or it uses a COPY stream. The main situation when it may run out of memory is not when importing, but when SELECT'ing a large resultset, especially on 32 bits systems. This situation is generally solved by setting ...
extract() the epoch from the time component after casting to time (effectively removing the "day" component): SELECT extract(epoch FROM ts::time) AS sec_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 ...
You could try it yourself: WARNING: there is already a transaction in progress It starts no new (sub)transaction as nested transactions are not implemented in PostgreSQL. (You may do some magic in a pl/pgsql function, for example, that mimics that behaviour, though.)
You could do this using one of the following queries: select date_part('second', timestamp '2011-02-01 20:23:43'); select to_char(timestamp '2011-02-01 20:23:43', 'SS'); select extract(second from timestamp '2011-02-01 20:23:43'); Have fun.
Use the extract() method: select extract(second from current_timestamp) + extract(minute from current_timestamp) * 60 + extract(hour from current_timestamp) * 60 * 60; of course this can be put into a function: create or replace function total_seconds(p_timestamp timestamp) returns int as $$ select (extract(second from p_timestamp) + ...
Only top voted, non community-wiki answers of a minimum length are eligible