Tag Info

Hot answers tagged

2

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 ...


1

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 ...


1

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.)


1

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.


1

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