Elasticsearch advanced queries

See also ElasticSearch basics.

DQL to filter by non-zero length: Advert.location_query:* (does not work as a filter)

Or in Lucene: Advert.location_query:?*

Results are limited to 10,000 records, unless you use the scroll API which can paginate and also make parallel requests.

Gist:

MySQL date display format conversion

MySQL date time conversion functions: 

• UNIX_TIMESTAMP(date) docs
• FROM_UNIXTIME(epoch) docs

Examples:

select name, from_unixtime(time_added, '%Y-%m-%d %h:%i')

from company

order by date_added desc

limit 10; -- list the most recently added companies

You may also omit the '%Y-%m-%d %h:%i' format string, to get the default format YYYY-MM-DD HH:MM:SS, e.g. `2023-09-07 09:43:51`

Find and kill slow postgres queries

SELECT query_start,datname,procpid,current_query FROM pg_stat_activity;
SELECT NOW();

SELECT pg_cancel_backend($PID);
-- where $PID is the number from the procpid column in the first query